How Do You Reference with a Single Statement Many Worksheet Names?

[Eric G]

I am doing a data pull that consolidates the data from multiple workbooks into a single workbook. My current code works as advertised; however, I would like to make the code more flexible by having it refer to specific cells in a spreadsheet on what and where to pull my data. I am having a problem with how to reference multiple workbooks with a single statement.

So, in regards to:

'DATA PULL
    With wsConsolidate_2
         .Range(.Cells(LRT2 + 1, 2), wsConsolidate_2.Cells(LRT2 + LRwsVar - 4, 20)).Value = wsVar.Range("A5:S" & LRwsVar).Value
    End With

Where I can get "wsVar" to refer to a single, specific worksheet with no problem, such as:

Dim wsVar as Worksheet
Set wsVar = Sheets("Apple")

But now, how do you go about rewriting "wsVar" where it refers to multiple worksheets as referenced in a column range of cells? Such as wsExtract.Range("A2:A8"), which in the spreadsheet it would look like:

A1 Worksheet Name
A2 Apple
A3 Banana
A4 Orange
A5 Pear
A6 Grape
A7 Mango
A8 Pineapple

I don't want to have to go into the code every time there is a change with these names. It would be nice if I could just update the column of worksheet names.

Any ideas?

 

[ExcelGzh]

This works best if you define a named range and then highlight the range with a color so you know which cells are in the range and which aren't (in case you want to add some more). Then use a For-Next loop.

Dim RowCounter as Integer
For RowCounter = Range("RangeName").Row to Range("RangeName").Rows.Count - 1
wsVar = Sheets(Cells(RowCounter, Range("RangeName").Column))
...
Next RowCounter

 

[RoryA]

I'd suggest a For Each loop was simpler:

Dim cell as range
For each cell in Range("RangeName").Cells
Set wsVar = Sheets(cell.value)
' do things to sheet
Next cell

 

[Eric G]

I've tried both approaches and had more progress with yours Rory. Though I did take up your highlighting suggestion, Gzh. Nonetheless, thank you both for responding.

I should've mentioned that each workbook has only one sheet with a name from the Worksheet Name column. As long as the file first to loop to open has a worksheet named from the first name listed in the Worksheet Name column, then the data is pulled. All of the subsequent workbooks do continue to loop open, but the data from their worksheets aren't being pulled despite those worksheets with having names listed in the Worksheet Name column.

Now, if the worksheet name of the workbook looped to open first does not match the first name listed in the Worksheet Name column, the following error appears: "Run-time error '9': Subscript out of range"

How can the code be modified to check the worksheet name against the list of "approved" (if you will) worksheet names in order to run the data pull? And when that workbook closes, the code rechecks the worksheet in the next looped open workbook.

Suggestions?

 

[ExcelGzh]

As well as looping through the list of sheet names as per the previous For each cell etc code, you also then need to check if the sheet name exists in the open workbook. Here is some code that will check if a sheet exists in a workbook.

Function FnWorksheetExists(WorkbookName As Workbook, WorksheetName As String) As Boolean
'Determines if WorksheetName exists within WorkbookName

  Dim Sht As Worksheet

  FnWorksheetExists = False
  For Each Sht In WorkbookName.Worksheets
    If UCase(Sht.Name) = UCase(WorksheetName) Then
      FnWorksheetExists = True
      Exit Function
    End If
  Next Sht
End Function 'FnWorksheetExists

In the main body of the code, you first of all open the workbook you want to pull data from, then you start working through the list of sheet names until you find the one that exists on the open workbook. You do the pull, close the workbook, open the next workbook and start at the top of the list again looking for the sheet that is on the next workbook. Your code will need to differentiate between the workbook that is having the data put into it and the workbook that is having the data pulled from it.

Dim cell as range
Workbooks.Open "PullWorkbookName"

For each cell in Range("RangeName").Cells
if FnWorksheetExists("PullWorkbookName", cell.value) then
  Set wsVar = Sheets(cell.value)
  ' do things to sheet
  Exit For 'Or you can just let it work through the whole list
End if 'FnWorksheetExists
Next cell

'Open next Pull workbook and loop through the names again