Copying entire row from one excel sheet to another

  • I need to copy the whole row from sheet1 to sheet 2 if Col f:f has any numbers ( number value will be different each time) but the second col K:K will say the same (invoice).
    Example:
    if f12 has $400 and K12 is Invoice than copy the whole row to sheet2
    if f43 has $12,000 and K43 is Invoice than copy the whole row to sheet2

  • Re: Copying entire row from one excel sheet to another


    Hard to say without seeing your data... but this worked for me:

    Code
    Public Sub test()
        Dim r As Range
        Set r = Worksheets(1).Range("A1").CurrentRegion
        r.AutoFilter Field:=11, Criteria1:="Invoice"     'Field 11 = column K
        r.Columns(6).EntireColumn.SpecialCells(xlCellTypeConstants, 1).EntireRow.Copy Destination:=Worksheets(2).Cells(Rows.Count, 1).End(xlUp)
        '6 = Column F
    End Sub

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Copying entire row from one excel sheet to another


    Quote from Ger Plante;783637

    Hard to say without seeing your data... but this worked for me:

    Code
    Public Sub test()
        Dim r As Range
        Set r = Worksheets(1).Range("A1").CurrentRegion
        r.AutoFilter Field:=11, Criteria1:="Invoice"     'Field 11 = column K
        r.Columns(6).EntireColumn.SpecialCells(xlCellTypeConstants, 1).EntireRow.Copy Destination:=Worksheets(2).Cells(Rows.Count, 1).End(xlUp)
        '6 = Column F
    End Sub


    THis is what my data is formatted as :


    [ATTACH=CONFIG]71087[/ATTACH]


    Also when i run the code it give the error "Autofilter model class range failed" and how do i know what worksheet its going to?

  • Re: Copying entire row from one excel sheet to another


    Cant really see the attachment all that well, but it looks like your first cell of data (your row header) in Cell B4 (I assumed it started in A1, which is why the code failed)


    If so, then try this:


    Code
    Public Sub test() 
        Dim r As Range 
        Set r = Worksheets(1).Range("B4").CurrentRegion 
        r.AutoFilter Field:=10, Criteria1:="Invoice" 'Field 10 = column K because we are starting in Cell B4
        r.Columns(5).EntireColumn.SpecialCells(xlCellTypeConstants, 1).EntireRow.Copy Destination:=Worksheets(2).Cells(Rows.Count, 1).End(xlUp) 
         '5 = Column F because we are starting in column B
    End Sub



    You can specify the worksheet names as Worksheets("Sheet1") and Worksheets("Sheet2"). I just used the worksheet index numbers (1) and (2), but you can replace them with actual sheet names to be more specific about where your data is coming from and going to.


    Regards
    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Copying entire row from one excel sheet to another




    Now its giving the "Subscription out of range" error

  • Re: Copying entire row from one excel sheet to another


    Can you show me the code you are using now? Copy/paste it here...


    And what is the name of the worksheets (the names on the tabs in Excel).


    Thanks,
    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Copying entire row from one excel sheet to another


    Quote from Ger Plante;783686

    Can you show me the code you are using now? Copy/paste it here...


    And what is the name of the worksheets (the names on the tabs in Excel).


    Thanks,
    Ger


    This is the code im using and I got it to work how ever its hiding rows and not copying the same way. I am attaching the excel file.

    Code
    Public Sub test()    Dim r As Range
        Set r = Worksheets("INVOICE & RECHARGE").Range("B4").CurrentRegion
        r.AutoFilter Field:=10, Criteria1:="Invoice" 'Field 10 = column K because we are starting in Cell B4
        r.Columns(5).EntireColumn.SpecialCells(xlCellTypeConstants, 1).EntireRow.Copy Destination:=Worksheets("ACC").Cells(Rows.Count, 1).End(xlUp)
         '5 = Column F because we are starting in column B
    End Sub


    forum.ozgrid.com/index.php?attachment/71093/

  • Re: Copying entire row from one excel sheet to another


    Quote

    not copying the same way


    Explain why / how please... it seems to be working perfect when I run the macro. You DO have a formula on Row 10 which just says =3620.34. If you remove the equals sign, it will copy that row too.


    The unhide the rows afterwards use this code.


    Code
    Public Sub test()
        Dim r As Range
        Set r = Worksheets("INVOICE & RECHARGE").Range("B4").CurrentRegion
        r.AutoFilter Field:=10, Criteria1:="Invoice" 'Field 10 = column K because we are starting in Cell B4
        r.Columns(5).EntireColumn.SpecialCells(xlCellTypeConstants, 1).EntireRow.Copy Destination:=Worksheets("ACC").Cells(Rows.Count, 1).End(xlUp)
         '5 = Column F because we are starting in column B
        r.AutoFilter
    End Sub


    Regards
    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Copying entire row from one excel sheet to another


    Quote from Ger Plante;783712

    Explain why / how please... it seems to be working perfect when I run the macro. You DO have a formula on Row 10 which just says =3620.34. If you remove the equals sign, it will copy that row too.


    The unhide the rows afterwards use this code.


    Code
    Public Sub test()
        Dim r As Range
        Set r = Worksheets("INVOICE & RECHARGE").Range("B4").CurrentRegion
        r.AutoFilter Field:=10, Criteria1:="Invoice" 'Field 10 = column K because we are starting in Cell B4
        r.Columns(5).EntireColumn.SpecialCells(xlCellTypeConstants, 1).EntireRow.Copy Destination:=Worksheets("ACC").Cells(Rows.Count, 1).End(xlUp)
         '5 = Column F because we are starting in column B
        r.AutoFilter
    End Sub


    Regards


    It works fine in the first file however when I try to do in the original file it still gives an error I am attaching the file. I keep getting the Autofilter error.


    forum.ozgrid.com/index.php?attachment/71110/

  • Re: Copying entire row from one excel sheet to another


    The quick fix is to just delete the value in Cell H3, or just put it in as a Comment instead of a value in the cell.


    Or, the code fix is:

    Code
    Public Sub test()
        Dim r As Range
        Set r = Worksheets("INVOICE & RECHARGE").Range("B4:N" & Worksheets("INVOICE & RECHARGE").Range("B" & Rows.Count).End(xlUp).Row)
        r.AutoFilter Field:=10, Criteria1:="Invoice" 'Field 10 = column K because we are starting in Cell B4
        r.Columns(5).SpecialCells(xlCellTypeConstants, 1).EntireRow.Copy Destination:=Worksheets("ACC").Cells(Rows.Count, 1).End(xlUp)
         '5 = Column F because we are starting in column B
        r.AutoFilter
    End Sub

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

  • Re: Copying entire row from one excel sheet to another


    Quote from Ger Plante;783840

    The quick fix is to just delete the value in Cell H3, or just put it in as a Comment instead of a value in the cell.


    Or, the code fix is:

    Code
    Public Sub test()
        Dim r As Range
        Set r = Worksheets("INVOICE & RECHARGE").Range("B4:N" & Worksheets("INVOICE & RECHARGE").Range("B" & Rows.Count).End(xlUp).Row)
        r.AutoFilter Field:=10, Criteria1:="Invoice" 'Field 10 = column K because we are starting in Cell B4
        r.Columns(5).SpecialCells(xlCellTypeConstants, 1).EntireRow.Copy Destination:=Worksheets("ACC").Cells(Rows.Count, 1).End(xlUp)
         '5 = Column F because we are starting in column B
        r.AutoFilter
    End Sub


    Thank you so much for your help and hopefully this will be the last time so I created another workbook that has more sheets but now the code is giving the subscript out of range error again.
    forum.ozgrid.com/index.php?attachment/71113/

  • Re: Copying entire row from one excel sheet to another


    This is actually the same error you reported in post number 5 above and which you fixed yourself in post number 7 :) HOwever, to be fair it was a little sneakier.


    Your worksheet name (i,e, in the tab in Excel) is called
    " INVOICE & RECHARGE"
    it should be called (I guess)
    "INVOICE & RECHARGE"


    (you accidentally added a space to the worksheet name)


    Ger

    _______________________________________________
    There are 10 types of people in the world. Those that understand Binary and those that dont. :P


    Why are Halloween and Christmas the same? Because Oct 31 = Dec 25... ;)

    _______________________________________________

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!