2

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.

pnuts
  • 58,317
  • 11
  • 87
  • 139
Artur Rutkowski
  • 487
  • 4
  • 9
  • 17

2 Answers2

3

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")
Community
  • 1
  • 1
Siddharth Rout
  • 147,039
  • 17
  • 206
  • 250
3

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
Olle Sjögren
  • 5,315
  • 3
  • 31
  • 51