Excel Forum

Copying tables

Sal Veltri

California

Member

Members

Excel Tables

Forum Posts: 28

Member Since:
May 27, 2019

Offline

September 25, 2019 - 12:57 pm

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/x9n.....Y-Rqa?dl=0

Any help would be appreciated.

See Answer

Catalin Bombea

Iasi, Romania

Member

Dashboards

Power Query

Power Pivot

Xtreme Pivot Tables

Excel for Decision Making

Excel for Finance

Excel Analysis Toolpak

Power BI

Excel

Word

Outlook

Excel Expert

Excel Customer Service

PowerPoint

Forum Posts: 1939

Member Since:
November 8, 2013

Offline

September 26, 2019 - 1:38 pm

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"