Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 426
  • Last Modified:

Error: Referencing my active workbook's worksheet name

Hello,

I am working through my code bit by bit and I can't get past my latest error. I know it must be something simple, because it is a simple code. I have copied data from one worksheet and pasting it into another workbook.

I get an error with this line:

With targetWorkbook.Worksheets("imported")

Thanks for any help,
Brent

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
      
      Set targetWorkbook = ActiveWorkbook
      
      '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

Open in new window

0
bvanscoy678
Asked:
bvanscoy678
  • 11
  • 11
  • 5
  • +2
4 Solutions
 
Saqib Husain, SyedEngineerCommented:
Check the spelling of "imported". Even leading or trailing spaces do matter
0
 
redmondbCommented:
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.
0
 
gowflowCommented:
I think you missed a point "."
replace this
Range("a2", .Cells(MyLastRow, MyLastColumn)).Select

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

gowflow
0
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
redmondbCommented:
Apologies...
Later, you again set targetWorkbook to the active workbook - which, of course, is now the file just opened.
0
 
gowflowCommented:
An other thing make sure that in this
With targetWorkbook.Worksheets("imported")
the sheet's name is called like this and not Imported or IMPORTED etc...gowflow
0
 
Martin LissOlder than dirtCommented:
Do you have a sheet that spelled exactly "imported"? Case sensitive.
0
 
bvanscoy678Author Commented:
Checking spelling now
0
 
gowflowCommented:
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
0
 
Saqib Husain, SyedEngineerCommented:
I don't think that the case matters here.
0
 
redmondbCommented:
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.
0
 
Martin LissOlder than dirtCommented:
To be honest, I just made a guess.
0
 
gowflowCommented:
me2 but the error definitively comes in the line before as poiinted out twice already
gowflow
0
 
bvanscoy678Author Commented:
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
0
 
redmondbCommented:
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,
0
 
redmondbCommented:
Thanks, Brent.

Drop your second set.

Regards,
Brian.
0
 
bvanscoy678Author Commented:
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
0
 
bvanscoy678Author Commented:
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

Open in new window

0
 
redmondbCommented:
Brent,

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

Regards,
Brian.
0
 
bvanscoy678Author Commented:
Brian,

I dropped it in the above code. We must have missed each other posting at the same time. Thanks.
0
 
redmondbCommented:
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.
0
 
bvanscoy678Author Commented:
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
0
 
redmondbCommented:
Brent,

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

Regards,
Brian.
0
 
bvanscoy678Author Commented:
Just ran it again and it fixed it, but now this error:

.Range("A" & MyLastRow + 1).PasteSpecial Paste:=xlPasteValues
0
 
redmondbCommented:
Brent,

This is the changed I mentioned above that's working fine for me. Edit: Sure you have data in the import file?Vacation-Log-Project-Code-Attemp.xlsmRegards,
Brian.
0
 
bvanscoy678Author Commented:
working now, let me look again. Thanks
0
 
bvanscoy678Author Commented:
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

Open in new window

Vacation-Log-Project-Code-Attemp.xlsm
0
 
redmondbCommented:
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.
0
 
bvanscoy678Author Commented:
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
0
 
gowflowCommented:
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
0
 
redmondbCommented:
Thanks, Brent!
0
 
bvanscoy678Author Commented:
Good advice and I'll take it!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 11
  • 11
  • 5
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now