What is wrong with this code

[MAMIBUSSOL]

for info:

I have a selection of transactions which I applied a filter. using the filter below I am selecting all transaction which have the code "REPAIR", I am then selecting just these records and transferring them to a new sheet called TRIAL. I need to run the process 3 times using firstly sheet CRED1, then CRED2 and finally CRED3, the data needs to be started in TRIAL in cell A3, transfer the data, on the next run it needs to put the data in the first blank row. which is why I am attempting to use the rows.count

    Range("A" & .Rows.Count).End(xlUp).Offset (1)

i hope someone can help me as this has me stumped

Sub main()
    Sheets.Add(AFTER:=Sheets(11)).Name = "TRIAL"
    ActiveWorkbook.Worksheets("CRED1").Select
    Call COPYPHASE
    ActiveWorkbook.Worksheets("CRED2").Select
    Call COPYPHASE
    ActiveWorkbook.Worksheets("CRED3").Select
    Call COPYPHASE
End Sub
Sub COPYPHASE()
    ActiveSheet.Range("$B$1:$IE$388").AutoFilter FIELD:=3, Criteria1:="=REPAIRS"
    ActiveSheet.Range("$A$3:$IE$388").Copy
    Sheets("TRIAL").Select
    Range("A" & .Rows.Count).End(xlUp).Offset (1)
    ActiveSheet.Paste
End Sub

 

[dave3009]

Try like this

Sub main()
    Dim i As Long
    Sheets.Add(AFTER:=Sheets(11)).Name = "TRIAL"
    For i = 1 To 3
        With ActiveWorkbook.Worksheets("CRED" & i)
            .Range("$B$1:$IE$388").AutoFilter FIELD:=3, Criteria1:="=REPAIRS"
            .Range("$A$3:$IE$388").Copy Sheets("TRIAL").Range("A" & .Rows.Count).End(xlUp).Offset(1)
        End With
    Next i
End Sub

 

[Sandeep Warrier]

Dave,

Shouldn't this be

.Range("$A$3:$IE$388").Copy Sheets("TRIAL").Range("A" & Sheets("TRIAL").Rows.Count).End(xlUp).Offset(1)

 

[dave3009]

Thanks for the correction Sandeep.



Dave

 

[Norie]

Isn't Rows.Count the same for each worksheet in the same workbook?

 

[Sandeep Warrier]

It is...but I've come across many errors on not qualifying it while using With/End With.... ... better to be safe

 

[dave3009]

The code could be written as

Sub main()
    Dim i As Long
    Sheets.Add(AFTER:=Sheets(11)).Name = "TRIAL"
    For i = 1 To 3
        With ActiveWorkbook.Worksheets("CRED" & i)
            .Range("$B$1:$IE$388").AutoFilter FIELD:=3, Criteria1:="=REPAIRS"
            .Range("$A$3:$IE$388").Copy Sheets("TRIAL").Range("A" & Rows.Count).End(xlUp).Offset(1)
        End With
    Next i
End Sub

Note the removal of the period.

 

[Norie]

Sandeep

It is qualified, to the worksheet ActiveWorkbook.Worksheets("CRED" & I) via the With.

You are right thought, better to be safe.

 

[Sandeep Warrier]

It is qualified, to the worksheet ActiveWorkbook.Worksheets("CRED" & I) via the With.

Yeah... I know....

I've still seen errors arise in such cases... (havent been able to explain why)... which disappear on using, for example, sheets("xyz").rows.count... etc.

 

[Norie]

Strange.

The only problems I have had are when I've been automating Excel from another application.

That's just me being sloppy though.