Solved

Error: Referencing my active workbook's worksheet name

Posted on 2013-01-13
31
396 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
 
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 45

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 45

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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now