Forum Discussion

SerPac87's avatar
SerPac87
Copper Contributor
Feb 23, 2023

Help with a macro

Hi everyone, I am having a bad time with a Macro and I was hoping you would be able to help me. I have two tabs with data:

-Data 1

-Data 2

 

I want to create a macro to take the data from Data 1 and paste it into a tab called "Data combined" and then take the data from Data 2 and paste it in "Data combined" below the data already pasted there (important, because the amount of rows may differ).

 

I have then two pivot tables that are taking the information from "Data combined" and doing a comparison between Data 1 and Data 2, so I need also to refresh the pivots with the new information and show the details.

 

I can handle the second part, but if you could help me with the first one I would really appreciate 🙂

Excel
Macros and VBA
office 365
  • HansVogelaar's avatar
    HansVogelaar
    Feb 23, 2023

    SerPac87 

    For example:

    Sub CopyData()
    Dim ws As Worksheet
    Dim wt As Worksheet
    Dim t As Long
    Application.ScreenUpdating = False
    Set wt = Worksheets("Data combined")
    t = 1
    For Each ws In Worksheets(Array("Data 1", "Data 2"))
        With ws.Range("A1").CurrentRegion
            .Copy Destination:=wt.Range("A" & t)
            t = .Rows.Count + 1
        End With
    Next ws
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub
  • HansVogelaar's avatar
    HansVogelaar
    MVP
    Feb 23, 2023

    SerPac87 

    For example:

    Sub CopyData()
    Dim ws As Worksheet
    Dim wt As Worksheet
    Dim t As Long
    Application.ScreenUpdating = False
    Set wt = Worksheets("Data combined")
    t = 1
    For Each ws In Worksheets(Array("Data 1", "Data 2"))
        With ws.Range("A1").CurrentRegion
            .Copy Destination:=wt.Range("A" & t)
            t = .Rows.Count + 1
        End With
    Next ws
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub