Error: Referencing my active workbook's worksheet name

Hi, Brent.

You start by setting targetWorkbook to the active workbook. You then prompt the user for a file and open that. Later, you againset targetWorkbook to the active workbook - which, of course, is now the active workbook.

So, which workbook is "imported" in?

Regards,
Brian.

I think you missed a point "."
replace this
Range("a2", .Cells(MyLastRow, MyLastColumn)).Select

by this
.Range("a2", .Cells(MyLastRow, MyLastColumn)).Select

gowflow

Apologies...
Later, you again set targetWorkbook to the active workbook - which, of course, is now the file just opened.

Checking spelling now

and don' forget the point for
.Range("a2", .Cells(MyLastRow, MyLastColumn)).Select
if you are refrencing to the workbook wb and the active sheet in the with
With wb.ActiveSheet

gowflow

I don't think that the case matters here.

gowflow, MartinLiss.

Are you sure that sheet names are case sensitive? The following works fine for me with a sheet named "Sheet1"...
Sub List_Name()
With ActiveWorkbook.Worksheets("sHEET1")
    Debug.Print .Name
End With
End Sub

Thanks,
Brian.

To be honest, I just made a guess.

me2 but the error definitively comes in the line before as poiinted out twice already
gowflow

Brian, I see what you mean, by setting it twice.

I have one workbook called "vacations" which I have 3 worksheets. Approved, Imported, Rejected. From our SQL datase I have an .xls file that is exported to my desktop. So, I am bringing in data from the exported file on my desktop and adding it my "Vacations" workbook.

My vacation workbook = TargetWorkbook.
My exported workbook  on my desktop =   Set wb = Workbooks.Open(Ret)

My did have my case incorrect. My sheet case was Imported, while my code had imported.
I fixed that, but still an error.

reading through the other suggestions now.

thanks

gowflow,

When the "With" is the activesheet then...
Range("a2", .Cells(MyLastRow, MyLastColumn)).Select
...and...
.Range("a2", .Cells(MyLastRow, MyLastColumn)).Select
...are the same!

Regards,
Brian,

Thanks, Brent.

Drop your second set.

Regards,
Brian.

Gowflow,,

I fixed the .range error. Thanks

I am attaching workbook to give better idea. This is the first part of several parts I am working on. Please ignore the other code errors, since I have not addressed them as of yet. I am still learning, so taking it step by step. I have commented the other portions as kind of a draft I of what I want to do.

Thanks
Vacation-Log-Project-Code-Attemp.xlsm

My current code

Option Explicit

Public Sub ImportFile()
    ' Get workbook...
    Dim ws As Worksheet
    Dim filter As String
    Dim targetWorkbook As Workbook, wb As Workbook
    Dim Ret As Variant
    Dim Caption As String
    Dim TargetRng As Range
    Dim MyLastRow As Integer
    Dim MyLastColumn As Integer

    Set targetWorkbook = Application.ActiveWorkbook

    ' get the customer workbook
    filter = "Excel files (*.xls),*.xls"
    Caption = "Please Select an input file "
    Ret = Application.GetOpenFilename(filter, , Caption)

    If Ret = False Then Exit Sub

    Set wb = Workbooks.Open(Ret)
    
    With wb.ActiveSheet
        'find last used row in column A
        MyLastRow = Cells(Rows.Count, "A").End(xlUp).Row
        'find last used column in row 1
        MyLastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
        .Range("a2", .Cells(MyLastRow, MyLastColumn)).Select

           
      End With
      
      
      'Note for activating target workbook:  Workbooks("Book1").Worksheets("Sheet1")
     
      With targetWorkbook.Worksheets("Imported")
            MyLastRow = .Range("A" & Rows.Count).End(xlUp).Row
           .Range("A" & MyLastRow + 1).PasteSpecial Paste:=xlPasteValues
      End With

End Sub

Select allOpen in new window

Brent,

The second "Set targetWorkbook" is still in the file!

Regards,
Brian.

Brian,

I dropped it in the above code. We must have missed each other posting at the same time. Thanks.

Brent,

Apologies for the crossing posts!

Is the error gone now?

(BTW, while the revised range is better code, as indicated earlier, it gives exactly the same result as the original version.)

Regards,
Brian.

No, I still have the error. I checking the range and workbook's name, but I don't think it matters since I set the workbook as the active workbook with the application.

Set targetWorkbook = Application.ActiveWorkbook
 

thanks
Vacation-Log-Project-Code-Attemp.xlsm

Brent,

I changed line 30 to the following and it's working fine...
.Range("a2", .Cells(MyLastRow, MyLastColumn)).Copy

Regards,
Brian.

Just ran it again and it fixed it, but now this error:

.Range("A" & MyLastRow + 1).PasteSpecial Paste:=xlPasteValues

working now, let me look again. Thanks

Yes, That fixed it!

I am not certain what the actual problem was to assign points. I know removing the set= line and changing the .select to .copy made it work, but the case sensitive and .range also where helpful.

My final code and final attached workbooks for future reference for anyone reading the post.

Thanks.

Option Explicit

Public Sub ImportFile()
    ' Get workbook...
    Dim ws As Worksheet
    Dim filter As String
    Dim targetWorkbook As Workbook, wb As Workbook
    Dim Ret As Variant
    Dim Caption As String
    Dim TargetRng As Range
    Dim MyLastRow As Integer
    Dim MyLastColumn As Integer

    Set targetWorkbook = Application.ActiveWorkbook

    ' get the customer workbook
    filter = "Excel files (*.xls),*.xls"
    Caption = "Please Select an input file "
    Ret = Application.GetOpenFilename(filter, , Caption)

    If Ret = False Then Exit Sub

    Set wb = Workbooks.Open(Ret)
    
        With wb.ActiveSheet
          'find last used row in column A
           MyLastRow = Cells(Rows.Count, "A").End(xlUp).Row
           'find last used column in row 1
           MyLastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
           .Range("a2", .Cells(MyLastRow, MyLastColumn)).Copy
        End With
      
      'Note for activating target workbook:  Workbooks("Book1").Worksheets("Sheet1")
     
        With targetWorkbook.Worksheets("Imported")
            MyLastRow = .Range("A" & Rows.Count).End(xlUp).Row
           .Range("A" & MyLastRow + 1).PasteSpecial Paste:=xlPasteValues
        End With

End Sub

Select allOpen in new window

Vacation-Log-Project-Code-Attemp.xlsm

Brent,

You had two problems...
(1) The second "Set" which meant that targetWorkbook was pointing to the wrong file.
(2) Line 30 selected the import range instead of copying it.

Points don't matter - 500/4 is fine!

Regards,
Brian.

Thanks to all 4 of you to help me solve the problem. Even though the .range and case sensitivity may have not affected this code, it is a good lesson for me to pay attention to those things.

I appreciate your time.

Thanks,
Brent

Tks for the points and as mentioned points don't matter I would add if you don't want to endup in similar situation in the future try never to refer to Activesheet Activeworkbook always from the start give meaningful names to you as far as workook or worksheets like
Impwb Impws and Destwb Desws
this way you keep following the logic and avoid weired affections.
gowflow

Thanks, Brent!

Good advice and I'll take it!