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")

 

[Chiwidan]

I am slowly getting there... I have the InvTemplate worksheet where the previous part of the Macro will put the information. I also have a template worksheet (shInvTemplate)

My next steps are:

1 To automatically create a worksheet which name will change every time:

'Creates Invoice Worksheet

    sheet_name = shMacros.Range("A24:A33").Cells(i, 1).Value
    Sheets.Add.Name = sheet_name

2 To copy the information of my Invoice Sample

3 To paste in the newly created worksheet...

But I got stuck... I asked in the forum:

how to refer a newly created worksheet?


I got the gust feeling I am missing something really simple...

Dan

 

[Fluff]

Please do not post the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.
You need to continue in your new thread.