recording macro that updates?

[erniepoe]

Hi All,

I want to record a macro wherein each new week you just drag everything in one column over to the next column (literally just copy column AM and paste on AN, for example). The reason for the macro is that I have to do this on roughly 75 tabs, so it takes some time.

However, I'd like for it to be able to update weekly, so that each week it's always copying and pasting the last column with data into the first blank column. So next week, column AN would be copied to AO, and on and on.

Is there some way to perhaps have the macro find the first blank cell in Row 1 or whatever, and then paste the column to the left of that into that blank column, and then each time you run the macro, it just adds a new column to your spreadsheet?

Hopefully my explanation has been clear. Thank you so much for your time.

Best,
Ernie

 

[Wanderer_geologist]

Hi

fold answers
formatting

that really easy to implement.

Timed execution

That not so easy as to have a timer the file need to run. So else you use schedule task from windows to open the files weekly and there is a run comdition on the workbook opening to check the week day and run the formatting or

you use a workbook with the formatting macro which will open the target files and run the formatting.

Are those files in the same folder ?

 

[erniepoe]

Hi

fold answers
formatting

that really easy to implement.

Timed execution

That not so easy as to have a timer the file need to run. So else you use schedule task from windows to open the files weekly and there is a run comdition on the workbook opening to check the week day and run the formatting or

you use a workbook with the formatting macro which will open the target files and run the formatting.

Are those files in the same folder ?

Thanks for the reply Wander Geologist,

In terms of a timer, i wouldnt need that, I would just go in an run the macro button and that would be that. Can you go into more detail on the fold answers and formatting part of your answer? I'm unclear on that concept and how to implement. Thanks!

 

[Michael M]

Try this way....it will do ALL worksheets

Sub MM1()
Dim ws As Worksheet, lc As Integer
For Each ws In Worksheets
    With ws
    lc = .Cells(2, Columns.Count).End(xlToLeft).Column
        .Columns(lc).Copy .Columns(lc).Offset(, 1)
    End With
Next ws
End Sub

 

[erniepoe]

thanks Michael M,

if i wanted to amend your code to just do worksheets "1 HYI" to "9AQH" (they are adjacent worksheets), would that be possible?

 

[Michael M]

I thought you wanted to do 75 tabs ??
To do it on 2 adjacent sheets would be a simple copy / paste...no code needed.

But if you must

Sub MM1()
Dim ws As Worksheet, lc As Integer
For Each ws In Worksheets
    If ws.Name = "1 HYI" Or ws.Name = "9AQH" Then
        With ws
        lc = .Cells(2, Columns.Count).End(xlToLeft).Column
            .Columns(lc).Copy .Columns(lc).Offset(, 1)
        End With
    End If
Next ws
End Sub

 

[erniepoe]

I thought you wanted to do 75 tabs ??
To do it on 2 adjacent sheets would be a simple copy / paste...no code needed.

But if you must

Sub MM1()
Dim ws As Worksheet, lc As Integer
For Each ws In Worksheets
    If ws.Name = "1 HYI" Or ws.Name = "9AQH" Then
        With ws
        lc = .Cells(2, Columns.Count).End(xlToLeft).Column
            .Columns(lc).Copy .Columns(lc).Offset(, 1)
        End With
    End If
Next ws
End Sub

Thank you Michael!

 

[Michael M]

glad to help and thx foir the feedback...