Replace Cell Refs with variable names

[Deborah Holt]

Hi all,

I've composed this macro which does what I need; (finds last used column in row4 and drags the contents 1 column to the right). In this case, last used column= BA

Sub newmonth_book()
Sheets("Book").Select
Range(Cells(4, 53), Cells(8, 53)).AutoFill Destination:=Range(Cells(4, 53),Cells(8, 54)), Type:=xlFillDefault
End Sub

However, I want to replace the values of 53 with a variable name as the lastused column is different every time.
I can establish the last used column using this;

Sub BOOK()
'Find the last used column in a Row: row 4 in this example
Sheets("Book").Select
Dim LastCol As Long
With ActiveSheet
LastCol = .Cells(4, .Columns.Count).End(xlToLeft).Column
End With

Dim NextCol As Long
NextCol = LastCol + 1

MsgBox (NextCol)
End Sub

However,when I replace the 'hard-coded' values with names, I can't get it towork and I get this message "Application-defined or object-definederror"
This is my code

Sub newmonth_book()
Sheets("Book").Select
Range(Cells(4, LastCol), Cells(8, LastCol)).AutoFillDestination:=Range(Cells(4, LastCol), Cells(8, NextCol)), Type:=xlFillDefault
End Sub

Can anyone advise how I get this to work?


Thanks in advance

 

[Fluff]

Duplicate https://www.mrexcel.com/forum/excel-questions/1061600-autofill-range-names.html#post5098086

Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).