Autofill with Range Names

[Deborah Holt]

I have a worksheet where I want to autofill the fields in rows 4 to 8from the last populated column, into the next column.
Rows 4 to 8 will always be ‘fixed’.
Currently, the last populated column is BA.

I can establish the LastCol & NextCol variables but I can’t passthem to my AutoFill command successfully.
Basically, I want to replace the ‘hard-coded’ BA & BB columns withtheir variable values LastCol & NextCol.

Any help much appreciated – code below

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

Dim NextCol As Integer
NextCol = LastCol + 1
End Sub


Sub newmonth_book()
'
' newmonth_book Macro
'
Sheets("Book").Select
Dim selection1 As Range
Dim selection2 As Range


Set selection1 =Range("BA4:BA8")
Set selection2 = Range("BA4:BB8")

selection1.AutoFillDestination:=selection2, Type:=xlFillDefault
End Sub

 

[BarryL]

is the below what you are looking to do?

Sub hardcode()
Dim lc As Long, nc As Long, ws As Worksheet
Set ws = ThisWorkbook.Sheets("Book")


With ws
Let lc = .Cells(4, .Columns.Count).End(xlToLeft).Column
Let nc = lc + 1


.Range(.Cells(4, nc), .Cells(8, nc)).Value = .Range(.Cells(4, lc), .Cells(8, lc)).Value




End With
End Sub

 

[Deborah Holt]

Sort of, however, your code is just copying the values where as I want to autofill, keeping the formatting and changing the monthname.
Here is the Range BA4 to BA8 that I'm trying to drag to the next column.

[TABLE="width: 82"]
<colgroup><col width="109" style="width: 82pt; mso-width-source: userset; mso-width-alt: 3986;"> <tbody>[TR]
[TD="width: 109, bgcolor: red, align: right"]Apr-18
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 123,723
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]41.73%[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]38.56%
[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] 89.31 [/TD]
[/TR]
</tbody>[/TABLE]

Rows 5 to 8 contain formulae.
Many thanks for your help so far

 

[Deborah Holt]

Hi BarryL I’ve re-read your reply and I think if I just replace the ‘Value’ parts with ‘Autofill’ parts then that should do the trick.
I”ll have another go tomorrow
Many thanks again

 

[BarryL]

Hi Deborah,

understood, can you try the below?

Sub hardcode()
Dim lc As Long, nc As Long, ws As Worksheet
Set ws = ThisWorkbook.Sheets("Book")




With ws
    Let lc = .Cells(4, .Columns.Count).End(xlToLeft).Column
    Let nc = lc + 1
    .Range(.Cells(4, lc), .Cells(8, lc)).AutoFill Destination:=.Range(.Cells(4, lc), .Cells(8, nc)), Type:=xlFillDefault
End With
End Sub