Copy between spreadsheets

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

jcrmeyer@gmail.com - 22 Jan 2007 15:03 GMT

Hi there, I'm a bit of a newbie with macros so was hoping someone might
be able to help me out with this one:

I have a master spreadsheet (master.xls) that has a header row and then
a series or rows containing data in columns A thru G. Each row
corresponds to data taken from a number of other spreadsheets
(order1.xls, order2.xls etc).

Basically I need a macro run in master.xls that will copy data from one
of the order spreadsheets and append it to the rows already there in
master, i.e. find the last row containing data and adding it after
that.

Data in the order.xls workbooks has a fixed format A1:A3 and then a
variable number of rows A5:D5 (A6:D6, A7:D7 etc). So when copied into
Master.xls A5:D5, A6:D6 etc is copied to first empty row in columns D
thru G, and for each of those the same A1:A3 is transposed to columns A
thru C. So it ends up something like this:

A1 A2 A3 A5 B5 C5 D5
A1 A2 A3 A6 B6 C6 D6
A1 A2 A3 A7 B7 C7 D7
etc

I'm okay with opening workbooks, making them active etc. What I need is
the code that will find the first empty row in Master.xls and the code
to copy (a loop I guess) that will move date from order.xls to
master.xls and know when to stop when it reaches the last filled row in
order.xls.

Thanks!

Reply to this Message

jcrmeyer@gmail.com - 22 Jan 2007 15:05 GMT

Sorry that should have been "move data" not "move date" in my last
sentence!

> Hi there, I'm a bit of a newbie with macros so was hoping someone might
> be able to help me out with this one:
[quoted text clipped - 27 lines]
>
> Thanks!

Reply to this Message

merjet - 22 Jan 2007 15:44 GMT

Di iRow As Long

'find last filled cell in column A
iRow =
Workbook("Master.xls").Sheets("Sheet1").Range("A1").end(xlDown).Row
'so iRow +1 would be the next empty row in column A
'do similar line in order.xls and use result to set last row of loop

Hth,
Merjet

Reply to this Message

merjet - 22 Jan 2007 15:46 GMT

Oops. Workbooks, not Workbook

Merjet

Reply to this Message

jcrmeyer@gmail.com - 22 Jan 2007 22:20 GMT

Okay, I've got it working. But I think there might be neater ways of
doing it. Because one range of data has to be transposed I'm copying
over in two stages. It would be good if I could do it in a single step
but I'm not sure how.

Also could someone tell me how I select a range for copying that is not
contiguous, i.e below I use
Range(Cells(rw, 1), Cells(rw, 20)).Select, but actually I only want to
select then copy the first cell and the last 15 cells in the row
skipping cells 2-5.

Workbooks("order.xls").Sheets("Orderform").Activate
Dim rw As Long
rw = 17

Do Until ActiveSheet.Cells(rw, 1) = ""

Range(Cells(8, 2), Cells(12, 2)).Select
Application.CutCopyMode = False
Selection.Copy

Workbooks("master.xls").Sheets("Orders").Activate
Range("F1").End(xlDown).Offset(1, -5).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=True

Workbooks("order.xls").Sheets("Orderform").Activate
Range(Cells(rw, 1), Cells(rw, 20)).Select
Application.CutCopyMode = False
Selection.Copy

Workbooks("master.xls").Sheets("Orders").Activate
Range("F1").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats,
Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

Workbooks("order.xls").Sheets("Orderform").Activate

rw = rw + 1

Loop

Reply to this Message