I need help with creating a macro that runs when a user enters a value in the 'Numbers' column, copies and pastes data in the corresponding worksheet 'Worksheet' column by the value of 'Numbers' column data. An excel file is attached. Any help much appreciated.
Automatically Copy & Append Range To Another Sheet Based On Number Entered
-
-
-
Re: macro to runs when a user enters a value in the 'Numbers' column
I have looked at your file, you need to do a little more design work and explanation, if a user typed a number in the numbers column of the summary sheet lets say 3 opposite sheet2 what would that 3 mean to the macro? would it mean only copy the first 3 rows? would it mean find all number 3's...and if it was to copy multiple rows or a block of data how should that be shown on the summary sheet as you have sheet2 and directly underneath sheet3?
-
Re: macro to runs when a user enters a value in the 'Numbers' column
Thanks for the prompt reply Simon. If a user typed in a 3, I want the macro to go to Sheet2 and paste the data from A1:A4, three times, below A4.
I've edited the spreadsheet with results (in red) of the macro if it was made.
-
Re: macro to runs when a user enters a value in the 'Numbers' column
This should do what you want, place it in the Summary worksheet module (Alt+F11, the double click the summary sheet on the left hand side).
CodePrivate Sub Worksheet_Change(ByVal Target As Range) Dim Rng As Range Dim i As Integer If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub If Not Intersect(Target, Range("A2,A3")) Is Nothing Then Exit Sub Set Rng = Sheets(Target.Offset(0, -1).Value).Range("A1:" & Range("A65536").End(xlUp).Address) For i = 0 To Target.Value Rng.Copy Destination:=Sheets(Target.Offset(0, -1).Value).Range("A65536").End(xlUp).Offset(1, 0) Next i End Sub
Edited the code to correct sheet after reading your update!
-
Re: Automatically Copy & Append Range To Another Sheet Based On Number Entered
Thanks Simon, looks great. Do you know how to make it so I can have an empty row after each pasted rows?
-
-
Re: Automatically Copy & Append Range To Another Sheet Based On Number Entered
Corrected Simons code and added row between copied data
Copy count correctedDestination sheetcopy range corrected. Must reference the sheet for both range A1 and Range A65536.
Changed the intersect statement.
Code
Display MoreOption Explicit Private Sub Worksheet_Change(ByVal Target As Range) Dim Rng As Range Dim i As Integer If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub If Intersect(Target, Range("B2,B3")) Is Nothing Then Exit Sub With Sheets(Target.Offset(0, -1).Value) Set Rng = .Range("A1:" & .Range("A65536").End(xlUp).Address) For i = 1 To Target.Value Rng.Copy Destination:=.Range("A65536").End(xlUp).Offset(2, 0) Next i End With End Sub
-
Re: Automatically Copy & Append Range To Another Sheet Based On Number Entered
Again, Simon and Bill. I hate to be a pain, however, when trying to adapt this code to my worksheet it isn't working. Also, I want to make a modification.
I need it to work so that in the new excel file, it goes to the corresponding tab (Green tabs) and pastes the data, including all rows and columns with values, formatting, colors, etc. Last time we only pasted the columns, can we extend this to include the all data in the green tabs.
I've posted the file again, let me know if you have any questions. Thanks again.
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!