Excel macro for copy & paste selected rangeSolved/Closed

Question

Hello 

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? 

Thank you in advance. 

DitzySuz

WutUp WutUp · Accepted Answer

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

DitzySuz · Answer

Hi Wutup

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

Excel macro for copy & paste selected range

2 responses

Similar discussions

Subscribe To Our Newsletter!