Hi all,
New to VBA and trying to practice a bit by creating a calendar, but struggling to copy a dynamic range to the new workbook. If I copy to H4:I20 it works fine, but I don't know how many rows will be populated and hence want this to be dynamic.
I'm using LastRow to count to the last row and then trying to use that to copy only the rows that are populated.
I feel like I'm missing something very silly and any help would be greatly appreciated.
Thanks in advance!
Sub Calendar()
Dim newbook As Workbook
Dim ShNew As Worksheet
Set newbook = Workbooks.Add
Dim LastRow As Long
LastRow = Cells(Rows.Count, 9).End(xlUp).Row
Set ShNew = newbook.Worksheets.Add
ShNew.Name = "January"
ShNew.Range("B2").FormulaR1C1 = "1/1/2020"
With ShNew.Range("B2:B4")
.ReadingOrder = xlContext
.MergeCells = True
.NumberFormat = "mmmm"
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
End With
With ShNew.Range("B2:B4").Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 12611584
End With
With ShNew.Range("B2:B4").Font
.ThemeColor = xlThemeColorDark1
.TintAndShade = 0
.Bold = True
End With
Workbooks("Rota_Test.xlsb").Worksheets("Info").Range("H4:I" & LastRow).Copy _
Range("A5")
End Sub
Bookmarks