Find last empty column, then fill across and down the added column up to current month (Apr-20)

paul2342

New Member
Joined
Apr 22, 2020
Messages
1
Office Version
  1. 2016
Platform
  1. Windows
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:
1587585765966.png


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:


1587586024204.png


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
 

Attachments

  • 1587585721846.png
    1587585721846.png
    151.3 KB · Views: 0

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Watch MrExcel Video

Forum statistics

Threads
1,127,318
Messages
5,623,974
Members
416,002
Latest member
Neshx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top