PaulyP
06-18-2022, 10:33 AM
Hi All,
I need help, it's been 15 years since I did any VBA and so I'm learning from scratch again - and not doing very well!
I am creating a workbook with sheets from April to March (Financial Year).
I am putting a macro button on each sheet to copy the current monthly data to subsequent months. i.e if I am updating figures on the July's sheet I want to then press the button and copy the July data on to the August, September, October... etc sheets.
I am ok with the button and macro and selecting and copying the data in VBA. The part I am having most trouble with, is selecting the all subsequent months from the sheet that is currently active.
In the code below:
I started with identifying the current sheet and then setting up an array containing each month.
After that there is some code for a warning box so I don't do it by accident.
Then the code for the cells to copy
Followed by the code for the sheets where I need to select all subsequent sheets - this is where I am stuck.
And then finally there is code to confirm that the operation has completed and copied the data to the remaining month - I managed to do a message listing all the remaining months - though it's probably not a very elegant way of doing it! :)
Sub Macro3()
CurrentSheet = ActiveSheet.Name
Dim SheetID(12) As String
SheetID(0) = "January"
SheetID(1) = "February"
SheetID(2) = "March"
SheetID(3) = "April"
SheetID(4) = "May"
SheetID(5) = "June"
SheetID(6) = "July"
SheetID(7) = "August"
SheetID(8) = "September"
SheetID(9) = "October"
SheetID(10) = "November"
SheetID(11) = "December"
For i = 0 To 11
If SheetID(i) = CurrentSheet Then
Dim Answer As VbMsgBoxResult
Answer = MsgBox("You are about to copy " & ActiveSheet.Name & " occupancy figures to the following month." _
& VBA.Constants.vbNewLine & "WARNING: THIS ACTION CAN NOT BE UNDONE!", vbOKCancel + vbQuestion + vbDefaultButton2, ActiveSheet.Name & " Occupancy Copy to Next Month")
If Answer = vbOK Then
Range("F4:F122").Select
Selection.Copy
Sheets(?????????).Select ' <<<<<<<<<<<<<<<<<<<<<This is where I am stuck! How to select all sheets following the current sheet.
Range("F4:F122").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets(CurrentSheet).Select
Else
Exit Sub
End If
Dim Months As String
Dim RemainingMonths(11) As String
For x = i + 1 To 11
RemainingMonths(x) = SheetID(x)
Next x
Months = Join(RemainingMonths)
MsgBox "Data copied to: " & Months
End If
Next i
End Sub
This all works fine if I just enter the name for a single month where the red ????? are, so hopefully someone can help me with selecting the months I need which varies depending on the currently active sheet.
I hope that's all clear and any help would be much appreciated.
Many thanks
Paul
I need help, it's been 15 years since I did any VBA and so I'm learning from scratch again - and not doing very well!
I am creating a workbook with sheets from April to March (Financial Year).
I am putting a macro button on each sheet to copy the current monthly data to subsequent months. i.e if I am updating figures on the July's sheet I want to then press the button and copy the July data on to the August, September, October... etc sheets.
I am ok with the button and macro and selecting and copying the data in VBA. The part I am having most trouble with, is selecting the all subsequent months from the sheet that is currently active.
In the code below:
I started with identifying the current sheet and then setting up an array containing each month.
After that there is some code for a warning box so I don't do it by accident.
Then the code for the cells to copy
Followed by the code for the sheets where I need to select all subsequent sheets - this is where I am stuck.
And then finally there is code to confirm that the operation has completed and copied the data to the remaining month - I managed to do a message listing all the remaining months - though it's probably not a very elegant way of doing it! :)
Sub Macro3()
CurrentSheet = ActiveSheet.Name
Dim SheetID(12) As String
SheetID(0) = "January"
SheetID(1) = "February"
SheetID(2) = "March"
SheetID(3) = "April"
SheetID(4) = "May"
SheetID(5) = "June"
SheetID(6) = "July"
SheetID(7) = "August"
SheetID(8) = "September"
SheetID(9) = "October"
SheetID(10) = "November"
SheetID(11) = "December"
For i = 0 To 11
If SheetID(i) = CurrentSheet Then
Dim Answer As VbMsgBoxResult
Answer = MsgBox("You are about to copy " & ActiveSheet.Name & " occupancy figures to the following month." _
& VBA.Constants.vbNewLine & "WARNING: THIS ACTION CAN NOT BE UNDONE!", vbOKCancel + vbQuestion + vbDefaultButton2, ActiveSheet.Name & " Occupancy Copy to Next Month")
If Answer = vbOK Then
Range("F4:F122").Select
Selection.Copy
Sheets(?????????).Select ' <<<<<<<<<<<<<<<<<<<<<This is where I am stuck! How to select all sheets following the current sheet.
Range("F4:F122").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets(CurrentSheet).Select
Else
Exit Sub
End If
Dim Months As String
Dim RemainingMonths(11) As String
For x = i + 1 To 11
RemainingMonths(x) = SheetID(x)
Next x
Months = Join(RemainingMonths)
MsgBox "Data copied to: " & Months
End If
Next i
End Sub
This all works fine if I just enter the name for a single month where the red ????? are, so hopefully someone can help me with selecting the months I need which varies depending on the currently active sheet.
I hope that's all clear and any help would be much appreciated.
Many thanks
Paul