Excel - Check rows for data, copy to new

 Wayne -
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.


1 reply

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
Application.CutCopyMode = False

End If

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


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).

Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

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

Here's what I ended up with.
For Each w In Workbooks
If w.Name <> ThisWorkbook.Name Then
w.Close savechanges:=True
End If
Next w
To select rows one after the next you set a loop

For c = 1 to 5 ' This would loop column A to E
For r = 31 to 48 ' This loops rows
Sheets("Your Sheet Name Here").Cells(r , c).select
' Add here what you need it to do i.e copy - paste
Next r
Next c