How to select different tables (Table1, Table2, Table3... using VBA

[Chiwidan]

Hi guys,

I hope you are having a good week so far. Greetings from New Zealand.

I am stuck but I will say this will be quite a basic question:

In a worksheet called "calculations" I have different 10 tables (From Table2 to Table11)

I am trying to pull the information from Table"i+1" but I am unsure how to code it.

Below is what I have but it gets stuck in the first line (on yellow)

Sub Invoice_Making()


Dim shMacros As Worksheet, shCalculations As Worksheet, shInvTemplate As Worksheet
Set shMacros = Worksheets("Macros")
Set shCalculations = Worksheets("Calculations")
Set shInvTemplate = Worksheets("Invoice Template")

'Inserts new sheets, and names them a name according to form
 
Sheets("Macros").Select

Dim sheet_count As Integer

sheet_count = Range("E2")

Dim sheet_name As String
Dim i As Integer



For i = 1 To sheet_count


'copy NHI from Calculations to Invoice Template


    [COLOR=rgb(247, 218, 100)]shCalculations.Range("Table" & "(i+1)[NHI]").Copy shInvTemplate.Range("X3")[/COLOR]
 
    shCalculations.Range("Table&i+1[NHI2]").Copy shInvTemplate.Range("X19")
 
    shCalculations.Range("Table&i+1[NHI3]").Copy shInvTemplate.Range("X35")

shCalculations.Range("Table&i+1[NHI4]").Copy shInvTemplate.Range("X51")

 

[Fluff]

What are the names of your tables?

 

[Chiwidan]

What are the names of your tables?

Well, the page will have 10 tables from Table2 to Table11, but not allways the 10 tables will be used...

The idea will be something like

for i = 1 to n
Take info from table(i+1) and copy it to another worksheet...
... other stuff.....
next i

 

[Fluff]

In that case use

shCalculations.Range("Table" & i + 1).Copy shInvTemplate.Range("X3")

but you are going to be copying the data to the same cells

 

[Chiwidan]

In that case use

shCalculations.Range("Table" & i + 1).Copy shInvTemplate.Range("X3")

but you are going to be copying the data to the same cells

Hi Fluff,

well, the idea is that the for i = 1 it will take the data from Table2 and copy to the InvTemplate worksheet... do something ... delete the info in InvTemplate and then the loop will go to i = 2, to take the date of Table3 and so on...

 

[Fluff]

Did the change I suggested do what you needed?

 

[Chiwidan]

Did the change I suggested do what you needed?

Hi Fluff... it did!!!! yay!!!! thanks heaps... It copied the formula that was in the Table... what would be the code for values only?

Thanks again...

Dan

 

[Fluff]

How about

With shCalculations.Range("Table" & i + 1)
   shInvTemplate.Range("X3").Resize(.Rows.Count, .Columns.Count).Value = .Value
End With

 

[Chiwidan]

How about

With shCalculations.Range("Table" & i + 1)
   shInvTemplate.Range("X3").Resize(.Rows.Count, .Columns.Count).Value = .Value
End With

'copy NHI from Calculations to Invoice Template


    shCalculations.Range("Table" & i + 1 & "[NHI]").Copy shInvTemplate.Range("X3")
    
With shCalculations.Range("Table" & i + 1 & "[NHI]")

    shInvTemplate.Range("X3").Resize(.Rows.Count, .Columns.Count).Value = .Value

It did the trick!!!!! Genius!!

 

[Fluff]

You're welcome & thanks for the feedback.