Hi Everyone,
I'm been getting brain meltdown trying to solve this one and have run out of ideas and wondered if anyone sees anything obvious.
It relates to 'filling across' vertically in a specified range (for example, take M97-M115 and then 'fill' from the last column to the right for as many columns as there are to bring it up to the current date like this:
For this sheet:
I managed to use some help I was given by someone, and adapt some code so that when I open the workbook it runs the code below, and then just adds the dates across for the last column and 'fills' it right up to the current month on this sheet perfectly:
The issue is, I can't tweak it to do the same on a similar sheet in the same workbook, because for example, AW-AX are a 'merged' cell with the date in it. AW and AX aren't merged though, from row 8 onwards. This is the sheet:
I'm just trying to automate similar to the above, where when the vba macro is run it does the same as the other sheet, and does these logical steps:
1) Find current month
2) Find the last two columns (in this example, AW:AX)
3) Extend them by another two (so AY-AZ then have Jul-19) in them.
I couldn't find a way to upload the sheet so apologies if it causes confusion pasting the images in.
I think my head is going to go boom soon :D
Any help greatly appreciated,
Best Regards,
Paul
I'm been getting brain meltdown trying to solve this one and have run out of ideas and wondered if anyone sees anything obvious.
It relates to 'filling across' vertically in a specified range (for example, take M97-M115 and then 'fill' from the last column to the right for as many columns as there are to bring it up to the current date like this:
For this sheet:
I managed to use some help I was given by someone, and adapt some code so that when I open the workbook it runs the code below, and then just adds the dates across for the last column and 'fills' it right up to the current month on this sheet perfectly:
VBA Code:
Sub extend_all_tab_dates_across_on_manu_sector_trends()
Application.ScreenUpdating = False
'Extend dates in "Manu Sector Trends - USE THIS"
Dim lastColumn As Long, dif As Long, ws As Worksheet
For Each ws In Sheets(Array("Manu Sector Trends - USE THIS"))
lastColumn = ws.Cells(97, ws.Columns.Count).End(xlToLeft).Column
dif = DateDiff("m", ws.Cells(97, lastColumn), Date)
If dif > 0 Then
ws.Cells(97, lastColumn - 1).Resize(40, 1).AutoFill Destination:=ws.Cells(97, lastColumn - 1).Resize(40, dif + 1)
End If
Next ws
'Extend dates in "NON-Manu Sector Trends - USE THIS"
For Each ws In Sheets(Array("NON-Manu Sector Trends - USE"))
lastColumn = ws.Cells(97, ws.Columns.Count).End(xlToLeft).Column
dif = DateDiff("m", ws.Cells(97, lastColumn), Date)
If dif > 0 Then
ws.Cells(97, lastColumn - 1).Resize(40, 1).AutoFill Destination:=ws.Cells(97, lastColumn - 1).Resize(40, dif + 1)
End If
Next ws
Application.ScreenUpdating = True
End Sub
The issue is, I can't tweak it to do the same on a similar sheet in the same workbook, because for example, AW-AX are a 'merged' cell with the date in it. AW and AX aren't merged though, from row 8 onwards. This is the sheet:
I'm just trying to automate similar to the above, where when the vba macro is run it does the same as the other sheet, and does these logical steps:
1) Find current month
2) Find the last two columns (in this example, AW:AX)
3) Extend them by another two (so AY-AZ then have Jul-19) in them.
I couldn't find a way to upload the sheet so apologies if it causes confusion pasting the images in.
I think my head is going to go boom soon :D
Any help greatly appreciated,
Best Regards,
Paul