Posts by hkaur08

I have an excel work sheet that has columns each column has names and numbers.

column AI has job categories (1A,1B,1C......)
Column A has the categories ( Executives , Directors,....)
Column AD has the transtaction ( New Hire, Rehire, Promotions)
Column J has the names of the leaders ( dan , steven, sally....) Workforce!A1 has the name of the person we are counting for.

In worksheet 2 I have to count how many Executives from job cat 1A,1B,1C where New hires, rehires, promotions under Dan

I am using Countifs to calculate the sum and its working fine but the formula keeps getting bigger as I have to and race in another calculation. My question is that is there an easies way to do this without having to write this long formula everytime I have to look for something specific.

=COUNTIFS('Transaction - Original '!AI:AI,"1A",'Transaction - Original '!J:J,Workforce!A1,'Transaction - Original '!A:A, "Executives",'Transaction - Original '!AD:AD,"New Hire") 
+COUNTIFS('Transaction - Original '!AI:AI,"1A",'Transaction - Original '!J:J,Workforce!A1,'Transaction - Original '!A:A, "Executives",'Transaction - Original '!AD:AD,"Rehire")
+COUNTIFS('Transaction - Original '!AI:AI,"1A",'Transaction - Original '!J:J,Workforce!A1,'Transaction - Original '!A:A, "Executives",'Transaction - Original '!AD:AD,"Promotion")
+COUNTIFS('Transaction - Original '!AI:AI,"1B",'Transaction - Original '!J:J,Workforce!A1,'Transaction - Original '!A:A, "Executives",'Transaction - Original '!AD:AD,"New Hire")
+COUNTIFS('Transaction - Original '!AI:AI,"1B",'Transaction - Original '!J:J,Workforce!A1,'Transaction - Original '!A:A, "Executives",'Transaction - Original '!AD:AD,"Rehire")
+COUNTIFS('Transaction - Original '!AI:AI,"1B",'Transaction - Original '!J:J,Workforce!A1,'Transaction - Original '!A:A, "Executives",'Transaction - Original '!AD:AD, "Promotion")
+ COUNTIFS('Transaction - Original '!AI:AI,"1C",'Transaction - Original '!J:J,Workforce!A1,'Transaction - Original '!A:A, "Executives",'Transaction - Original '!AD:AD,"New Hire")
+COUNTIFS('Transaction - Original '!AI:AI,"1C",'Transaction - Original '!J:J,Workforce!A1,'Transaction - Original '!A:A, "Executives",'Transaction - Original '!AD:AD,"Rehire")
+COUNTIFS('Transaction - Original '!AI:AI,"1C",'Transaction - Original '!J:J,Workforce!A1,'Transaction - Original '!A:A, "Executives",'Transaction - Original '!AD:AD,"Promotion")

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

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

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.

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 from Ger Plante;783667

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

Display More

Now its giving the "Subscription out of range" error

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?

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