Multiple Workbooks - Issue with not Able to Activate Second Workbook to filter, Sort, etc.

[VBAToolMaker]

Version: Excel 2010

My main spreadsheet is to consolidate specific month of information from regional office spreadsheets, containing tables with multiple month data in different tabs. I want to either sort & copy or filter & copy from the regional office spreadsheets. The driving macro is in the main spreadsheet.

My issue has to do with not successfully activating the open regional office spreadsheet in order to define the variable range in order to either sort or filter.

I have tried two different methods, where both receive errors but different ones.

Option 1: Sheet9.Select (Object Required error)
Option 2: Workbooks(tROWorkbookName).Sheet9.Select (Error 438 "Object does not support this property or method")

I am still trouble shooting the draft module and am at the stage shown in the excerpt below. The present version shows option 1.

Private Sub UploadMonthlyData()

ChDir tSourceDataFilePath

'File Parameters
nFilePathCol = 1
nFileNameCol = 2
nFileRow = 2

Windows(tMainWorkbookName).Activate
Sheet10.Range("A2").Select
tFilepath = Cells(nFileRow, nFilePathCol).Value
tFileName = Cells(nFileRow, nFileNameCol).Value
nLengthFilePath = Len(tFilepath)
nLengthFileName = Len(tFileName)
tFilePathWoName = Left(tFilepath, nLengthFilePath - nLengthFileName)
tMainReportMonth = Range("MONTHREPORT").Value

Do While tFilepath <> "" 'Going from top to bottom of the list of files within the provincial tool

'////// Opening each file without updating links
Set wb2 = Workbooks.Open(tFilepath, False, False)
tROWorkbookName = wb2.Name

Workbooks(tMainWorkbookName).Activate
Sheet10.Select
Range("A2").Select

'////// UPLOAD DATA /////

nMainReferenceRow = 3

nIPMainDatabaseFirstRow = Range("IPDATABASEFIRSTRECORDROW").Value
nIPMainDatabaseLastRow = Range("IPDATABASELASTRECORD").Value
nIPMainReferenceDatabaseColumn = Range("IPREFERENCEUPLOADDATEDATABASECOLUMN").Value
tIPMainReferenceColumnName = Sheet6.Cells(nMainReferenceRow, nIPMainReferenceDatabaseColumn - 3).Value

wb2.Activate
With wb2
nIPRODatabaseFirstRow = Range("IPDATABASEFIRSTRECORDROW").Value
nIPRODatabseLastColumn = Range("IPDATABASELASTCOLUMN").Value
nIPRODatabaseReportMonthColumn = Range("IPDATABASEREPORTMONTHCOLUMN").Value
nIPRODatabaseUniqueChildIndexColumn = Range("IPDATABASEUNIQUECHILDINDEXCOLUMN").Value
nIPRODatabaseLastRow = Range("IPDATABASELASTRECORD").Value
End With

'//////Sort Database by report month and person
'//////'Important that exact same structure for both main and regional office files.

Workbooks(tROWorkbookName).Activate
Sheet9.Cells(nIPRODatabaseFirstRow, 1).Select
Set rIPDatabaseSortRange = Range(Sheet9.Cells(nIPRODatabaseFirstRow, 1), Sheet9.Cells(nIPRODatabaseLastRow, nIPRODatabseLastColumn))
With Sheet9.Sort
.SortFields.Clear
.SortFields.Add Key:= _
Cells(nIPMainDatabaseFirstRow, nIPRODatabaseReportMonthColumn), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
.SortFields.Add Key:= _
Cells(nIPMainDatabaseFirstRow, nIPRODatabaseUniqueChildIndexColumn), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
.SetRange rIPDatabaseSortRange
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.Apply
End With

 

[JoeMo]

Have you stepped through the code to see if the workbook is activated?

 

[VBAToolMaker]

Thank you for getting back to me.

Regarding my troubleshooting, what I do is step through the program to diagnose which exact lines are causing a problem.

The file related to wb2 opens successfully. As well, the section 'wb2.Activate -> With wb2' steps, earlier on, work fine, as both files use the same range names, but may contain different contents. The steps collect the correct information.

