VBA Code not pasting to top row of Table

[WxShady13]

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?

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

 

[mart37]

Delete the old table first: Range("A2", Range("A2").SpecialCells(xlLastCell)).ClearContents

 

[WxShady13]

I tried that but it still starts in row 7 (1st row of table is row 6).

 

[NoSparks]

Here's a couple of links to sites that deal with tables

Excel: Working with Tables (VBA)

jkp-ads.com: Excel Tables (VBA)

www.jkp-ads.com

The VBA Guide To ListObject Excel Tables

For a data analyst, Excel Tables are a necessity! They are the most efficient way to organize your raw data and refer to data that contracts or expands on a regular basis. Likewise, Excel tables can be extremely useful in combination with VBA.

www.thespreadsheetguru.com

 

[WxShady13]

This is still not answered if anyone can help.

 

[Akuini]

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]

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]

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]

Are you sure A6 is empty? No spaces?

 

[WxShady13]

Are you sure A6 is empty? No spaces?

Yes it is completely clear