Kioskea
Recherche
Ask a question Report

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

Fuzzy - Latest answer on Dec 15, 2010 02:53PM
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
Read more 
Answer
+7
moins plus
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
Fuzzy- Jan 13, 2009 03:05PM
Well, it's not pretty, but it seems to work.

Here's what I ended up with.
Reply
Wayne- Dec 15, 2010 02:42PM
For Each w In Workbooks
If w.Name <> ThisWorkbook.Name Then
w.Close savechanges:=True
End If
Next w
Reply
Wayne- Dec 15, 2010 02:53PM
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
Reply
Add comment
This document entitled « Excel - Check rows for data, copy to new » from Kioskea (en.kioskea.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.

Not a member yet?

sign-up, it takes less than a minute and it's free!

Members get more answers than anonymous users.

Being a member gives you detailed monitoring of your requests.

Being a member gives you additional options.