Move data from one sheet to another with VBA

Question

I have an Excel workbook with 4 sheets.

1. Master Sheet
2. test_1
3. test_2
4. test_3

I want to move the data from Master Sheet to all the other sheets, which I have done by creating a macro. Daily the Master sheet data will be increasing, so how can I accommodate this change in the Macro.

I have pasted my existing code below:

Sub sbCopyRangeToAnotherSheet()
    Sheets("Master").Range("B10:M1628").Copy
    Sheets("test_1").Activate
    Range("B9").Select
    ActiveSheet.Paste
    Application.CutCopyMode = Flase
End Sub


Sub sbCopyRangeToCRP2()
    Sheets("Master").Range("B10:M1628").Copy
    Sheets("test_2").Activate
    Range("B9").Select
    ActiveSheet.Paste
    Application.CutCopyMode = Flase
End Sub


Sub sbCopyRangeToCRP3()
    Sheets("Master").Range("B10:M1628").Copy
    Sheets("test_3").Activate
    Range("B9").Select
    ActiveSheet.Paste
    Application.CutCopyMode = Flase
End Sub

In the above code I mentioned the hard coded range value of Master Sheet which starts from B10 and ends at M1628.

Going forward number of rows increase**(B10 range will remain)** and I don't want to hard code the Range. How can I accomplish this?

Answer 1

I would suggest either to use the UsedRange property of the Worksheet object,

or to define named ranges on the sheet that expand automatically as the data on the sheet grows, like: =OFFSET($A$1,0,0,COUNTA($A:$A),1)

Answer 2

I suggest to combine these 3 subs to one that you can re-use by giving the worksheet as parameter:

Sub sbCopyRangeToAnotherSheet(ToSheet As Worksheet)
    Dim LastUsedRow As Long

    With Sheets("Master")
        LastUsedRow = .UsedRange.Row + .UsedRange.Rows.Count - 1
        .Range("B10:M" & LastUsedRow).Copy ToSheet.Range("B9")
    End With

    Application.CutCopyMode = False
End Sub

Then you can run this sub for any sheet name like

Sub test_1()
    sbCopyRangeToAnotherSheet Sheets("test_1")
    'and for the second sheet
    sbCopyRangeToAnotherSheet Sheets("test_2")
End Sub

Answer 3

You can use this looping macro

Sub CopyAll()
    Dim src As Range, dest
    With Worksheets("Master") ' set the source range
        Set src = .Range("B10:M" & .Cells(.Rows.Count, "B").End(xlUp).Row)
    End With
    For Each dest In Array("test_1", "test_2", "test_3") ' loop on destination sheets
        src.Copy Worksheets(dest).Range("B9")
    Next
End Sub

Answer 4

I think the esaiest way to copy data is to use an array, which is filld dinamicly.

1. Create the exact array
2. Fill with data, from Master Sheet
3. Paste the data.

And in this case, you don't have to worry about the new row's becouse you use dinamic array. See some example below.

Sub sbCopyRangeToAnotherSheet()
Sheets("Master").Select
Dim RowNum as integer 
For i = 0 To 250000 'Count all rows
    If IsEmpty(Cells(i + 10, 2)) = False Then
        RowNum = RowNum + 1 'Count all rows which have data in it's second column
    Else
        Exit For
    End If
Next
ReDim myData(RowNum - 1, 12) As String 'create array
For i = 0 To RowNum - 1 'fill array, with data
    For j = 0 to 12
    myData(i, j) = Cells(i + 10, j+2) '+10 because you said B**10**
                                      '+2 because you said **B**10
    Next
Next

Sheets("test_1").Activate
For i = 0 To RowNum - 1 'fill array, with data
    For j = 0 to 12
    Cells(i + 10, j+2) = myData(i, j) 'Fill cells with data
    Next
Next
End Sub