CODEWITHSUNDEEP
vbaexcel
subtilty
subtilty

Reputation: 33

Using a variable as a sheet name

I am getting a RunTime 13 error when trying to use a variable for a sheetname as per below:

Sub inputdata()

Set asheet1 = ThisWorkbook.Worksheets("input").Range("D12")
Set rangeDate = ThisWorkbook.Worksheets("input").Range("inputdate")

Range("F12:M12").Copy
Sheets(asheet1).Select

It is erroring on the line Sheets(asheet1).Select

Any help would be great thanks!

Upvotes: 1

Views: 52805

Answers (2)

Ateszki
Ateszki

Reputation: 2255

The asheet1 is not a string, you are asigning a range object to it . You should declare asheet1 as string and the change this line to

Dim asheet1 as string
asheet1 = ThisWorkbook.Worksheets("input").Range("D12").Value

That should make it work!

Edit

removed the Set keyword from the string var.

Upvotes: 4

shahkalpesh
shahkalpesh

Reputation: 33476

Option Explicit

Sub inputdata()
dim inputSheet as WorkSheet
dim aCellOnInputSheet as Range
dim inputDateCell as Range
dim userSheetName as String


Set inputSheet = ThisWorkbook.Worksheets("input") 

Set aCellOnInputSheet = inputSheet.Range("D12")
userSheetName = aCellOnInputSheet.Value
Set inputDateCell = inputSheet.Range("inputdate")

Range("F12:M12").Copy
Sheets(userSheetName).Select

End Sub

EDIT: A couple of points
1) Option Explicit is a must.
2) Define variables and name it appropriately. i.e. define variable which refers to a cell/Range with the name range instead of aSheet - it confuses the reader

Upvotes: 1

Related Questions