Problem Declaring sheets

[Zabman]

Hi,

This is less of an error, and more of an "I cant workout how to do this" I have spent the past 45 minutes on google and just can't seem to get it right.

I have to refer to two workbooks & copy data in between them, and instead of naming the worksheet explicitely everytime, I want to name each worksheet as a variable down from the workbook error. I have tried many different thins & declarations but just can't seem to get it right. This is how it looks presently, and it is not working.

Sub ImportTNTInfo()
Dim lRow As Long
Dim TNTWb As Workbook
Dim BillingWb As Workbook
Dim WS As Worksheet
Dim TNTSheet As String
    TNTSheet = "C:\TNT\File.csv"
    Workbooks.Open (TNTSheet)
    TNTWb = Workbook("File.csv")
    BillingWb = Workbook("AU-EI Billing Sheet")
    WS = Worksheets(1)
    
    lRow = TNTWb.WS.Range("A1").End(xlDown).Row
    '** Import Shiprefs **'
    TNTWb.WS.Range("I2:I" & lRow).Copy BillingWb.WS.Range("E10")
    '** Import Address **'
    TNTWb.WS.Range("M2:M" & lRow).Copy BillingWb.WS.Range("F10")
    '** Import Suburb **'
    TNTWb.WS.Range("N2:N" & lRow).Copy BillingWb.WS.Range("G10")
    '** Import Postal Code **'
    TNTWb.WS.Range("O2:O" & lRow).Copy BillingWb.WS.Range("H10")
    '** Import State **'
    TNTWb.WS.Range("P2:P" & lRow).Copy BillingWb.WS.Range("I10")
    '** Import Customer Name **'
    TNTWb.WS.Range("M2:M" & lRow).Copy BillingWb.WS.Range("K10")
End Sub

 

[SydneyGeek]

When you assign an object to a variable you must use Set. So...

    Set TNTWb = Workbooks("File.csv")
    Set BillingWb = Workbooks("AU-EI Billing Sheet")
    Set WS = Worksheets(1)

Note:
1. When you assign a workbook to a variable use Workbooks, because you are working with the collection of workbooks.
2. unless those workbooks are in the same directory you must use the full path
3. It's a good idea to use the file extension (.xls, .xlsx or whatever) with the workbook name.

Denis

 

[Zabman]

Hi,

Thank you for your help. I have changed my declarations but am still getting an error at this line:

lRow = TNTWb.WS.Range("A1").End(xlDown).Row

The error is Compile error, method or data member not found.

Dim lRow As Long
Dim TNTWb As Workbooks
Dim BillingWb As Workbooks
Dim WS As Worksheet
    
    Set TNTWb = Workbooks("C:\TNT\File.csv")
    Set BillingWb = Workbooks("C:\TNT\AUStag.xls")
    Set WS = Worksheets(1)
    Workbooks(TNTWb).Open
    
    lRow = TNTWb.WS.Range("A1").End(xlDown).Row
    '** Import Shiprefs **'
    TNTWb.WS.Range("I2:I" & lRow).Copy BillingWb.WS.Range("E10")
    '** Import Address **'
    TNTWb.WS.Range("M2:M" & lRow).Copy BillingWb.WS.Range("F10")
    '** Import Suburb **'
    TNTWb.WS.Range("N2:N" & lRow).Copy BillingWb.WS.Range("G10")
    '** Import Postal Code **'
    TNTWb.WS.Range("O2:O" & lRow).Copy BillingWb.WS.Range("H10")
    '** Import State **'
    TNTWb.WS.Range("P2:P" & lRow).Copy BillingWb.WS.Range("I10")
    '** Import Customer Name **'
    TNTWb.WS.Range("M2:M" & lRow).Copy BillingWb.WS.Range("K10")
End Sub

 

[SydneyGeek]

Once you have opened the workbook you don't need to qualify the reference at this point.
Instead of...
lRow = TNTWb.WS.Range("A1").End(xlDown).Row

Try...
lRow = Range("A1").End(xlDown).Row

Although, be careful. If there are blanks in Column A you won't get to the last row using this code. Instead, go to the bottom of the sheet and ride up...
lRow = Range("A" & Rows.Count).End(xlUp).Row


Denis

 

[Zabman]

Hi Denis,

I appreciate your help, but I still can't get it to work, I am certain there is some very basic concept I am not getting!

I have changed the code as you suggested, and it has just moved the same error to the next line of code:
TNTWb.WS.Range("I2:I" & lRow).Copy BillingWb.WS.Range("E10")

 

[Norie]

I think you need 2 worksheet references, not one.

Also you don't declare the workbooks as Workbooks.

I'm not sure exactly what it is you are trying to do but this might be a start.

Option Explicit
 
Sub test()
Dim TNTWb As Workbook
Dim BillingWb As Workbook
Dim WSFrom As Worksheet
Dim WSTo As Worksheet
Dim lRow As Long
    
    Set TNTWb = Workbooks.Open("C:\TNT\File.csv")
    Set BillingWb = Workbooks.Open("C:\TNT\AUStag.xls")
    
    Set WSFrom = TNTWb.Worksheets(1)
    Set WSTo = BillingWb.Worksheets(1)
       
    lRow = WSFrom.Range("A" & Rows.Count).End(xlUp).Row

    '** Import Shiprefs **'
    WSFrom.Range("I2:I" & lRow).Copy WSTo.Range("E10")

    '** Import Address **'
    WSFrom.Range("M2:M" & lRow).Copy WSTo.Range("F10")

    '** Import Suburb **'
    WSFrom.Range("N2:N" & lRow).Copy WSTo.Range("G10")

    '** Import Postal Code **'
    WSFrom.Range("O2:O" & lRow).Copy WSTo.Range("H10")

    '** Import State **'
    WSFrom.Range("P2:P" & lRow).Copy WSTo.Range("I10")

    '** Import Customer Name **'
    WSFrom.Range("M2:M" & lRow).Copy WSTo.Range("K10")

End Sub

I've had to make a few assumptions with that code, for example that you are opening both workbooks.

If you aren't then the code to reference the workbooks will have to be changed.

Another thing assumption is that you are copying between the 1st worksheet of each workbook.

If that isn't what you want to do then post back with further explanation.

 

[Zabman]

Hi Norie,

That is exactly what I am trying to do! Thanks!!!!

The AUSTag.xls sheet is actually already open, so how would I declare the workbook without trying to reopen it which would cause an error?

 

[SydneyGeek]

If it's already open you could use either...

    Set BillingWb = Workbooks("C:\TNT\AUStag.xls")

Or...

    Set BillingWb = Windows("AUStag")

Denis

 

[Norie]

If the open workbook is where the code is located you can use this:

Set wb = ThisWorkbook

Change the variable name as needed.

 

[Norie]

Got cut off, surprised that last post made it through.

Anyway I meant to add, if the workbook is open you can create a reference to it like this.

Set BillingWb = Workbooks("AUStag.xls"

No need to change the variable name with that.