Automatically Copy & Append Range To Another Sheet Based On Number Entered

bigben2003 · Mar 14th 2008

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.

Simon Lloyd · Mar 14th 2008

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?

bigben2003 · Mar 14th 2008

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.

Simon Lloyd · Mar 14th 2008

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).

Code

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 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!

bigben2003 · Mar 16th 2008

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?

Bill Rockenbach · Mar 16th 2008

Re: Automatically Copy & Append Range To Another Sheet Based On Number Entered

Corrected Simons code and added row between copied data
Copy count corrected

Destination sheetcopy range corrected. Must reference the sheet for both range A1 and Range A65536.

Changed the intersect statement.

Code

Option 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

Display More

bigben2003 · Mar 16th 2008

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.

Automatically Copy & Append Range To Another Sheet Based On Number Entered

Participate now!

Share