CODEWITHSUNDEEP
vbaexcel
Artur Rutkowski
Artur Rutkowski

Reputation: 537

Referring to newly added worksheets

I want to refer to newly added sheets. They are added by a loop.

They are named Sheet1, Sheet2, Sheet3 etc. I also have a variable n which equals to 1 when a Sheet1 is created, equals to 2 when a Sheet2 is created, equals to 3 when a Sheet3 is created, and so on.

My code looks like the following: 

For i = 2 To 1000

If Worksheets("DATA").Cells(i, "A").Value <> "" And Worksheets("DATA").Cells(i, "A").Value <> "TOTAL" Then

Worksheets("Template").Cells(1, "C").Value = Worksheets("DATA").Cells(i, "B").Value
Worksheets("Template").Cells(2, "C").Value = Worksheets("DATA").Cells(i, "C").Value
Worksheets("Template").Cells(3, "C").Value = Worksheets("DATA").Cells(i, "A").Value
Worksheets("Template").Cells(5, "D").Value = Worksheets("DATA").Cells(i, "D").Value
Worksheets("Template").Cells(5, "E").Value = Worksheets("DATA").Cells(i, "E").Value

Sheets.Add

n = 0
n = n + 1

Worksheets("Template").Cells.Copy

Worksheets("Sheet & n").Range("A1").Paste 'Problem is in this line

Worksheets("Sheet" & n).Activate ' And in this line

    Application.PrintCommunication = False
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With

I have a problem with the lines marked with comments.
My question is how to refer to a Sheet1, Sheet2, etc by using the n variable?

Please be patient with me, as I am quite new to VBA. If my question is vague or hard to understand please let me know.
Thank you in advance.

Upvotes: 2

Views: 142

Answers (2)

Olle Sj&#246;gren
Olle Sj&#246;gren

Reputation: 5384

You can also save a reference to the worksheet when you create it, so you don't have to guess the name. Replace your line Sheets.Add with the first two of my lines:

Dim oSheet As Worksheet

Set oSheet = Sheets.Add

'***** Do what you need with the sheet
oSheet.Range("A1").Paste
Debug.Print oSheet.Name

'***** Clear the reference when you're done
Set oSheet = Nothing

Upvotes: 3

Siddharth Rout
Siddharth Rout

Reputation: 149335

Anything within quotes "" will be considered as string.

Change "Sheet & n" to "Sheet" & n

Also you should avoid the use of .Activate. INTERESTING READ

And one more thing :P

You don't need to Activate a sheet to paste to it. You can do it in one line. For Example.

ThisWorkbook.Sheets("Sheet1").Range("A1:A5").Copy _
ThisWorkbook.Sheets("Sheet2").Range("A1")

Upvotes: 3

Related Questions