How do you copy data from one sheet to another using a button

[Niffer]

Hi I am trying to create a macro that will copy rows from a list o
sheet 1 to sheet 2 based on a specific value (Column M = "Y"). I have
button that moves all Y values to the top of the list. I have code tha
will work with literal ranges. However there may be 1 or 10 rows tha
need to be copied and the next open row on Sheet 2 will be alway
changing.

Worksheets("Sheet 1").Range("A1920").Copy _
Destination:=Worksheets("Sheet 2").Range("A1011")

I have tried to use calculated variables that will ensure the prope
number of rows will be copied and be copied into the next blank spac
but it won't compile and since this is my first attempt at a macro
cannot figure out how to fix this.

Worksheets("Sheet 1").Range(Cells(19, 1), Cells(test, 4)).Copy _
'Destination:=Worksheets("Sheet 2").Range(Cells(test2, 1), Cells(test3
4))
I am assuming there is a much easier way to do this but I do not have
clue!
Any help would be greatly appreciated : - )
Thanks for your time

 

[Nigel]

Hi Niffer
Try the following, it uses an autofilter to subset your records, determine
how many records are displayed (where column M) is set to Y, then copies
them into the next available row on sheet 2.
A few assumptions I made:
1. Your data is on sheet1 starts in row1 and uses columns A to M
2. You wish to copy all columns after filtering to required rows
3. I use column M to determine the number of selected rows on sheet1 and
sheet2

Sub SelectiveCopy()
Dim LastRow1 As Long, LastRow2 As Long
Sheets("Sheet1").Activate
Range("A1:M1").AutoFilter
Range("A1:M1").AutoFilter Field:=13, Criteria1:="Y"
LastRow1 = Worksheets(1).Cells(Rows.Count, "M").End(xlUp).Row
LastRow2 = Worksheets(2).Cells(Rows.Count, "M").End(xlUp).Row
Range("A1:M" & LastRow1).Copy Destination:=Sheets(2).Range("A" &
LastRow2)
Range("A1:M1").AutoFilter
End Sub

Cheers
Nigel

 

[Bob Phillips]

There doesn't seem to be anything wrong with that approach. I don't know
what all of the variables refer to/from, but here is a way to calculate the
size of the range and copy it

For i = 1 To Cells(Rows.Count,"M").End(xlUp).Row
If Cells(i,"M").Value <> "M" Then
Exit For
End If
Next i

Worksheets("Sheet 1").Range("A!" & i-1).Copy _
Destination:=Worksheets("Sheet 2").Range("A1")

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)