Search : in
By :

Excel - Check rows for data, copy to new

Last answer on Jan 13, 2009 8:05:22 pm GMT Fuzzy, on Jan 13, 2009 3:59:49 pm GMT 
 Report this message to moderators

Heya folks! I've been reading the answers to similar questions and I plan to try and patch something together, but I thought I'd put a (more) specific example out there, and see if someone might be able to throw some good advice my way while I'm working on it. Different perspectives often have a clearer view.

I have a large (but unknown) number of Excel workbooks, and I'm looking for a way to check if data exists in a row, and if it does, copy and paste it to a new sheet, so I end up with many consolidated rows of the data on a single sheet.

I'm working for a vendor to a grocery chain, and I need to go through our price sheets - which have lots of extra data, disclaimers, spot for signature, etc - and glean what items have been offered, prices, and dates. I need to check Column D (starting in row 31 - ends at row 48), and if there is a UPC, copy information from the same row in Columns B, D, G, K, N, W, AC and AI if possible (I can't unprotect AI). Then, paste it on a different sheet, and repeat for the next row.

So far I've been looking at using ISEMPTY() to check for something in the UPC slot (Column D), and copying/pasting is relatively straightforward, but advancing down the rows has got me a little hung up. I see the .xlUp function used in a lot of the examples, but it just seems so inefficient... is there a better way?

I'm gonna start tinkering with this now, but I'd appreciate any input you folks have. I'll try to check back in shortly to answer questions and whatnot.

Thanks!
Fuzzy

Configuration: Windows XP
Firefox 3.0.5

Best answers for « Excel Check rows for data, copy to new » in :
Find copy and create new excel files w macro Show
Search data in one column, copy data in new Show
[Excel] Sorting rows only Show [Excel] Sorting rows only Microsoft Excel by default perform a sort on the columns (Data/Sort). To sort the data horizontally, just select the data to be sorted, then Data tab /Sort/ Options/ check the Sort Left to right box in the...
Excel – Macro to detect and hide blank rows ShowExcel – Macro to detect and hide blank rows Issue Solution Note Issue I want a macro to unhide about 20 blank rows copy values into the top row then hide the remaining rows (some cells have fill though) then the next time it will...
Delete duplicates in an Excel column ShowDelete duplicates in an Excel column To remove duplicates in an Excel column: Click on the Data menu Filter Advanced Filter In this menu, select the column where the duplicates Check the box "Extract without duplication"...
Copy an entire partition on Vista ShowCopy an entire partition on Vista Issue Solution Issue It is possible to copy the entire partition on your hard drive with the DVD of vista. It is very useful if you want to change your hard drive in order to restore all data....
Ask your question Reply

1

Fuzzy, on Jan 13, 2009 6:19:51 pm GMT
  • +2

So, after more poking around, I see people being scolded on other sites for not providing example code. Here's what I have so far... it's simple, and missing a few key steps - but it's there.

---

Sheets("Widget Sheet").Select

If Not IsEmpty("D31") Then
Range("B31:AI31").Select
Selection.Copy
Windows("Book2").Activate
ActiveSheet.Paste
Application.CutCopyMode = False

End If

Windows("01-05-09 Widget.xls").Activate
ActiveWindow.Close

----


It looks to me like I need two loops, one for going through the UPCs, and one to advance my target sheet down the rows. For some reason I'm having a heck of a time getting a nice loop set up. I know there are always 17 rows (31-48), so I have a constant loopcount number as long as for each row it checks the space to see if it's empty. I can't quite figure out how to get the range to move down to D32, however. I'm considering just making copies of this little piece 17 times >.<

Also, if possible I'd like to be able to switch back (or not switch away) from the current book so I can close it and move (ala "ActiveWindow.Close") on to the next one automatically. So far in my poking I haven't seen any way to set a variable to mirror the book name, and every one is named differently. Does anyone know a way?

Copying column AI seems to work fine, even though it is protected (I can't even select it with the mouse).

Thanks!
Fuzzy

   
Reply to Fuzzy

2

 Fuzzy, on Jan 13, 2009 8:05:22 pm GMT
  • +7

Well, it's not pretty, but it seems to work.

Here's what I ended up with.
Sub Glean()

Dim iRowLoop As Integer

For iRowLoop = 31 To 48

If Not IsEmpty("D" & iRowLoop) Then

Range("B" & iRowLoop & ":" & "AI" & iRowLoop).Copy

' as iRowLoop increments, the row selection moves down, checking them all for ISEMPTY().

Workbooks("Book2").Worksheets("Sheet1").Range("A65535").End(xlUp).Offset(1, 0).PasteSpecial xlValues

' goes to the bottom of "Book2", ctrl-up-arrow to the top, down one, and pastes in text without switching active sheets.

End If

Next iRowLoop

' Increments iRowLoop, making the IF run 17 times.

End Sub

---

Now the fun part... actually doing the work! xD

   
Reply to Fuzzy
Ask your question Reply
They need your help