Menu
  1. Accueil
  2. Tech Forum
  3. Programming

Excel macro for copy & paste selected range [Solved/Closed]

Report
-
 Karim -
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

2 replies

Answer 1 / 2

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
3
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Report

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

hi watup watup,

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?
Report

Sub Update_Month()
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
Answer 2 / 2

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
Ask a question

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!