th enumber of rows will changes every time I do this report I

[Darkchild]

HI fellow excel users! I have a situation that maybe you have an answer for. So I want to excel to look into one worksheet copy all data rows/but not empty rows and then paste them into the first empty row in another worksheet. I'd write a macro, but the number of rows to select will be different each time. I've been all over the web trying to solve this. I don't see anything that really addresses the fact that the amount of rows is going to be different each time I do this report: 2x a month. I'd like to have a formula, I think, rather than a macro. I'm only good at recording and not so much at code in VBA. Any suggestions would be very welcome.

I can provide a sample if you think you may have an answer
Thanks.

 

[Dryver14]

some questions


are there empty rows before the end of the data or is it just the last row that varies?

Does the report start on row 2

how many columns wide is it?

is the report going to column A in second sheet

what are the names of the 2 sheets

 

[Darkchild]

Destination sheet is named for particular item number:X8920030
X8920030 is basically a central data dump for about 12 different vendors.Once I get all 12 vendors' data lined up in one place I could then do a pivot table.

I'd want to move however many rows of data with that part number from the sheet named Vendor X_US onto X8920030.
The data on Vendor X's worksheet could be anywhere below and including A2:D2.
The colums headings are: Part number, Customer Lot number,Expiration date and Quantity Available.

Hope that helps. thanks

 

[icerabbit]

My understanding is only basic, but this could be fairly simple. Indeed the issue is that you can't define a range ... which is what the macro recorder picks up. Record your macro with a current set of info. Then edit your recorded macro, looking at the selection part in the beginning and replace it with the code for whole sheet.

Essentially:

Sub CopyToSheet2()
' Select data on sheet 1
Sheet1.Activate
Sheet1.Cells.Select

or if it has tabname

Sheets("tabname").Activate
Sheets("tabname").Select

' Select & Copy
Selection.Copy
Sheet2.Activate

or

Selection.Copy
Sheets("tabname").Activate

Then you need the code to find the first empty cell. There are some sample codes in the forum via first empty cell or first blank cell.

Then once it has that cell.

ActiveSheet.Paste
End Sub

Sorry, I can't be of more concrete help. (just saw your post while seeing if somebody answered my question)

Hopefully one of the gurus may be able to help out with a complete macro. I would have to test it regarding the blank cell.

 

[Darkchild]

Thanks anyway- every little bit helps. The select part worked, but pasting did not.

 

[icerabbit]

I see what you're saying ... copy whole sheet requires an empty sheet to paste in ... mmm ... give me a few minutes.

 

[icerabbit]

Give this a go:

Sub darkchild1()
' darkchild1 Macro
' copy selection from 1 to first blank cell on 2
Sheets("Sheet1").Select
Range("A1").Select
Range(Selection, Selection.SpecialCells(xlLastCell)).Select
Selection.Copy
Sheets("Sheet2").Select
ActiveSheet.Range("a1").End(xlDown).Offset(1, 0).Select
ActiveSheet.Paste
End Sub

 

[icerabbit]

Of course, replace sheet1 and sheet2 with the names of the worksheets in your document.

 

[Darkchild]

IceRabbit thanks for your help. If I wanted to pull data from another workbook, Would that be written as
"!Inventory!Sheets(P8920030)"? where "Inventory" would be the name of the other workbook?


Happy Tuesday
D

 

[icerabbit]

Would you have the two documents open?

Do you want the macro in source to copy to the destination?
Or the macro in the destination sheet and copy from the source?