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.
If I change the tab reference to Sheets("0908").Activate. It works fine. Why does this not accept the varTabvalue? Thanks again.
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: