VBA Code not pasting to top row of Table

WxShady13

Board Regular
Joined
Jul 24, 2018
Messages
153
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I have VBA code that will pull the last row of data from a table on every worksheet within the workbook and then paste it back to the Balance Tables worksheet. Currently it is not pasting to the 1st row of the table (each month I need to overwrite the data for billing purposes). What adjustments need to be made to the code to get it to start at the first row of the table?
VBA Code:
Sub Copy_Sheets_To_Master()

Dim wb As Workbook
Dim ws As Worksheet
Dim i, LastRowa, LastRowd As Long
Dim WSname As String

Set wb = ActiveWorkbook

For Each ws In wb.Sheets

If ws.Name <> "Balance Tables" Then
'If ws.Name <> "Monthly Amount Due" Then
If ws.Name <> "Main" Then
If ws.Name <> "Key" Then
If ws.Name <> "Mail Merge" Then
'If ws.Name <> "Balance" Then
'If ws.Name <> "VLOOKUP DATA" Then
'If ws.Name <> "Payroll Table" Then
'If ws.Name <> "Payment Log" Then

WSname = ws.Name

'With ActiveSheet
ws.Cells(Rows.Count, 1).End(xlUp).EntireRow.Copy
'End With
'LastRowa = ws.Cells(Rows.Count, "A").End(xlUp).Row
LastRowd = Sheets("Balance Tables").Cells(Rows.Count, "A").End(xlUp).Row

'ws.Range("A6" & LastRowa).EntireRow.Copy
Sheets("Balance Tables").Range("A" & LastRowd + 1).PasteSpecial Paste:=xlPasteValues
'Sheets("Master").Range("A" & LastRowd + 1).Value = WSname

End If
End If
End If
End If
'End If
'End If
'End If
'End If
'End If

Next ws

End Sub
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,044
Office Version
  1. 2016
Platform
  1. Windows
Delete the old table first: Range("A2", Range("A2").SpecialCells(xlLastCell)).ClearContents
 

WxShady13

Board Regular
Joined
Jul 24, 2018
Messages
153
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
I tried that but it still starts in row 7 (1st row of table is row 6).
 

NoSparks

Well-known Member
Joined
Mar 15, 2013
Messages
1,048
Office Version
  1. 2010
Platform
  1. Windows

WxShady13

Board Regular
Joined
Jul 24, 2018
Messages
153
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

This is still not answered if anyone can help.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,955
Office Version
  1. 365
Platform
  1. Windows
Try changing this line

LastRowd = Sheets("Balance Tables").Cells(Rows.Count, "A").End(xlUp).Row

to this:

LastRowd = Sheets("Balance Tables").Range("A:A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
 

WxShady13

Board Regular
Joined
Jul 24, 2018
Messages
153
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile

ADVERTISEMENT

Try changing this line

LastRowd = Sheets("Balance Tables").Cells(Rows.Count, "A").End(xlUp).Row

to this:

LastRowd = Sheets("Balance Tables").Range("A:A").Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
That starts the data at row 6689 now for some reason.
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
2,955
Office Version
  1. 365
Platform
  1. Windows
Looks like you have data below the table.
What's the name of the table? Say it's "Table1", then
Replace these 2 lines:

LastRowd = Sheets("Balance Tables").Cells(Rows.Count, "A").End(xlUp).Row Sheets("Balance Tables").Range("A" & LastRowd + 1).PasteSpecial Paste:=xlPasteValues

with this:
Sheets("Balance Tables").ListObjects("Table1").DataBodyRange.Cells(1, 1).PasteSpecial Paste:=xlPasteValues
 

mart37

Well-known Member
Joined
Aug 4, 2017
Messages
1,044
Office Version
  1. 2016
Platform
  1. Windows
Are you sure A6 is empty? No spaces?
 

Watch MrExcel Video

Forum statistics

Threads
1,127,009
Messages
5,622,145
Members
415,881
Latest member
tasic89

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
Top