Import data using code name instead of Sheet name (VBA)

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi

I have a file where I import data, but the person keeps on changing the Sheet name / front of house name.

So I'd like to use the code name / back of house name of the Sheet, when importing the data.

Does anyone know how I can use the code name / back of house name instead of the Sheet name in the code below, please?

So I want to change this: Worksheets("Sheet1").Select so that it refers to the name in the VB editor. Sometimes the front of house name is called "Sheet1" and other times it's called "Main" etc.

VBA Code:
Sub GetData()

Dim fd As FileDialog
Dim filewaschosen As Boolean
Dim ReportP As Workbook
Dim iWBP As Workbook

Set ReportP = ActiveWorkbook
Set fd = Application.FileDialog(msoFileDialogOpen)
fd.Filters.Clear
fd.Filters.Add "Custom Excel Files", "*.xlsx, *.xlsm, *.xls"
fd.AllowMultiSelect = False
fd.InitialFileName = Environ("UserProfile") & "\Main Folder"
filewaschosen = fd.Show
fd.Execute

Set iWBP = ActiveWorkbook
'Get data from  file
Worksheets("Sheet1").Select
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:AE" & lastrow).Copy
 

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney
L

Legacy 456155

Guest
Worksheets("Sheet1").Select
would become
Sheet1.Select
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks for the prompt response.

However, I'm getting a 'Run-time error 1004' that says

Method 'Select' of Object '_Worksheet' failed.

What's strange is that it has an inverted comma before an underscore then the word 'Worksheet'

Please find below the updated code (it stopped at the line "Sheet1.Select" which was highlighted yellow.

Do you know why that would have happened?

TIA

VBA Code:
Sub GetData()

Dim fd As FileDialog
Dim filewaschosen As Boolean
Dim ReportP As Workbook
Dim iWBP As Workbook



Set ReportP = ActiveWorkbook
Set fd = Application.FileDialog(msoFileDialogOpen)
fd.Filters.Clear
fd.Filters.Add "Custom Excel Files", "*.xlsx, *.xlsm, *.xls"
fd.AllowMultiSelect = False
fd.InitialFileName = Environ("UserProfile") & "\Main Folder"
filewaschosen = fd.Show
fd.Execute

Set iWBP = ActiveWorkbook
'Get data from  file
Sheet1.Select
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:AE" & lastrow).Copy
 

Trevor G

Well-known Member
Joined
Jul 17, 2008
Messages
6,704
Office Version
  1. 2016
Platform
  1. Windows
Have you checked the VBA screen to make sure sheet1 exists
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

Good question!

The file I'm trying to import data is a .xlsx file so when you try to access the VB editor, it doesn't actually show anything!

I would have thought that it would have a default name of 'Sheet1' in the VB editor, as there is only every on sheet in that file.

The only things that change are the data and the name of the sheet, depending on what the other person wants to call it.

Given that it's an xlsx file, do you know if it's still possible to select the sheet using the code name instead of the sheet name?
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
36,730
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
You can't directly refer to the code name of a sheet in another workbook to the code (well, you can, but it's generally more trouble than it's worth). You'd need to loop through the sheets in that workbook and check the codename to find the one you want.
 
L

Legacy 456155

Guest

ADVERTISEMENT

You are probably better to use:

VBA Code:
Set iWBP = ActiveWorkbook
'Get data from  file
With iWBP.Sheet1
    lastrow = .Cells(.Rows.CountLarge, "A").End(xlUp).Row
    .Range("A1:AE" & lastrow).Copy
End With

...assuming Sheet1 is the correct codename.
 

VBasic2008

Board Regular
Joined
Oct 25, 2016
Messages
88
Office Version
  1. 2019
Platform
  1. Windows
Code Name In Another Workbook

Instead of the line Worksheets("Sheet1").Select you could use the following line:

VBA Code:
defineSheetByCodeName(iWBP, "Sheet1").Select

backed up by the following function:

VBA Code:
Function defineSheetByCodeName(Book As Workbook, SheetCodeName As String) _
         As Object
    Dim Sheet As Object
    For Each Sheet In Book.Sheets
        If StrComp(Sheet.CodeName, SheetCodeName, vbTextCompare) = 0 Then
            Set defineSheetByCodeName = Sheet
            Exit For
        End If
    Next Sheet
End Function
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Yes.
You are probably better to use:

VBA Code:
Set iWBP = ActiveWorkbook
'Get data from  file
With iWBP.Sheet1
    lastrow = .Cells(.Rows.CountLarge, "A").End(xlUp).Row
    .Range("A1:AE" & lastrow).Copy
End With

...assuming Sheet1 is the correct codename.
Thanks for this. I tried it, but I got an error with this that said 'Run time error: 438 'Object doesn't support this property or method'
 

Mr2017

Well-known Member
Joined
Nov 28, 2016
Messages
634
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Code Name In Another Workbook

Instead of the line Worksheets("Sheet1").Select you could use the following line:

VBA Code:
defineSheetByCodeName(iWBP, "Sheet1").Select

backed up by the following function:

VBA Code:
Function defineSheetByCodeName(Book As Workbook, SheetCodeName As String) _
         As Object
    Dim Sheet As Object
    For Each Sheet In Book.Sheets
        If StrComp(Sheet.CodeName, SheetCodeName, vbTextCompare) = 0 Then
            Set defineSheetByCodeName = Sheet
            Exit For
        End If
    Next Sheet
End Function
Thanks for this - but can you please clarify where in the code I should place the function? TIA

I tried this, but keep on getting an error that says it's expecting an 'End Sub' but I have one at the end....And it stops at the line defineSheetByCodeName

VBA Code:
Dim iWBP As Workbook

Set ReportP = ActiveWorkbook
Set fd = Application.FileDialog(msoFileDialogOpen)
fd.Filters.Clear
fd.Filters.Add "Custom Excel Files", "*.xlsx, *.xlsm, *.xls"
fd.AllowMultiSelect = False
fd.InitialFileName = Environ("UserProfile") & "\Box\Proposals & Contracts"
filewaschosen = fd.Show
fd.Execute

Set iWBP = ActiveWorkbook

defineSheetByCodeName(iWBP, "Sheet1").Select

Function defineSheetByCodeName(Book As Workbook, SheetCodeName As String) _
         As Object
    Dim Sheet As Object
    For Each Sheet In Book.Sheets
        If StrComp(Sheet.CodeName, SheetCodeName, vbTextCompare) = 0 Then
            Set defineSheetByCodeName = Sheet
            Exit For
        End If
    Next Sheet
End Function
'Get data from  file

Sheet1.Select
lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Range("A1:AE" & lastrow).Copy



'End Function

End Sub
 

Watch MrExcel Video

Forum statistics

Threads
1,130,169
Messages
5,640,550
Members
417,151
Latest member
ChickenTenderer

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
Top