0

I have an excel with multiple sheets and would like to copy or better say want to extend the last column every month.

Eg:- I have a sheet with sheet named sheet1,sheet2,sheet3,sheet4,sheet5...every sheet at the end of the month has formulas.Once a month is over I would like to add a new column with new month and copying the existing formula to the new column.Let say I have last month Jan and I need VBA to add new column with month as Feb and copy all the formula to the new column.

Sometimes I also need to copy multiple column (eg:-Column C-J) and replicate the next 8 column with new month and formula.

Tried with recording macro but the issue is it doesn't create a new column for every month it just copy paste it in same column rather than creating a new one for every month

Improve this question
5
  • Are you coping Data from different worksheets or are you duplicating a column on your Summary sheet and wanting to update the formulae in the new column? Commented Jun 16, 2016 at 8:42
  • Can you post your formula. This can be easily accomplished without the VBA. Commented Jun 16, 2016 at 9:21
  • Nope.All I want to do is there are multiple sheets and I would like to extend the same formula. ' Macro4 Macro ' ' Application.Calculation = xlManual Sheets(Array("Shee1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")). _ Select Sheets("Sheet1").Activate Columns("AN:AN").Select Selection.AutoFill Destination:=Columns("AN:AO"), Type:=xlFillDefault Columns("AN:AO").Select End Sub Above is the macro so inplace of copying from AN every time and copying to AN:AO every time .What I want it next time when AO is filled it should now copy from AO and move to AP Commented Jun 16, 2016 at 9:27
  • I was referring to your worksheet formula. Not your macro. Commented Jun 16, 2016 at 9:30
  • It is multiple formulas of sumifs,add,subtract and percentage on multiple rows.All I wanted is copy the same from last column to next column so once I only feed the data an all get updated as per as new month which sumifs can do Commented Jun 16, 2016 at 9:44

3 Answers 3

Reset to default
0

It is difficult to understand the problem without seeing the formulas.

It sounds like you could start by using the AutoFill. You could do this manually by selecting the range you want to copy and dragging the cross in the bottom right corner. This will update the month automatically.

You can achieve this with VBA, such as:

Public Sub copyRange()
    Dim rngSource As Range
    Dim rngDestination As Range

    rngSource = ActiveSheet.Range("A1:A20")
    rngDestination = ActiveSheet.Range("B1:B20")

    rngSource.AutoFill Destination:=rngDestination
End Sub

Either way, I can't tell how to reset the formulae for the new months without seeing the cell code.

UPDATE: To AutoFill multiple columns on multiple tabs 

Public Sub copySpecifiedColumns()
    copyRanges InputBox("How many columns do you wish to copy?", "Copy Columns", "1")
End Sub
Private Sub copyRanges(copyCols As Byte)
    Dim ws As Worksheet, lastCol As Integer, lastRow As Integer
    Dim rngSource As Range, rngDestination As Range
    Dim sheetList As Variant
    sheetList = Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")

    For Each ws In ThisWorkbook.Sheets
        If (UBound(Filter(sheetList, ws.Name)) > -1) Then
            lastCol = ws.Cells(1, ws.Columns.Count).End(xlToLeft).Column
            lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row
            Set rngSource = ws.Range(ws.Cells(1, lastCol - copyCols + 1), _
                ws.Cells(lastRow, lastCol))
            Set rngDestination = ws.Range(ws.Cells(1, lastCol - copyCols + 1), _
                ws.Cells(lastRow, lastCol + copyCols))

            rngSource.AutoFill rngDestination
        End If
    Next ws
End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

20 Comments

Below is the Macro ' Macro4 Macro ' ' Application.Calculation = xlManual Sheets(Array("Shee1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")). _ Select Sheets("Sheet1").Activate Columns("AN:AN").Select Selection.AutoFill Destination:=Columns("AN:AO"), Type:=xlFillDefault Columns("AN:AO").Select End Sub Above is the macro so inplace of copying from AN every time and copying to AN:AO every time .What I want it next time when AO is filled it should now copy from AO and move to AP
Thanks partially solves my problem.But I am unable to see where multiple sheets are selected.It working fine for the only one sheet I want the same for multiple sheet like sheet 1 ,sheet2,sheet 3 etc.
I see. Although your code includes an array of sheet names, it then selects Sheet1, ignoring the array, so I thought that is where you wanted to apply the autofill. I have updated the code at the end of my answer to iterate through worksheets based on the names held in an array. Hope this works for you.
If you want to copy the range in all worksheets in the workbook you can remove the If statement and the array.
I updated the array with sheet name but I am getting error now "Autofill method of Range class failed..
|
0

I Agree it's a bit difficult to understand what you are trying to achieve here. From what I understand if you want to make a copy of last column in the next column in each sheet and change the 1st cell of that column to the month at the time. This code can help.

Sub copy_col()


Dim lColumn As Long
For Each Sheet In Worksheets

lColumn = Sheet.Cells(1, Columns.Count).End(xlToLeft).Column
Sheet.Columns(lColumn).Copy Sheet.Columns(lColumn + 1)
Sheet.Cells(1, lColumn + 1).Value = Month(Now())

Next Sheet
End Sub

If this is not what you want then please explain your problem more briefly.

Thanks

Improve this answer

1 Comment

Nope.All I want to do is there are multiple sheets and I would like to extend the same formula. ' Macro4 Macro ' ' Application.Calculation = xlManual Sheets(Array("Shee1", "Sheet2", "Sheet3", "Sheet4", "Sheet5")). _ Select Sheets("Sheet1").Activate Columns("AN:AN").Select Selection.AutoFill Destination:=Columns("AN:AO"), Type:=xlFillDefault Columns("AN:AO").Select End Sub Above is the macro so inplace of copying from AN every time and copying to AN:AO every time .What I want it next time when AO is filled it should now copy from AO and move to AP
0

Extend List and Update Formula

Usage

ExtendList 5, "Sheet1", "Sheet3"

Where 1. 5, is the Column to Duplicate to the next empty Column 2. "Sheet1" is the sheet referenced in the original formula 3. "Sheet3" is the replace sheet name

Original Formula

=Sheet1!$A10

New Formula

=Sheet3!$A10

Sub ExtendList(SourceColumn As Long, OriginalSheetName As String, NewSheetName As String)
    On Error Resume Next
    Dim newColumnNumber As Integer
    Worksheets(NewSheetName).Name = NewSheetName
    If Err.Number <> 0 Then
        Err.Clear
        Exit Sub
    End If
    On Error GoTo 0
    
    newColumnNumber = Range(Cells(1, Columns.Count), Cells(Rows.Count, Columns.Count)).End(xlToLeft).Offset(, 1).Column

    Columns(SourceColumn).Copy Columns(newColumnNumber)
    
    Columns(newColumnNumber).Replace What:=OriginalSheetName, Replacement:=NewSheetName, lookat:=xlPart, _
        SearchOrder:=xlByColumns, MatchCase:=True, SearchFormat:=False, _
        ReplaceFormat:=False

End Sub

This will only work if the column reference is absolute:

Correct

$A1 or $A$2

Incorrect

A1 or A$1

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.