Appending data from one spreadsheet to another using a macro

[Guest]

I have been trying to append same type data from one spreadsheet to another
using a macro but since I never know how many lines of data are in each
spreadsheet I am appending it is either not inserting at all (because I am
selecting all -- it is indicating the rows are not the same size) or it is
only copying the number of rows from the second spreadsheet (not the number
of rows from subsequent spreadsheets if they are higher or it copies blank
rows). I am sure there is vb code that allows it to insert from one to
another on the fly but I can't get it to work. Any ideas would be
appreciated.

 

[Dave O]

This worked for me: assume you have two files open concurrently, one
called Source.xls and the other called Target.xls. Enter the word
"stop" (no quotes) at the bottom of the rows in the Source spreadsheet,
and run this code while the Source.xls sprdsht is in the active window:

Sub CopyRows()

Do Until ActiveCell.Value = "stop"
Selection.EntireRow.Copy
Windows("target.xls").Activate
ActiveSheet.Paste
ActiveCell.Offset(1, 0).Select
Windows("source.xls").Activate
ActiveCell.Offset(1, 0).Select
Loop


End Sub

 

[Guest]

I appreciate your quick response. I was just wondering how I could have the
source.xls spreadsheet automatically have the first row down from the last
cell automatically say "stop" as this will be something several users would
be completing. If it were me it would not be a big deal but I just want to
make it easier for the user, so they will use the app. Appreciate it.

DebP

 

[Guest]

I found another way with the help of Rowan Drummond. Maybe this could be
helpful for you also (and you don't have to have both spreadsheets open at
the same time as long as the column headers are the same, it works great.

Try something like:
Range("A2" & Cells(Rows.Count, 1).End(xlUp).Row).Copy