CODEWITHSUNDEEP
vbaexcel
Angel
Angel

Reputation: 71

Copy worksheet and rename it and declare variable vBA

I am copying a worksheet and rename it using the cell values from 3rd sheet. THe only issue I am having is how do I declare the new sheet a a variable since i will be working on that sheet? I get an error saying "expected: end of statement" on the the last line.

Dim wsNew As Worksheet
Dim wsIntro As Worksheet
Dim wsUp As Worksheet
Set wsUp = Worksheets("Sheet1")

Set wsIntro = Worksheets("Instructions")

Worksheets("Sheet1").Copy after:=Sheets(Worksheets.Count)
With ActiveSheet.UsedRange
.Value = .Value
End With
ActiveSheet.name = wsIntro.Range("b6").Value & wsIntro.Range("b7").Value
Dim wsAllo As Worksheet
Set wsAllo = "wsIntro.Range("b6").Value & wsIntro.Range("b7").Value"

Upvotes: 0

Views: 3343

Answers (1)

YowE3K
YowE3K

Reputation: 23974

As the worksheet that you are trying to set a reference to is the ActiveSheet, you can simply change

Set wsAllo = "wsIntro.Range("b6").Value & wsIntro.Range("b7").Value"

to

Set wsAllo = ActiveSheet

Refactoring your code slightly gives:

Dim wsNew As Worksheet
Dim wsIntro As Worksheet
Dim wsUp As Worksheet
Dim wsAllo As Worksheet

Set wsUp = Worksheets("Sheet1")
Set wsIntro = Worksheets("Instructions")

'You shouldn't use "Sheets(Worksheets.Count)" - it will sometimes not do
'what you expect (when you have Charts as well as Worksheets in the Workbook)
'Use either "Sheets(Sheets.Count)" to place the new sheet as the last sheet
'in the workbook or use "Worksheets(Worksheets.Count)" to place the new sheet
'after the last worksheet in the workbook (but possibly with Charts after that)    
wsUp.Copy After:=Sheets(Sheets.Count)
Set wsAllo = ActiveSheet
With wsAllo
    .Name = wsIntro.Range("b6").Value & wsIntro.Range("b7").Value
    .UsedRange.Value = .UsedRange.Value
End With

Upvotes: 2

Related Questions