CODEWITHSUNDEEP
excelvba
srt
srt

Reputation: 541

End of statement error while naming a workbook by adding some strings

Sub openwb()

Dim sb As String

Dim wb, dwb As Workbook

Dim ws As Worksheet

Set dwb = ActiveWorkbook

Set ws = dwb.Sheets("Home")

sb = ws.Range("F4").Value

Set wb = sb&"_Powertrain Metrics_" & (Format(Date, "YYYYMMDD") & ".xlsm")

Debug.Print wb

End Sub

Here on 8th line, the portion "_Powertrain Metrics_" is highlighted and says "Compile error:Expected end of statement". Can u tell me what is the problem here?Is it anything about strings i used?

Upvotes: 2

Views: 1159

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149305

A. Change

Dim wb, dwb As Workbook

to

Dim wb As Workbook, dwb As Workbook

In VBA you have to explicitly declare the variables else they will be considered as variants.

B. Change

Set wb = sb&"_Powertrain Metrics_" & (Format(Date, "YYYYMMDD") & ".xlsm")

to

Set wb = Workbooks.Open(sb & "_Powertrain Metrics_" & Format(Date, "YYYYMMDD") & ".xlsm")

You were missing a SPACE before and after the &

Upvotes: 1

Related Questions