SOLVED

Help with a macro

Copper Contributor

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 :)

2 Replies
best response confirmed by SerPac87 (Copper Contributor)
Solution

@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
Wow man, amazing, thanks a lot!
1 best response

Accepted Solutions
best response confirmed by SerPac87 (Copper Contributor)
Solution

@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

View solution in original post