recording macro that updates?

erniepoe

Active Member
Joined
Oct 23, 2006
Messages
330
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
 

Some videos you may like

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Wanderer_geologist

New Member
Joined
Jan 13, 2019
Messages
26
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

Active Member
Joined
Oct 23, 2006
Messages
330
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,238
Office Version
  1. 2013
Platform
  1. Windows
Try this way....it will do ALL worksheets

Code:
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

Active Member
Joined
Oct 23, 2006
Messages
330

ADVERTISEMENT

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

Well-known Member
Joined
Oct 27, 2005
Messages
19,238
Office Version
  1. 2013
Platform
  1. Windows
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

Code:
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

Active Member
Joined
Oct 23, 2006
Messages
330
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

Code:
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

Well-known Member
Joined
Oct 27, 2005
Messages
19,238
Office Version
  1. 2013
Platform
  1. Windows
glad to help and thx foir the feedback...(y)
 

Watch MrExcel Video

Forum statistics

Threads
1,108,816
Messages
5,525,061
Members
409,617
Latest member
Lenaf

This Week's Hot Topics

Top