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
Copying entire row from one excel sheet to another
-
hkaur08 -
January 11, 2017 at 11:39 PM -
Thread is marked as Resolved.
-
-
-
Re: Copying entire row from one excel sheet to another
Hard to say without seeing your data... but this worked for me:
CodePublic 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
-
Re: Copying entire row from one excel sheet to another
Quote from Ger Plante;783637Hard to say without seeing your data... but this worked for me:
CodePublic 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:
CodePublic 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 -
Re: Copying entire row from one excel sheet to another
Quote from Ger Plante;783667Cant 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:
CodePublic 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
GerNow 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 -
Re: Copying entire row from one excel sheet to another
Quote from Ger Plante;783686Can 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,
GerThis 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.
CodePublic 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
-
Re: Copying entire row from one excel sheet to another
Quotenot 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.
CodePublic 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 -
Re: Copying entire row from one excel sheet to another
Quote from Ger Plante;783712Explain 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.
CodePublic 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
RegardsIt 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.
-
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:
CodePublic 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
-
-
Re: Copying entire row from one excel sheet to another
Quote from Ger Plante;783840The 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:
CodePublic 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
Participate now!
Don’t have an account yet? Register yourself now and be a part of our community!