Macro/VBA Paste Data Below the Last Used Row

E

egarcia7

New Member
Joined
Aug 9, 2016
Messages
17
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.

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

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).
Sort by date Sort by votes
How about
Code: 
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
 
Upvote 0
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
 
Upvote 0
How about
Code: 
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
 
Upvote 0
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.


Code: 
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
 
Upvote 0
By changing the Offset(1) to Offset(0) you are overwriting the last row of data everytime.
 
Last edited:
Upvote 0
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.






 
Upvote 0
What is the name of the table?
 
Upvote 0
In that case try
Code: 
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
 
Upvote 0
You must log in or register to reply here.

Similar threads

willow1985
  • Solved
VBA Run-time error 91 - Object variable or With block variable not set when trying to refresh data/Sleep
Replies
4
Views
238
willow1985
willow1985
K
  • Question Question
Increasing row before the macro runs again
Replies
1
Views
181
kbk
K
N
  • Question Question
Adding to an existing MACRO
Replies
2
Views
97
Northern NY Design
N
J
  • Solved
Continuous Copy and paste
2
Replies
10
Views
162
Joe4
Joe4
brent3162
  • Solved
VBA help with increasing row before macro runs again.
Replies
3
Views
342
jolivanes
jolivanes

Forum statistics

Threads
1,223,162
Messages
6,170,432
Members
452,326
Latest member
johnshaji

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top