Excel - A macro to copy/paste a selected range

December 2016



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 that


Thanks to WutUp WutUp for this tip on the forum]

Related :

This document entitled « Excel - A macro to copy/paste a selected range » from CCM (ccm.net) is made available under the Creative Commons license. You can copy, modify copies of this page, under the conditions stipulated by the license, as this note appears clearly.