Copy paste Excel data in

Question

I have 2 sheets, sheet1 and sheet2. From sheet1 data i have to copy data and paste in sheet2, then again from sheet1 i have to copy another different set of data and paste in sheet2 last line, where i pasted data 1st time

Sub Copy_chains_to_other_sheet()
ActiveSheet.Range("$A$1").AutoFilter Field:=8, Criteria1:="<>1", _
Operator:=xlAnd
ActiveSheet.Range("$A$1:$I$681").AutoFilter Field:=1, Criteria1:="=*antaris*" _
    , Operator:=xlAnd
Range("A1").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Select
Selection.Copy
ActiveSheet.Next.Select
ActiveSheet.Paste
ActiveSheet.Range("$A$1").AutoFilter Field:=1

End Sub

This is the macro i wrote, but i don't know how to proceed. Because one time i have 5 rows of data that time i need to copy data from sheet1 and paste in sheet2 and with next set of data i need to paste it in 6th row, but another time i have 8 row of data that time i need to paste the next set of data from 9the row onwards, so how to deal with this.

Thanks in advance for answering.

Regards, Vignesh

Looping through a given range to look for cells that meet your criteria, then setting the boundaries of the range, you can loop through that range using variables for the row number and column numbers instead of letters. I can show you once I understand what determines your logic for selecting your ranges. — peege
The range copied is not fixed. according to the filtering condition it will differ. The above code which i gave is recorded.Now the problem is, i am pasting data in another sheet, in that sheet i already pasted data from A1 to C3 for example now i need to paste next set of data in A4 column. — Vigneshwaran Kandaswamy
I am not getting what you are asking, sorry.What determines the range, i am not getting it. I cant able to add image also, i don't know how to explain my situation. — Vigneshwaran Kandaswamy
I apologize. You are saying that the range changes. How do you decide what it is in the first place? I understand that you pick up where you left off from the previous range on all subsequent copies. But when you decide in the first use, how do you come up with the range? One time is 5, another time 8. What makes that decision for you? Why 5 or 8? — peege

barryleajo barryleajo · Accepted Answer · 2014-11-27T22:17:27

If I understand correctly you want to copy the results of successive autofiltered data from Sheet1 to a continuous "list" in Sheet2. If this is so then perhaps try the following to get you going. You will need to alter the variables/names to suit your requirement, I have made some assumptions.

Option Explicit

Sub copyAFs()
Dim wsONE As Worksheet, wsTWO As Worksheet
Dim ONEstrow As Long, ONEendrow As Long, ONEstcol As Long, ONEendcol As Long
Dim TWOstrow As Long, TWOnextrow As Long, TWOstcol As Long
Dim crit1col As Long, crit2col As Long
Dim crit1 As String, crit2 As String

Set wsONE = Sheets("Sheet1")
Set wsTWO = Sheets("Sheet2")
ONEstrow = 1
ONEstcol = 1
ONEendcol = 10
TWOstrow = 1
TWOstcol = 1
crit1 = "antaris"
crit2 = "1"
crit1col = 1
crit2col = 8

    With wsTWO
        TWOnextrow = .Cells(.Rows.Count, TWOstcol).End(xlUp).Row + 1
    End With

'clear autofilter
wsONE.AutoFilterMode = False

    'apply autofilter
    With wsONE
        ONEendrow = Cells(Rows.Count, ONEstcol).End(xlUp).Row + 1
            With .Range(.Cells(ONEstrow, ONEstcol), .Cells(ONEendrow, ONEendcol))
                'set autofilter
                .AutoFilter Field:=crit1col, Criteria1:=crit1
                .AutoFilter Field:=crit2col, Criteria1:=crit2
            End With
    End With

    'copy filtered range without header
    With wsTWO
        wsONE.AutoFilter.Range.Offset(1, 0).Copy Destination:=.Range(.Cells(TWOnextrow, TWOstcol), .Cells(TWOnextrow, TWOstcol))
    End With

    'clear autofilter
    wsONE.AutoFilterMode = False

End Sub

Copy paste Excel data in

1 Answers