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 D1:D15
etc...

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

Haas

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

wrote in message
ups.com...
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 D1:D15
etc...

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

Haas

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.

"Mike Fogleman" wrote:

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

wrote in message
ups.com...
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 D1:D15
etc...

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

Haas

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

--
Regards Ron de Bruin
http://www.rondebruin.nl



wrote in message ups.com...
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 D1:D15
etc...

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

Haas

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

--
Regards,
Tom Ogilvy


"Mike Fogleman" wrote in message
m...
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

wrote in message
ups.com...
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 D1:D15
etc...

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

Haas

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

Tom Ogilvy wrote:
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

--
Regards,
Tom Ogilvy


"Mike Fogleman" wrote in message
m...
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

wrote in message
ups.com...
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 D1:D15
etc...

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

Haas