Excel - Check rows for data, copy to new [Solved/Closed]

Ask a question Fuzzy - Last answered on Dec 15, 2010 at 02:53 PM by 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.

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

Was this answer helpful?  
Fuzzy- Jan 13, 2009 at 03:05 PM
Well, it's not pretty, but it seems to work.

Here's what I ended up with.
Wayne- Dec 15, 2010 at 02:42 PM
For Each w In Workbooks
If w.Name <> ThisWorkbook.Name Then
w.Close savechanges:=True
End If
Next w
Wayne- Dec 15, 2010 at 02:53 PM
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

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!