Thread: Is this type of copy even possible?

Trying to create a custom solution in Excel, and everybody in the office is stumped.

The Excel document has three worksheets, called MASTER, DATA1, DATA2. All three worksheets have the same columns, in the same locations. Only thing different is DATA1 and DATA2 have different info listed in their rows.

What we want to do is have the MASTER sheet display content from both DATA1 and DATA2. The trick is that data will be updated in DATA1 and DATA2 regularly, additional rows of content will be added over time.

How do we get this type of content to properly display in the MASTER sheet? We need MASTER to be dynamic enough to "add" rows from DATA1 and DATA2, or even delete rows from either of those sheets...all without overwiting data from the other worksheet. We can "reserve" a number of rows in MASTER, but we'd like to have it list all the rows from one, then all the rows from the other, if at all possible.

Help? Thank you!

You would need code to copy the data from each sheet to the master.Would you clear existing data from the incoming sheets or h=just keep adding them?

I'm willing to try anything at this point, CC You would think this would be something very simple with a copy function, but guess nobody else at MS thinks consolidating info onto a master is important Thanks!

By the way, I've got a test file with what I'm trying to do...just can't figure out how to get it attached here on my forum postings.

EDIT: UPLOADED SAMPLE XLSX file

Thanks, edited my response above with the test document. All I want is to get everything from DATA 1 and 2 (and other future additional sheets) into MASTER, no matter how many rows there are in each of the sheets

I do appreciate your helping me along!

Maybe it's not the best option but it works. I've uploaded workbook.

Private Sub MasterButton_Click()
Dim Db1, Db2 As Worksheet
Dim MyDb1Rng, MyDb2Rng As Range
Dim lRow, lstRow, lsRow As Long

Set Db1 = Sheets("DATA1")
Set Db2 = Sheets("DATA2")

Application.ScreenUpdating = False

'Finding last row in DATA1 sheet
    With Db1
        lRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With

'Finding last row in DATA2 sheet
    With Db2
        lsRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With

'Finding lastrow in Master sheet
    With ActiveSheet
        lstRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With
    
'Clearing Contents of used range in master sheet
Sheets("Master").Range("A2", "H" & lstRow + 1).ClearContents

'Setting up and copying DATA1 used range and pasting copied range into Master sheet.
Set MyDb1Rng = Db1.Range("A2:H" & lRow)
    MyDb1Rng.Copy
    Sheets("Master").Range("A2").PasteSpecial (xlPasteValues)
    Application.CutCopyMode = False

'Setting up and copying DATA2 used range and pasting copied range into Master sheet.
Set MyDb2Rng = Db2.Range("A2:H" & lsRow)
    MyDb2Rng.Copy

    'Finding lastrow in Master sheet
    With ActiveSheet
        lstRow = .Cells(.Rows.Count, 1).End(xlUp).Row
    End With

Sheets("Master").Range("A" & lstRow + 1).PasteSpecial (xlPasteValues)

    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
End Sub

Contaminated,

In these declarations,

Dim Db1, Db2 As Worksheet
Dim MyDb1Rng, MyDb2Rng As Range
Dim lRow, lstRow, lsRow As Long

... everything except the last variable on each line is a Variant. What you want is

Dim Db1 As Worksheet, Db2 As Worksheet
Dim MyDb1Rng  As Range, MyDb2Rng As Range
Dim lRow As Long, lstRow As Long, lsRow As Long

shg
I'm really new in VBA and I can make a mistake. So thank you for correction.
Briefely for the future: I must dim worksheet and range separately? Right.

I don't want to arque, but the code works, so why to change smth?

I know I'm wrong here by asking that, but I just want to understand

thx in advance...

You must specify the data type for each variable, else the variable defaults to a Variant.

I don't want to arque, but the code works, so why to change smth?

You don't have to declare variables at all; it's just good practice. It enables VBA to detect type mismatches, and avoids unnecessary errors.