i have 12 tables on one sheet, each table is a header, first row with formulas only, no data. Each one of these are needed on another sheet. Using the following code:
With shtPlaces
' .Visible = xlSheetVisible
.ListObjects("tblSampleDay1F1").Range.Copy Destination:= _
Range("C33")
shtBDP.ListObjects(1).Name = "tblDay1Front1"
.ListObjects("tblSampleDay1F2").Range.Copy Destination:= _
Range("C46")
shtBDP.ListObjects(2).Name = "tblDay1Front2"
.ListObjects("tblSampleDay1B1").Range.Copy Destination:= _
Range("G33")
shtBDP.ListObjects(3).Name = "tblDay1Back1"
.ListObjects("tblSampleDay1F1").Range.Copy Destination:= _
Range("G46")
shtBDP.ListObjects(4).Name = "tblDay1Back2"
.ListObjects("tblSampleDay1F1").Range.Copy Destination:= _
Range("K33")
shtBDP.ListObjects(5).Name = "tblDay1Totat1"
.ListObjects("tblSampleDay1F1").Range.Copy Destination:= _
Range("K46")
shtBDP.ListObjects(6).Name = "tblDay1Totat2"
.ListObjects("tblSampleDay2F1").Range.Copy Destination:= _
Range("C33")
shtBDP.ListObjects(7).Name = "tblDay2Front1"
.ListObjects("tblSampleDay2F2").Range.Copy Destination:= _
Range("C93")
shtBDP.ListObjects(8).Name = "tblDay2Front2"
.ListObjects("tblSampleDay2B1").Range.Copy Destination:= _
Range("C106")
shtBDP.ListObjects(9).Name = "tblDay2Back1"
.ListObjects("tblSampleDay2F1").Range.Copy Destination:= _
Range("G93")
shtBDP.ListObjects(10).Name = "tblDay2Back2"
.ListObjects("tblSampleDay2F1").Range.Copy Destination:= _
Range("G106")
shtBDP.ListObjects(11).Name = "tblDay2Totat1"
.ListObjects("tblSampleDay2F1").Range.Copy Destination:= _
Range("K93")
shtBDP.ListObjects(12).Name = "tblDay2Totat2"
.ListObjects("tblSampleDay2F1").Range.Copy Destination:= _
Range("K106")
End With
The works fine until line shtBDP.ListObjects(7).Name = "tblDay2Front1". Here it seems to change the first table named "tblDay1Front1" to "tblDay2Front1" instead of renaming the table just pasted.
heres a link to the file: https://www.dropbox.com/sh/x9ns177mmjcn25m/AAB5BkzTzWuGFm1wS-1NY-Rqa?dl=0
Any help would be appreciated.
Hi Sal,
The file from the link provided does not contain the code you described.
Use shtBDP.ListObjects("tblSampleDay1F1") reference to make sure you are referring to the correct table.
I see you are trying to paste a table from a sheet to another, then you "assume" that the newly added table has the index 12:
.ListObjects("tblSampleDay2F1").Range.Copy Destination:= _
Range("K93")
shtBDP.ListObjects(12).Name = "tblDay2Totat2"
Using shtBDP.ListObjects(12) is not precise, you cannot know for sure that shtBDP.ListObjects(12) is the table named "tblSampleDay1F1" or it's the table "tblSampleDay2F1"
To see the index number associated to each table, use:
For i=1 to 12
debug.print "Table no. " & i & "starts in cell " & shtBDP.ListObjects(i).Range.Cells(1).Address
Next i
You can rename the table, if you're sure it's in the correct location:
If shtBDP.ListObjects(i).Range.Cells(1).Address="$K$93" Then shtBDP.ListObjects(i).Name = "tblDay2Totat2"
Thank you for the assistance - Sal
