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

Question

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

Fuzzy · Accepted Answer

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

Excel - Check rows for data, copy to new

1 response

Similar discussions

Subscribe To Our Newsletter!