Transferring (Copy/Paste) Info to another excel workbook

[haas786]

Hey all,

I currently have an Excel template of a pricing sheet which I use daily
for individual data entry. Once I've entered all the data into it, I
want to press a button which runs a macro - this macro essentially
Copies information from various cells in the pricing template, and then
opens up another workbook ('Master Orders.xls') into which it Pastes
the copied info.

Here's my problem: how can I Paste the info to the next blank row in
the 'Master Order.xls' workbook? Everytime I press the Macro button,
the Copy, Open 'Master Order.xls' workbook, and Paste functions work
well, except it overwrites the last entry in the 'Master Order.xls'
workbook. So, if Order#1 (A1:A15) and Order#2 (B1:B15) are already in
the 'Master Order.xls' work book, and I run the macro, Order#2 gets
replaced. Instead, I want the info pasted on C1:C15, and then D115
etc...

Any or all help will be much appreciated. Thanks much in advance!

Haas

 

[Mike Fogleman]

Basically you need to find the last row in your Master Order wb and add 1 to
it before you paste.

Dim LRow as Long
LRow = Workbooks("Master Orders").Worksheets("Sheet1") _
.Cells(Rows.Count, "A").End(xlUp).Row

Workbooks("Template").Worksheets("Sheet1").Range("A1:A15").Copy Workbooks _
("Master Orders").Worksheets("Sheet1").Range("A" & LRow + 1)

Mike F

 

[Guest]

It looks like the orders are being entered as one order per column. If this
is true, then you will only be able to enter 256 orders. That's fine if you
don't expect a lot of business, or you are deleting the order information as
the orders are filled. Otherwise, you might want to change the format to
enter one order per row and then you can use Mike's code.

 

[Ron de Bruin]

See also
http://www.rondebruin.nl/copy1.htm

 

[Tom Ogilvy]

for columns

Dim rng as Range
With Workbooks("Master Orders").Worksheets("Sheet1")
if isempty( .Cells(1, "IV")) then
set rng = .Cells(1,"IV").End(xlToLeft)
else
msgbox "Range is full"
Exit Sub
end if
End with
if not isempty(rng) then set rng = rng.offset(0,1)
Workbooks("Template").Worksheets("Sheet1").Range("A1:A15").Copy rng

 

[haas786]

Thanks you all for your wonderful suggestions. This is the best
newsgroup out there in terms of helpfulness. I hope I can contribute
back one of these days.

Haas