Excel – Macro for copy & paste selected range
Issue
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?
Solution
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.")
Select Case answer
Case 1
Exit Sub
Case 2
For j = 3 To 6
Range("B" & j) = Range("A" & j)
Next j
Case 3
For j = 3 To 6
Range("C" & j) = Range("B" & j)
Next j
Case 4
For j = 3 To 6
Range("D" & j) = Range("C" & j)
Next j
Case 5
For j = 3 To 6
Range("E" & j) = Range("D" & j)
Next j
Case 6
For j = 3 To 6
Range("F" & j) = Range("E" & j)
Next j
Case 7
For j = 3 To 6
Range("G" & j) = Range("F" & j)
Next j
Case 8
For j = 3 To 6
Range("H" & j) = Range("G" & j)
Next j
Case 9
For j = 3 To 6
Range("I" & j) = Range("H" & j)
Next j
Case 10
For j = 3 To 6
Range("J" & j) = Range("I" & j)
Next j
Case 11
For j = 3 To 6
Range("K" & j) = Range("J" & j)
Next j
Case 12
For j = 3 To 6
Range("L" & j) = Range("K" & j)
Next j
End Select
End Sub
Note
Thanks to
WutUp WutUp for this tip on the forum]
Excel find string in a range of cells