Solved: HELP Worksheet.CodeName is not consistent when copying a worksheet into a workbook

[Elishajah]

I am using a macro in one workbook to open another workbook, copy the first sheet in that 'opened' workbook, and then close the 'opened' workbook. My macro performs this operation twice, and then imports a text file into a new worksheet. So, after these three operations, I have three new worksheets.

Here's the problem:

When the new worksheets are added to my workbook (the one running the macro) excel does not always use the Workbook.CodeName value in the sequence for each new worksheet.

Specifically, the original sheet in the workbook with the macro has a CodeName of 'Sheet1'. The new sheets, though, will sometimes have CodeNames of 'Sheet5', 'Sheet6', and 'Sheet7' respectively.

This is a problem because the next macro that runs has specific reference to Sheets 2-4, which may or may not exist, depending on Excel's mood :banghead:

Is there any way for me to 'refresh' or something like that to make sure that the newly added sheets get CodeNames Sheet2 through Sheet4?

Here's my code (I know it's ugly; I'm new at this):


Sub GetFilesToProcess()
' Path1 and Path2 are variables that I defined as strings
Dim Path1 As String
Dim Path2 As String
' Book_1 and Book_2 are variables that I defined as objects (workbooks)
' so that I can copy their sheets later
Dim Book_1 As Workbook
Dim Book_2 As Workbook
Dim ThisBook As Workbook
Set ThisBook = ThisWorkbook
' If old sheets exist, delete the old sheets
If Sheets.Count > 1 Then
MsgBox ("You must delete the sheets from the previous analysis. Please delete these sheets, " _
& "SAVE AND CLOSE THIS WORKBOOK, and then re-run the macro." _
& " DO NOT DELETE THE AR Submittal Analysis sheet!!!")
GoTo HandleCancel

Else: GoTo Equals1Sheet

End If

Equals1Sheet:
' Open a window that lets the user specify the file to be openned.
' The file's path is the string defined as Path1
Path1 = Application.GetOpenFilename(filefilter:="EXCEL files (*.xls),*.xlx", _
Title:="Select the first workbook you wish to process:")
'If the user clicks the cancel button, end the whole thing.
If Path1 = "False" Then
GoTo HandleCancel
End If

Set Book_1 = Workbooks.Open(Filename:=Path1, ReadOnly:=False)
Book1_Name = Book_1.Name
Workbooks(Book1_Name).Sheets(1).Copy before:=ThisBook.Sheets(1)
Workbooks(Book1_Name).Close
HandleError:
' Open a window that lets the user specify the file to be openned.
' The file's path is the string defined as Path2
Path2 = Application.GetOpenFilename(filefilter:="EXCEL files (*.xls),*.xlx", _
Title:="Select the second workbook you wish to process:")
' If the user clicks the cancel button, end the whole thing.
If Path2 = "False" Then
GoTo HandleCancel

' Otherwise, if the user selects the same file twice, go back to 'HandleError:'
' and let the user select the second file again.
ElseIf Path1 = Path2 Then
MsgBox ("You cannot select " & Path1 & " twice. Please select another file.")
GoTo HandleError
End If

Set Book_2 = Workbooks.Open(Filename:=Path2, ReadOnly:=False)
Book2_Name = Book_2.Name
Workbooks(Book2_Name).Sheets(1).Copy before:=ThisBook.Sheets(1)
Workbooks(Book2_Name).Close

' Specify the text file to import
Dim TextFilePath As String
TextFilePath = Application.GetOpenFilename(filefilter:="TEXT files (*.txt),*.txx", _
Title:="Select the text file to import:")
'If the user clicks the cancel button, end the whole thing.
If TextFilePath = "False" Then
GoTo HandleCancel
End If
' The next section performs 3 steps, twice each (once per workbook):
' 1. Open the user specified workbook
' 2. Copy the first sheet in the user specified book to before the first sheet in this book
' 3. Close the user specified workbook
' Import the actual file by the variable TextFilePath
ThisBook.Worksheets.Add
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & TextFilePath, Destination:=Range("a1"))
.Name = TextFilePath
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = xlWindows
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = True
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = True
.TextFileColumnDataTypes = Array(1)
.Refresh BackgroundQuery:=False
End With
ActiveSheet.Name = "PDF Aging Detail"
MsgBox "The following files were imported: " & Path1 & ", " & Path2 & ", " & TextFilePath & _
". Please run the macro called ProcessFiles."
HandleCancel:
End Sub



