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

Darkchild

New Member
Joined
Mar 13, 2011
Messages
5
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.
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
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

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

New Member
Joined
Mar 11, 2011
Messages
18
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.
 
Last edited:

Darkchild

New Member
Joined
Mar 13, 2011
Messages
5

ADVERTISEMENT

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

icerabbit

New Member
Joined
Mar 11, 2011
Messages
18
I see what you're saying ... copy whole sheet requires an empty sheet to paste in ... mmm ... give me a few minutes.
 

icerabbit

New Member
Joined
Mar 11, 2011
Messages
18

ADVERTISEMENT

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

New Member
Joined
Mar 11, 2011
Messages
18
Of course, replace sheet1 and sheet2 with the names of the worksheets in your document.
 

Darkchild

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

New Member
Joined
Mar 11, 2011
Messages
18
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,108,627
Messages
5,523,985
Members
409,551
Latest member
WillCaton

This Week's Hot Topics

Top