I have tried:
(1) wb2.Activate -> Sheet9.Select
(2) wb2.Sheet9.Activate
(3) Workbooks(tROWorkbookName).Activate -> Sheet9.Select
(4) Workbooks(tROWorkbookName).Sheet9.Activate

And they all lead to either of the two mentioned errors. And "tROWorkbookName" contains the text to the correct name of the file to process.

As there are extensive posts on Mr. Excel, my hunch has to possibly with other code not shown. I definitely don't have any missing 'IFs or End Ifs', or "Do without Loops" errors, as there are no immediate compiling errors.

Your experience in addressing this error is appreciated.

 

[JoeMo]

Are you certain there is a Sheet9 in the workbook AND, if yes, that it is not hidden? You can't select a hidden sheet.
What is the tab name of Sheet9?

 

[VBAToolMaker]

Thank you again, for getting back to me.

The main spreadsheet is being developed as a master file with the exact same data file structure as the regional office spreadsheets. That meant that both spreadsheets originally had a "Sheet9" code name with a tab name "IP_MONTHLYDATABASE". In fact, while both had the same code name, I had the sort macro running; however, it would sort the main file database and not the intended regional office one. In response, I renamed the sheet code name for the main file spreadsheet away from "Sheet9", such that only the regional office spreadsheet would have "Sheet9". (And that is when I started to get these errors.)

All tabs are currently visible.

As well, I have a check (not shown) as to whether a procedure was completed, which would ensure the data is finalized before uploading. If not, I would run the macro contained within in the regional office spreadsheet. And that macro would successfully run.

 

[JoeMo]

If you open the regional office workbook, press alt+F11 to open the VBE, do you see a sheet with code name Sheet9 in the workbook's VBA Project? Have you tried changing the sheet9 to Sheets("IP_MONTHLYDATABASE").Select after you have activated the workbook? You can do this in the immediate window.

 

[VBAToolMaker]

Thank you, Joe.

I have been trying to avoid using the sheet name in case it gets changed by the user causing the macro to crash. However, that said, the change to 'Sheets("IP_MONTHLYDATABASE")' resulted in removing the error and allowing the macro to proceed past that line.

When it comes to "Sheet9", I confirm its existence. I have developed various macros in the regional office spreadsheet to run off of the code name. And that tab is the most significant of all the tabs in that spreadsheet.

It does answer the question in that Excel is saying that "sheet9" does not exist in its mind. However, it does exist and I would prefer being able to continue to be able to use the code name. I can refer the tab name as a backup process.

Thank you, Joe, again.

 

[JoeMo]

Strange. Here's a sub you can run to see if Sheet9 is seen by VBA. Install this as a standard module in your workbook. Then insert a new sheet and with the new sheet active, run the sub. It will list out all sheet tab names in column B and their code names in Column C. See if Sheet9 appears where you expect it.

Sub SheetCodeNames()
Dim Sht As Worksheet, Ct As Integer
Ct = ThisWorkbook.Worksheets.Count
[b1].Value = "Worksheet Name"
[c1].Value = "Code Name"
For i = 1 To Ct
    [b1].Offset(i, 0).Value = Worksheets(i).Name
    [c1].Offset(i, 0).Value = Worksheets(i).CodeName
Next i
With [b1:c1]
    .EntireColumn.AutoFit
    .Font.Bold = True
End With
[b1].CurrentRegion.Sort key1:=[C2], Order1:=xlAscending, _
Header:=xlYes, OrderCustom:=1, _
MatchCase:=False, _
Orientation:=xlTopToBottom

End Sub

 

[VBAToolMaker]

Thank you, Joe.

The coding worked perfectly within the regional office workbook. It confirmed the existence of "Sheet9".

I realize now the issue originated with trying to reference the code name in another workbook. Excel would not recognize that code name, as it existed outside of the main workbook.

Through continued search I found a solution that seems to initially be working, although I have not used it thoroughly yet. I found it on a website with search title "Using Worksheet CodeNames in Other Workbooks", whereby a custom function is created. This function would look at the code names. A variable dimensioned as Excel.Worksheet would be used in the macro. So I could create "ROSheet9", which is referencing to the code name in the regional office workbook, to be distinct from "Sheet9", which is contained within the main workbook.

Your experience could have lead you to another solution.

I can share the result of this solution I found, once I have used the method in my testing of the macor.

Thanks again.