SLElephant
New Member
- Joined
- Jul 6, 2023
- Messages
- 5
- Office Version
- 365
I am trying to get a user defined sheet name to work for a xlookup. When I hard code the sheet name it works But when I try to use a name defined it doesn't
Dim CurMonth As Integer
Dim PreMonth As Integer
Dim PreCycleTime As String
Dim PreCPHRate As String
' InsertSheets
'
Sheets.Add before:=Sheets(1)
CurMonth = InputBox("Enter Two Digit Numarical Current Month and Press Enter")
PreMonth = CurMonth - 1
PreCycleTime = ("Cycle Times 2023." & PreMonth)
'***************************************************************************************************************************
Sheets(1).Name = "CPH Rates 2023." & CurMonth
Sheets.Add After:=Sheets(1)
Sheets(2).Name = "Cycle Times 2023." & CurMonth
Sheets("Cycle Times 2023." & CurMonth).Select
'
'
'
'Cycle times Populate
'
THIS WORKS
ActiveCell.Formula = _
"=XLOOKUP(A2&B2&C2, 'Cycle Times 2023.06'!A:A & 'Cycle Times 2023.06'!B:B & 'Cycle Times 2023.06'!C:C, 'Cycle Times 2023.06'!K:K)"
Not THIS
Range("N2").Select
ActiveCell.Formula = _
"=XLOOKUP(A2&B2&C2, 'PreCycleTime'!A:A&'PreCycleTime'!B:B&'PreCycleTime'!C:C,'PreCycleTime'!L:L)"
'
Dim CurMonth As Integer
Dim PreMonth As Integer
Dim PreCycleTime As String
Dim PreCPHRate As String
' InsertSheets
'
Sheets.Add before:=Sheets(1)
CurMonth = InputBox("Enter Two Digit Numarical Current Month and Press Enter")
PreMonth = CurMonth - 1
PreCycleTime = ("Cycle Times 2023." & PreMonth)
'***************************************************************************************************************************
Sheets(1).Name = "CPH Rates 2023." & CurMonth
Sheets.Add After:=Sheets(1)
Sheets(2).Name = "Cycle Times 2023." & CurMonth
Sheets("Cycle Times 2023." & CurMonth).Select
'
'
'
'Cycle times Populate
'
THIS WORKS
ActiveCell.Formula = _
"=XLOOKUP(A2&B2&C2, 'Cycle Times 2023.06'!A:A & 'Cycle Times 2023.06'!B:B & 'Cycle Times 2023.06'!C:C, 'Cycle Times 2023.06'!K:K)"
Not THIS
Range("N2").Select
ActiveCell.Formula = _
"=XLOOKUP(A2&B2&C2, 'PreCycleTime'!A:A&'PreCycleTime'!B:B&'PreCycleTime'!C:C,'PreCycleTime'!L:L)"
'