Solved

Error: Referencing my active workbook's worksheet name

Posted on 2013-01-13
31
404 Views
Last Modified: 2013-01-13
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
Comment
Question by:bvanscoy678
  • 11
  • 11
  • 5
  • +2
31 Comments
 
LVL 43

Assisted Solution

by:Saqib Husain, Syed
Saqib Husain, Syed earned 100 total points
ID: 38772189
Check the spelling of "imported". Even leading or trailing spaces do matter
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38772190
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
 
LVL 29

Expert Comment

by:gowflow
ID: 38772191
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

 
LVL 26

Expert Comment

by:redmondb
ID: 38772197
Apologies...
Later, you again set targetWorkbook to the active workbook - which, of course, is now the file just opened.
0
 
LVL 29

Assisted Solution

by:gowflow
gowflow earned 100 total points
ID: 38772199
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
 
LVL 46

Assisted Solution

by:Martin Liss
Martin Liss earned 100 total points
ID: 38772200
Do you have a sheet that spelled exactly "imported"? Case sensitive.
0
 

Author Comment

by:bvanscoy678
ID: 38772210
Checking spelling now
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38772213
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
 
LVL 43

Expert Comment

by:Saqib Husain, Syed
ID: 38772214
I don't think that the case matters here.
0
 
LVL 26

Expert Comment

by:redmondb
ID: 38772218
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
 
LVL 46

Expert Comment

by:Martin Liss
ID: 38772220
To be honest, I just made a guess.
0
 
LVL 29

Expert Comment

by:gowflow
ID: 38772221
me2 but the error definitively comes in the line before as poiinted out twice already
gowflow
0
 

Author Comment

by:bvanscoy678
ID: 38772225
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
 
LVL 26

Expert Comment

by:redmondb
ID: 38772227
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
 
LVL 26

Expert Comment

by:redmondb
ID: 38772229
Thanks, Brent.

Drop your second set.

Regards,
Brian.
0
 

Author Comment

by:bvanscoy678
ID: 38772235
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
 

Author Comment

by:bvanscoy678
ID: 38772245
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
 
LVL 26

Expert Comment

by:redmondb
ID: 38772246
Brent,

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

Regards,
Brian.
0
 

Author Comment

by:bvanscoy678
ID: 38772249
Brian,

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

Expert Comment

by:redmondb
ID: 38772257
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
 

Author Comment

by:bvanscoy678
ID: 38772261
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
 
LVL 26

Expert Comment

by:redmondb
ID: 38772268
Brent,

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

Regards,
Brian.
0
 

Author Comment

by:bvanscoy678
ID: 38772269
Just ran it again and it fixed it, but now this error:

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

Accepted Solution

by:
redmondb earned 200 total points
ID: 38772272
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
 

Author Comment

by:bvanscoy678
ID: 38772275
working now, let me look again. Thanks
0
 

Author Comment

by:bvanscoy678
ID: 38772287
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
 
LVL 26

Expert Comment

by:redmondb
ID: 38772295
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
 

Author Closing Comment

by:bvanscoy678
ID: 38772321
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
 
LVL 29

Expert Comment

by:gowflow
ID: 38772344
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
 
LVL 26

Expert Comment

by:redmondb
ID: 38772348
Thanks, Brent!
0
 

Author Comment

by:bvanscoy678
ID: 38772414
Good advice and I'll take it!
0

Featured Post

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Drop Down List with Unique/Distinct Values (Part II - ComboBox or ListBox and Data Validation List Bonus!) David Miller (dlmille) Intro This article focuses on delivering unique, sorted lists to list objects (e.g., ComboBox, ListBox) and Dat…
Introduction This Article is a follow-up to my Mappit! Addin Article (http://www.experts-exchange.com/A_2613.html), it was inspired by an email posting I made to EUSPRIG (http://www.eusprig.org/index.htm), I will briefly cover: 1) An overvie…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question