I have a summary of data by month in one sheet and the raw data in another sheet. Instead of creating multiple worksheets for my raw data for each month, I want to wipe out previous month data and replace with new data. To do this I will have to copy the formulas that created the summary onto another column (for the new month) and then copy and paste value of the current month summary data (so once the source raw data have changed it wouldn't change my values there).
Example
A B C D
Jan Feb Mar Apr
1
5
6
7
I would like my end result to look like (i.e. copy from A3:A6 and paste onto B3. Then next month it will copy from B3:B6 and paste onto C3, and so on)
A B C D
Jan Feb Mar Apr
1 1
5 5
6 6
7 7
Jan and Feb are the same at the moment because the copy and paste will happen before new raw data has been replaced.
Hope it makes sense. I have very little knowledge in writting VB but can manage to read and understand the simple ones. Could anyone help me with a macro that will do this automatically?
This macro uses an inputbox to ask what month you are updating. Use numeric values instead of typing in the month. Example: Jan. =1, Feb. = 2, Mar. = 3. I am assuming you would not want to update January so the macro will exit if 1 is typed in the inputbox (unless it is updated from December then we can change it).
Hope this helps.
Sub Update_Month()
Dim answer As Variant
Dim j
j = 3
answer = InputBox("What month are you updating?" & vbCrLf & _
"Ex: January =1, February = 2, March=3, etc.")
Thanks very much for the code - it worked beautifully! Although it worked differently to what I'd expected, it actually gave me a good idea to improve my overall summary sheet.
Instead of going horizontal, I need to go vertical.
I have few data, and I need to update the rows daily by adding new row, and pasting the value of previous data and updating the current row. I have about 15 different sub-set of rows that needs to be updated daily.
Thanks very much for the code - it worked beautifully! Although it worked differently to what I'd expected, it actually gave me a good idea to improve my overall summary sheet.
has helped 1687 users this month
Thanks very much for the code - it worked beautifully! Although it worked differently to what I'd expected, it actually gave me a good idea to improve my overall summary sheet.
Thanks again - you are a legend!
Cheers
DitzySuz
I have similar issue.
Instead of going horizontal, I need to go vertical.
I have few data, and I need to update the rows daily by adding new row, and pasting the value of previous data and updating the current row. I have about 15 different sub-set of rows that needs to be updated daily.
do you think you will be able to help me out?
Sub Button924_Click()
Dim answer As Variant
Dim j
j = 3
answer = InputBox("What month are you updating?" & vbCrLf & _
"E.g. Feb = 2, Mar = 3, Apr = 4, May = 5 ... etc")
Select Case answer
Case 1
Exit Sub
Case 2
For j = 8 To 8
Range("F8:F400").Select
Selection.Copy
Range("G8:G400").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Next j
Case 3
For j = 8 To 8
Range("F8:F400").Select
Selection.Copy
Range("H8:H400").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Next j
Case 4
For j = 8 To 8
Range("F8:F400").Select
Selection.Copy
Range("I8:I400").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Next j
Case 5
For j = 8 To 8
Range("F8:F400").Select
Selection.Copy
Range("J8:J400").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Next j
Case 6
For j = 8 To 8
Range("F8:F400").Select
Selection.Copy
Range("K8:K400").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Next j
Case 7
For j = 8 To 8
Range("F8:F400").Select
Selection.Copy
Range("L8:L400").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Next j
Case 8
For j = 8 To 8
Range("F8:F400").Select
Selection.Copy
Range("M8:M400").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Next j
Case 9
For j = 8 To 8
Range("F8:F400").Select
Selection.Copy
Range("N8:N400").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Next j
Case 10
For j = 8 To 8
Range("F8:F400").Select
Selection.Copy
Range("O8:O400").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Next j
Case 11
For j = 8 To 8
Range("F8:F400").Select
Selection.Copy
Range("P8:P400").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Next j
Case 12
For j = 8 To 8
Range("F8:F400").Select
Selection.Copy
Range("Q8:Q400").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Next j
End Select
End Sub