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


New Member
Mar 13, 2011
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

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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
Upvote 0
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
Upvote 0
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.


Sub CopyToSheet2()
' Select data on sheet 1

or if it has tabname


' Select & Copy



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.

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.
Last edited:
Upvote 0
I see what you're saying ... copy whole sheet requires an empty sheet to paste in ... mmm ... give me a few minutes.
Upvote 0
Give this a go:

Sub darkchild1()
' darkchild1 Macro
' copy selection from 1 to first blank cell on 2
Range(Selection, Selection.SpecialCells(xlLastCell)).Select
ActiveSheet.Range("a1").End(xlDown).Offset(1, 0).Select
End Sub
Upvote 0
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
Upvote 0
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?
Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back