Macro/VBA Paste Data Below the Last Used Row

[egarcia7]

Hello Everyone,

I have 5 tabs in total in my workbook, when I click on the macro button I want to clear the information from the dashboard and then bring in the information from the other tabs onto the Dashboard tab. So the problem i'm having is when I bring in the information from the second, third and fourth tab it just overlaps each other, or in the case below it just pastes it on the selected cell range. I want the code to search for the last row with information and paste it below that

Tab 1: Dashboard
Tab 2: Brian
Tab 3: Michael
Tab 4: Raul
Tab 5: Rudy

Any help will be appreciated, Thank you in advance.

Sub Aggergate_to_Master()
'
' Aggergate_to_Master Macro
' This macro will clear the master sheet and copy all the new information that the coordinators have updated.
'
If Not ActiveCell.ListObject Is Nothing Then
    ActiveCell.ListObject.DataBodyRange.Delete
End If
'
        
    Sheets("Brian").Select
        Range("Brian").Select
            Selection.Copy
        Sheets("Dashboard").Select
            Range("A11").Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
    
    Sheets("Michael").Select
        Range("Michael").Select
            Application.CutCopyMode = False
                Selection.Copy
        Sheets("Dashboard").Select
            Range("A23").Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
    
    Sheets("Raul").Select
        Range("Raul").Select
            Application.CutCopyMode = False
                Selection.Copy
        Sheets("Dashboard").Select
            ActiveWindow.SmallScroll Down:=27
                Range("A38").Select
                    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                        :=False, Transpose:=False
    
    Sheets("Rudy").Select
        Range("Rudy").Select
            Application.CutCopyMode = False
                Selection.Copy
        Sheets("Dashboard").Select
            Range("A44").Select
                Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
                    :=False, Transpose:=False
    
    Sheets("Andy").Select
        Range("A10").Select
End Sub

 

[Fluff]

How about

Sub Aggergate_to_Master()
'
' Aggergate_to_Master Macro
' This macro will clear the master sheet and copy all the new information that the coordinators have updated.
'
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("Brian", "Michael", "Raul", "Rudy")
   
   If Not ActiveCell.ListObject Is Nothing Then
       ActiveCell.ListObject.DataBodyRange.Delete
   End If

   With Sheets("Dashboard")
      For i = 0 To UBound(Ary)
         Sheets(Ary(i)).Range(Ary(i)).Copy
         .Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial xlPasteValues
      Next i
   End With
   Application.CutCopyMode = False
   Sheets("Andy").Select
   Range("A10").Select
End Sub

 

[egarcia7]

Hello Fluff,

Thank you for your help with the macros. In short it did work but when the information is being pasted it skips the table, in other words it pastes below the table.

Can I be doing something wrong?

Thanks again for your help,

-E

 

[Fluff]

How about

Sub Aggergate_to_Master()
'
' Aggergate_to_Master Macro
' This macro will clear the master sheet and copy all the new information that the coordinators have updated.
'
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("Brian", "Michael", "Raul", "Rudy")
   
   If Not ActiveCell.ListObject Is Nothing Then
       ActiveCell.ListObject.DataBodyRange.Delete
   End If

   With Sheets("Dashboard")
      For i = 0 To UBound(Ary)
         Sheets(Ary(i)).Range(Ary(i)).Copy
         .Range("A" & ActiveCell.ListObject.Range.Rows.Count).PasteSpecial xlPasteValues
      Next i
   End With
   Application.CutCopyMode = False
   Sheets("Andy").Select
   Range("A10").Select
End Sub
Sub Aggergate_to_Master()
'
' Aggergate_to_Master Macro
' This macro will clear the master sheet and copy all the new information that the coordinators have updated.
'
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("Brian", "Michael", "Raul", "Rudy")
   
   If Not ActiveCell.ListObject Is Nothing Then
       ActiveCell.ListObject.DataBodyRange.Delete
   End If

   With Sheets("Dashboard")
      For i = 0 To UBound(Ary)
         Sheets(Ary(i)).Range(Ary(i)).Copy
         .Range("A" & ActiveCell.ListObject.Range.Rows.Count).PasteSpecial xlPasteValues
      Next i
   End With
   Application.CutCopyMode = False
   Sheets("Andy").Select
   Range("A10").Select
End Sub

 

[egarcia7]

Hi Fluff,

I've been playing around with both macro you posted above and I was able to getthe first macro to work, (view macro below) I just adjusted the offset.

But I have a problem, as you already know there are 4 tabs with data. Whenimporting to the dashboard, they are skipping the last row of data for somereason, even if I add a totals row it’s still skipping one line of data.

Sub Aggergate_to_Master()
'
' Aggergate_to_Master Macro
' This macro will clear the master sheet and copy all the new information that the coordinators have updated.
'
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("Brian", "Michael", "Raul", "Rudy")
   
   If Not ActiveCell.ListObject Is Nothing Then
       ActiveCell.ListObject.DataBodyRange.Delete
   End If[/FONT][/COLOR]
[COLOR=#222222][FONT="Verdana"]   With Sheets("Dashboard")
      For i = 0 To UBound(Ary)
         Sheets(Ary(i)).Range(Ary(i)).Copy
         .Range("A" & Rows.Count).End(xlUp).Offset(0).PasteSpecial xlPasteValues
      Next i
   End With
   Application.CutCopyMode = False
   Sheets("Andy").Select
   Range("A10").Select
End Sub

-Brian:Skipping last row (even with a totals row)
-Michael: Skippinglast row (even with a totals row)
-Raul: Skippinglast row (even with a totals row)
-Rudy: importsall data

 

[Fluff]

By changing the Offset(1) to Offset(0) you are overwriting the last row of data everytime.

 

[egarcia7]

Yes you are correct, when I changed the offset all the datadid come in, but then I ran into the same problem with the data not being partof the table. I was trying to add an image but I due to computer restraints Icannot upload any pictures to other sites. But when the information is being copiedover to the dashboard the table header is located at A10 and the data starts onA11, Last column used is L. Not sure if this would make a difference.


When I use the second macro it erases the table and insertsthe data starting on A1. The reason I start the data on A10 is to give room forthe slicers.


Thank you for your patience.

 

[Fluff]

What is the name of the table?

 

[egarcia7]

The name of the table where I want to paste everything on to is called, "Dashboard".

 

[Fluff]

In that case try

Sub Aggergate_to_Master()
'
' Aggergate_to_Master Macro
' This macro will clear the master sheet and copy all the new information that the coordinators have updated.
'
   Dim Ary As Variant
   Dim i As Long
   Dim Rng As Range
   Ary = Array("Brian", "Michael", "Raul", "Rudy")
   
   With Sheets("Dashboard").Range("Dashboard").ListObject
      If .ListRows.Count > 1 Then .DataBodyRange.Delete
      .ListRows.Add , True
      For i = 0 To UBound(Ary)
         Sheets(Ary(i)).Range(Ary(i)).Copy
         .DataBodyRange.Cells(.ListRows.Count + Application.Min(i, 1), 1).PasteSpecial xlPasteValues
      Next i
   End With
   Application.CutCopyMode = False
   Sheets("Andy").Select
   Range("A10").Select
End Sub