Append data from one worksheet to a another worksheet

[maxebi]

Hi,
I would like to append data from one worksheet (Append to Master) to another worksheet (Master).

Append to Master (data)

columns
A............B............C

abc........1234......aidan
def.........5678......aidan
ghi..........9012.....aidan



Master (data)
columns

A.............B..............C

mos........8765.........lang
mos........4321.........lang
mos........9876.........lang


Results would look like:

Master
columns

A............B............C

abc........1234......aidan
def.........5678......aidan
ghi..........9012.....aidan
mos........8765......lang
mos........4321......lang
mos........9876.......lang

Thanks for your help..

Ariano

 

[ColinKJ]

Hi maxebi,

You could try something like this:

Sub CopyOver()
R = Workbooks("Data").Sheets("Data").Range("A65536").End(xlUp).Row
Workbooks("Master").Sheets("Master").Activate
For a = 2 To R
Cells(2, 1).Select
Selection.EntireRow.Insert
For x = 1 To 3
Cells(2, x) = Workbooks("Data").Sheets("Data").Cells(a, x)
Next x
Next a
End Sub

Regards

ColinKJ

 

[maxebi]

Hi,
Thanks for the reply, but I receive 'subscript out of range' when I run this as a macro in both worksheets.
Do I run this as a macro? If so, from worksheet master or data? Or how do I run this?

Thank you for all your help.
Ariano

 

[Datsmart]

This macro code will do what you are asking:

Sub Copy2Master()
'Assign variables for last row numbers in Source and Target worksheets
    lr = Sheets("Master").Range("A65536").End(xlUp).Row + 1
    ls = Sheets("Append to Master").Range("A65536").End(xlUp).Row
'Get range from "Append to Master" and copy
    Set Rng2 = Sheets("Append to Master").UsedRange.Offset(1, 0).Resize(ls - 1, 3)
    Rng2.Copy Sheets("Master").Range("A" & lr)
End Sub

As written, the code assumes you have a header row on the "Append to Master" worksheet that you do not want copied.
If you do not have a header row, change the "Set Rng2" line to this:

Set Rng2 = Sheets("Append to Master").UsedRange

You can run the code from either worksheet, it does not matter which is active.

 

[maxebi]

Thanks.. It works.
Thanks for you help.
Ariano