Code that works on Workbook but not another

[Luke777]

Hi all,

I'm using the following code

Sub EmpNumFind()

'Finds unique numbers and pastes them to Sheet1
Sheet1.Range("A:A").AdvancedFilter xlFilterCopy, , Sheet2.Range("A1:A1"), True
Sheet2.Range("A:A").ClearFormats
Sheet2.Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
'Copy and Transpose
Sheet2.Range("A2", Range("A2").End(xlDown)).Copy
Sheet2.Range("A1").PasteSpecial Transpose:=True
Sheet2.Range("A2", Range("A2").End(xlDown)).Clear
End With

End Sub

It basically finds unique numbers in one column, and then transposes them to another sheet. This part works exactly as I want, though I appreciate there may be better ways of doing it.

I'm having trouble implementing this code on a different workbook. For context, this code is running on a daily report - I need the code to be contained within a different workbook that accesses the daily report and runs this code on it.

I've tried the usual Workbooks.Open("FilePath") and .Activate. I even tried insterting With and End With which I recently learned about thanks to this forum (though I may have used it incorrectly).

Anything I'm obviously doing wrong here?

 

[Luke777]

Hi all,

I'm using the following code

Sub EmpNumFind()

'Finds unique numbers and pastes them to Sheet1
Sheet1.Range("A:A").AdvancedFilter xlFilterCopy, , Sheet2.Range("A1:A1"), True
Sheet2.Range("A:A").ClearFormats
Sheet2.Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
'Copy and Transpose
Sheet2.Range("A2", Range("A2").End(xlDown)).Copy
Sheet2.Range("A1").PasteSpecial Transpose:=True
Sheet2.Range("A2", Range("A2").End(xlDown)).Clear
End With

End Sub

It basically finds unique numbers in one column, and then transposes them to another sheet. This part works exactly as I want, though I appreciate there may be better ways of doing it.

I'm having trouble implementing this code on a different workbook. For context, this code is running on a daily report - I need the code to be contained within a different workbook that accesses the daily report and runs this code on it.

I've tried the usual Workbooks.Open("FilePath") and .Activate. I even tried insterting With and End With which I recently learned about thanks to this forum (though I may have used it incorrectly).

Anything I'm obviously doing wrong here?

"Run-time error 1004 - The extract range has a missing or invalid field name" is the error I get

 

[Alex Blakenburg]

Try this:
You have the property "range" inside the statements without the sheet reference.
I don't know what your xlCellTypeBlanks is trying to achieve, there don't ever seem to be any blanks and it is failing so I commented it out.
You are using the CodeNames "Sheet1." & "Sheet2.", you can only use Codename when referencing sheets in the "same" workbook that the VBA code is contained in and you are trying to run against a different workbook.

Sub EmpNumFind_OP_v02()

    Dim wb As Workbook
    Dim srcSht As Worksheet, outSht As Worksheet
    
    Set wb = ActiveWorkbook
    Set srcSht = wb.Worksheets("Sheet1")
    Set outSht = wb.Worksheets("Sheet2")
    
    With outSht
        'Finds unique numbers and pastes them to Sheet1
        srcSht.Range("A:A").AdvancedFilter xlFilterCopy, , .Range("A1:A1"), True
        .Range("A:A").ClearFormats
        ' XXX What is the next line supposed to do - it erroring out
        '.Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        'Copy and Transpose
        .Range("A2", .Range("A2").End(xlDown)).Copy
        .Range("A1").PasteSpecial Transpose:=True
        .Range("A2", .Range("A2").End(xlDown)).Clear
    End With
    
End Sub

 

[Luke777]

Hi all,

I'm using the following code

Sub EmpNumFind()

'Finds unique numbers and pastes them to Sheet1
Sheet1.Range("A:A").AdvancedFilter xlFilterCopy, , Sheet2.Range("A1:A1"), True
Sheet2.Range("A:A").ClearFormats
Sheet2.Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
'Copy and Transpose
Sheet2.Range("A2", Range("A2").End(xlDown)).Copy
Sheet2.Range("A1").PasteSpecial Transpose:=True
Sheet2.Range("A2", Range("A2").End(xlDown)).Clear
End With

End Sub

It basically finds unique numbers in one column, and then transposes them to another sheet. This part works exactly as I want, though I appreciate there may be better ways of doing it.

I'm having trouble implementing this code on a different workbook. For context, this code is running on a daily report - I need the code to be contained within a different workbook that accesses the daily report and runs this code on it.

I've tried the usual Workbooks.Open("FilePath") and .Activate. I even tried insterting With and End With which I recently learned about thanks to this forum (though I may have used it incorrectly).

Anything I'm obviously doing wrong here?

Try this:
You have the property "range" inside the statements without the sheet reference.
I don't know what your xlCellTypeBlanks is trying to achieve, there don't ever seem to be any blanks and it is failing so I commented it out.
You are using the CodeNames "Sheet1." & "Sheet2.", you can only use Codename when referencing sheets in the "same" workbook that the VBA code is contained in and you are trying to run against a different workbook.

Sub EmpNumFind_OP_v02()

    Dim wb As Workbook
    Dim srcSht As Worksheet, outSht As Worksheet
   
    Set wb = ActiveWorkbook
    Set srcSht = wb.Worksheets("Sheet1")
    Set outSht = wb.Worksheets("Sheet2")
   
    With outSht
        'Finds unique numbers and pastes them to Sheet1
        srcSht.Range("A:A").AdvancedFilter xlFilterCopy, , .Range("A1:A1"), True
        .Range("A:A").ClearFormats
        ' XXX What is the next line supposed to do - it erroring out
        '.Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
        'Copy and Transpose
        .Range("A2", .Range("A2").End(xlDown)).Copy
        .Range("A1").PasteSpecial Transpose:=True
        .Range("A2", .Range("A2").End(xlDown)).Clear
    End With
   
End Sub

Thanks!

I figured this one out as you posted last night (for me) :D

To answer your question though, the data in A sometimes contains a blank row which needs removing The data set I've been using for testing does contain a blank, so if there is no blank it breaks am i to understand?

Thanks

 

[Alex Blakenburg]

It seems that way. I didn't have any blanks and it kept erroring out.
A quick and dirty would be to put an On Error Resume Next before it and reset it with an On Error Goto 0 after it.

 

[Luke777]

It seems that way. I didn't have any blanks and it kept erroring out.
A quick and dirty would be to put an On Error Resume Next before it and reset it with an On Error Goto 0 after it.

I shall give this a go

Have you any idea how I’d handle not knowing the Sheet1 name? If it was variable for whatever reason?

 

[Alex Blakenburg]

You would still need some way of picking which sheet eg.

• ActiveSheet
• Worksheets(1) - 1st worksheet starting from the left
• The name of the worksheet
• Part of the name of the worksheet - Loop through sheets to find it
• Something in a specific cell of the worksheet - Loop through sheets to find it
• Codename but if it is in a different workbook you would need to loop through the worksheets to find it.

 

[Luke777]

T

You would still need some way of picking which sheet eg.

• ActiveSheet
• Worksheets(1) - 1st worksheet starting from the left
• The name of the worksheet
• Part of the name of the worksheet - Loop through sheets to find it
• Something in a specific cell of the worksheet - Loop through sheets to find it
• Codename but if it is in a different workbook you would need to loop through the worksheets to find it.

Thanks for all your help!

 

[Alex Blakenburg]

No problem. Glad to be able to help. Come back to me with some specifics if you are stilling have issues with this.