The next macro is then run:

Public AR_Report As Worksheet
Public AgingDetail As Worksheet
Public AR_Submittal As Worksheet
Public PDF_AgingDetail As Worksheet
Public Sub ProcessFiles()
Set PDF_AgingDetail = Sheets("PDF Aging Detail")
PDF_AgingDetail.Select
PDF_AgingDetail.UsedRange.Select
Selection.Sort Key1:=ActiveCell, Order1:=xlAscending, Header:=xlGuess, _
OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
DataOption1:=xlSortNormal ' This last parameter is not available in earlier versions of Excel


' Find and define the AR Report sheet

If Sheet1.Range("b1").Value = "INVOICE" Then
Set AR_Report = Sheet1
GoTo FoundAR_Report
ElseIf Sheet2.Range("b1").Value = "INVOICE" Then
Set AR_Report = Sheet2
GoTo FoundAR_Report
ElseIf Sheet3.Range("b1").Value = "INVOICE" Then
Set AR_Report = Sheet3
GoTo FoundAR_Report
ElseIf Sheet4.Range("b1").Value = "INVOICE" Then
Set AR_Report = Sheet4
GoTo FoundAR_Report
End If
FoundAR_Report:

' Find and define the Aging Detail sheet

If Sheet1.Range("a1").Value = "CUSTOMER" Then
Set AgingDetail = Sheet1
GoTo FoundAgingDetail
ElseIf Sheet2.Range("a1").Value = "CUSTOMER" Then
Set AgingDetail = Sheet2
GoTo FoundAgingDetail
ElseIf Sheet3.Range("a1").Value = "CUSTOMER" Then
Set AgingDetail = Sheet3
GoTo FoundAgingDetail
ElseIf Sheet4.Range("a1").Value = "CUSTOMER" Then
Set AgingDetail = Sheet4
GoTo FoundAgingDetail
End If
FoundAgingDetail:

' Find and define the AR Submittal Analysis sheet

If Sheet1.Range("a1").Value = "Invoice Number" Then
Set AR_Submittal = Sheet1
GoTo FoundAR_Submittal
ElseIf Sheet2.Range("a1").Value = "Invoice Number" Then
Set AR_Submittal = Sheet2
GoTo FoundAR_Submittal
ElseIf Sheet3.Range("a1").Value = "Invoice Number" Then
Set AR_Submittal = Sheet3
GoTo FoundAR_Submittal
ElseIf Sheet4.Range("a1").Value = "Invoice Number" Then
Set AR_Submittal = Sheet4
GoTo FoundAR_Submittal
End If
FoundAR_Submittal:

' some other stuff happens after this



The error, of course, occurs when the object "Sheet2" is not found:


If Sheet1.Range("b1").Value = "INVOICE" Then
Set AR_Report = Sheet1
GoTo FoundAR_Report
ElseIf Sheet2.Range("b1").Value = "INVOICE" Then
Set AR_Report = Sheet2
GoTo FoundAR_Report





Please help, and thanks for reading all of this.

-Mark

[mdmackillop]

Hi Elishaja,
Welcome to VBAX.

How about

Dim sh As Worksheet
For Each sh In Worksheets
If sh.Range("b1").Value = "INVOICE" Then
Set AR_Report = sh
Exit For
End If
Next

[Elishajah]

That worked! Thanks so much!

Here is the updated code:


Dim sh1 As Worksheet
For Each sh1 In Worksheets
If sh1.Range("b1").Value = "INVOICE" Then
Set AR_Report = sh1
Exit For
End If
Next
Dim sh2 As Worksheet
For Each sh2 In Worksheets
If sh2.Range("a1").Value = "CUSTOMER" Then
Set AgingDetail = sh2
Exit For
End If
Next

Dim sh3 As Worksheet
For Each sh3 In Worksheets
If sh3.Range("a1").Value = "Invoice Number" Then
Set AR_Submittal = sh3
Exit For
End If
Next

[mdmackillop]

As long as there is no possible conflict betwwen A1 and B1


Dim sh As Worksheet
For Each sh In Worksheets
Select Case sh.Range("b1").Value
Case "INVOICE"
Set AR_Report = sh
End Select
Select Case sh.Range("a1").Value
Case "CUSTOMER"
Set AgingDetail = sh
Case "Invoice Number"
Set AR_Submittal = sh
End Select
Next