troubleshooting Question

Set WorkSheet not Working

Avatar of Frank Batusic
Frank Batusic asked on
Microsoft OfficeMicrosoft ExcelVBA
9 Comments2 Solutions171 ViewsLast Modified:
After spending a lot of time reviewing answers on Experts Exchange (and select other Excel VBA Forums) I am attempting to rewrite existing code to eliminate the use of Select and Activate and incorporate "Cells" rather than always needing to convert a numeric value for a column to an alpha character.

My code module is developed for the explicit purpose of rolling out a new version of code.  The program builds a list of a user's existing files and then copies all pertinent data into the new program file.  While the code technically worked as it existed, the numerous times I used either "Select" or "Activate" I now know is not efficient code and also caused significant screen refreshes.

Without getting too detailed, the data table in each user's file can have a different number of columns with different data types by column.  So, as part of the code to migrate old data to the new program file I need to determine the last column and last row (of contiguous input, not last row on the sheet).  With the following code, I am attempting to change the WorkSheet for the file I am importing.  I have used the Set command to change the value of my WorkSheet variable "wsI".  However, when the code runs to establish the last column and last row values it is giving me the values from the previous WorkSheet.  

The variable "ImportWbk" is set to the name of the file from which I am copying data.  Using Debug.Print I have verified that ImportWbk is set to the correct filename.  After setting "wsI" (defined as WorkSheet variable) to "Analysis" I have confirmed via the Debug.Print command that it is indeed now set to "Analysis".

Can someone help me to get this corrected?

    Set wsI = ImportWbk.Worksheets("Analysis")
    Debug.Print wsI.Name
    With ImportWbk
        ImportLC = Sheets(wsI).Cells(21, Columns.Count).End(xlToLeft).Column
        ImportLR = Sheets(wsI).Range("A22").End(xlDown) - 1
    End With
Join our community to see this answer!
Unlock 2 Answers and 9 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 9 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros