Using a variable to select an Excel sheet in vba

Question

I have the following Excel VBA code.

I already copied some data from another Excel document. I need to paste these date in a second document that contains an Excel sheet for every week.

The Excel sheets are called "week 01", "week 02", "..." , "week 52".

Now I need to select the sheet with the correct number to paste my copied data there.

Sub Macro1()

Dim number As String
Dim year As String

number = Application.InputBox(Prompt:="insert week number (01-52))", Type:=2)
year = Application.InputBox(Prompt:="insert year (YYYY)", Type:=2)

Workbooks.Open Filename:= _
"F:\documents\" & year & "\example " & number & ".xlsm"

Sheets("Week" number).Select

Range("M4").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

End Sub

I know that the following code isn't correct. How can I select the correct excel sheet, using the variable "number"?

Sheets("Week" number).Select

Dubison Dubison · Accepted Answer · 2015-01-20T13:58:50

about the below code, did you miss to put "&" in between, or you just miss typed it?

Sheets("Week" number).Select

should be

Sheets("Week " & number).Select 'there is a space after week??

and please pay attention to the spaces between " ". They can mass up your code. like the one below. there is a space after "\example ":

"F:\documents\" & year & "\example " & number & ".xlsm"

Using a variable to select an Excel sheet in vba

1 Answers