Transferring a dynamically named string variable to another string variable.

**Concorde Rules** · 10-09-2012, 03:54 AM

Hi, this is a complex one to explain but I'll try. (Also hampers googling!)

I have six spreadsheets with names, I need to loop through these spreadsheets in order to clear the contents before I then insert some new data.

I need the [Active_Sheet = "Sheet_" & iSheet] line to transfer the *contents* of the variable (for example: "Sheet_3") to Active_Sheet, rather than the name of the variable it currently does.


Dim Sheet_1 As String
Dim Sheet_2 As String
Dim Sheet_3 As String
Dim Sheet_4 As String
Dim Active_Sheet As String

Sheet_1 = "Monkies"
Sheet_2 = "Bananas"
Sheet_3 = "Sheep"
Sheet_4 = "Dogs"


Do Until iSheet = 6

     Active_Sheet = "Sheet_" & iSheet

    Set Excel_Sheet = Excel_Workbook.Sheets(Active_Sheet)
    Call Clear_Cells(Excel_Sheet)

    iSheet = iSheet + 1
    
Loop

Thoughts?

Thanks!

**Concorde Rules** · 10-09-2012, 03:59 AM

Right, after reading through this forum I stumbled across the answer! An array!

http://www.mrexcel.com/forum/excel-q...ble-names.html

**Andy Pope** · 10-09-2012, 04:00 AM

When not use an array for the sheet names?

Dim SheetName(1 To 4) As String
Dim Active_Sheet As String
Dim iSheet As Integer

SheetName(1) = "Monkies"
SheetName(2) = "Bananas"
SheetName(3) = "Sheep"
SheetName(4) = "Dogs"

iSheet = 1
Do Until iSheet = 5

     Active_Sheet = SheetName(iSheet)

    Set Excel_Sheet = Excel_Workbook.Sheets(Active_Sheet)
    Call Clear_Cells(Excel_Sheet)

    iSheet = iSheet + 1
    
Loop