# Posts by hkaur08

• ## Count cells based on different criteria?

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.

Code
``````=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")``````
• ## Copying entire row from one excel sheet to another

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/

• ## Copying entire row from one excel sheet to another

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/

• ## Copying entire row from one excel sheet to another

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/

• ## Copying entire row from one excel sheet to another

Re: Copying entire row from one excel sheet to another

Now its giving the "Subscription out of range" error

• ## Copying entire row from one excel sheet to another

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?

• ## 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