VBA to Open Existing Workbook and Copy Tab into Current WB based on variable cells

carpjm1

New Member
Joined
Sep 12, 2014
Messages
5
Hi All. Thank you in advance for any help provided. I have workbook A and I have code to open an exisitng workbook based based on a cell value. I want a specific tab (based on a cell value) to be copied from the newly opened wb and pasted into workbook A. Here is my code.
Code:
Sub OpenBCP()    
    Dim ExtFile As String
    Dim ExtBk As Workbook
    Dim varTabvalue As String
    
    'define variable values cell locations
    varTabvalue = Lkbx.Range("c10").Value
    ExtFile = Lkbx.Range("b6").Value
    
    'open variable filename in cell, if wrong, select file
    If Not ExtFile = "" And Dir(ExtFile) <> "" Then
    Else
        ExtFile = Application.GetOpenFilename(FileFilter:="microsoft excel files (*.xlsx), *.xlsx", Title:="Please Select BCP file")
    End If
    On Error Resume Next
    Set ExtBk = Workbooks(Dir(ExtFile))
    On Error GoTo 0
    If ExtBk Is Nothing Then
        Application.Workbooks.Open ExtFile
        Set ExtBk = Workbooks(Dir(ExtFile))
    End If
    
[COLOR=#ff0000]   Sheets(varTabvalue).Activate
[/COLOR]   ActiveSheet.UsedRange.Copy
End Sub

If I change the tab reference to Sheets("0908").Activate. It works fine. Why does this not accept the varTabvalue? Thanks again.
 
Last edited:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
I suppose that the variable doesn't have the write value... Why dont you try with Debud -> Add watch... to see how that variable have been initialized...
 
Upvote 0
Thank you spoon. Watch say expression not defined in context. How do I properly set this variable?
 
Upvote 0
Sorry for my late reply...
Rich (BB code):
varTabvalue = Lkbx.Range("c10").Value


The variable has to contain the data in Range C10. What is it in that cell? Is the sheet's name equal to the value in that cell?

Also, every sheet can be reached with his name or index. No meter what the name is, the index always is number which represent the position of the sheet in the book - Sheet1, Sheet2, Sheet3 etc... leyaut from left to right... So if you want so do something in sheet1 and you don't know his name, try Sheet1.Activate - not Sheets("name of the sheet").Activate... of course, if you know the position of the sheet... to many ifs...
 
Upvote 0

Forum statistics

Threads
1,226,588
Messages
6,191,889
Members
453,684
Latest member
Gretchenhines

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top