Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets Loops error
I am trying to create a loop with variable sheet names and I am having
issues. The error I'm getting right now is "Duplicate declaration in current scope." I declare all the sheet names as Strings because if I don't I get "Compile Error: Sub or Function not defined" This code is fairly simple so I'm hoping that someone can take a look and easily see what I'm doing wrong. Sub MyLoop() Dim DataSheet(1) As String Dim DataSheet(2) As String Dim DataSheet(3) As String Dim DataSheet(4) As String Dim DataSheet(5) As String Dim RptSheet(1) As String Dim RptSheet(2) As String Dim RptSheet(3) As String Dim RptSheet(4) As String Dim RptSheet(5) As String i = 1 DataSheet(1) = "Owned Data" DataSheet(2) = "Owned+BenchData" DataSheet(3) = "RiskIndexExpData" DataSheet(4) = "ARIE Data" DataSheet(5) = "Owned+BenchData" RptSheet(1) = "Owned" RptSheet(2) = "Plus Benchmark" RptSheet(3) = "RiskIndexExposures" RptSheet(4) = "Asset Risk Index Exposures" RptSheet(5) = "ARIE - Plus Benchmark" Do Until i 5 Sheets(DataSheet(i)).Select Cells.Select Selection.Copy Sheets(RptSheet(i)).Select Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False i = i + 1 Loop End Sub Thank you I appreciate it! Brian |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets Loops error
Brian,
You are using arrays, so you need to declare your variables as arrays. Change Dim DataSheet(1) As String Dim DataSheet(2) As String Dim DataSheet(3) As String Dim DataSheet(4) As String Dim DataSheet(5) As String Dim RptSheet(1) As String Dim RptSheet(2) As String Dim RptSheet(3) As String Dim RptSheet(4) As String Dim RptSheet(5) As String to Dim DataSheet(1 to 5) As String Dim RptSheet(1 to 5) As String You could also use variables like this, but you would have a harder time looping... Dim DataSheet1 As String Dim DataSheet2 As String Dim DataSheet3 As String Dim DataSheet4 As String Dim DataSheet5 As String Dim RptSheet1 As String Dim RptSheet2 As String Dim RptSheet3 As String Dim RptSheet4 As String Dim RptSheet5 As String -- HTH, Bernie MS Excel MVP "Bongard" wrote in message ... I am trying to create a loop with variable sheet names and I am having issues. The error I'm getting right now is "Duplicate declaration in current scope." I declare all the sheet names as Strings because if I don't I get "Compile Error: Sub or Function not defined" This code is fairly simple so I'm hoping that someone can take a look and easily see what I'm doing wrong. Sub MyLoop() Dim DataSheet(1) As String Dim DataSheet(2) As String Dim DataSheet(3) As String Dim DataSheet(4) As String Dim DataSheet(5) As String Dim RptSheet(1) As String Dim RptSheet(2) As String Dim RptSheet(3) As String Dim RptSheet(4) As String Dim RptSheet(5) As String i = 1 DataSheet(1) = "Owned Data" DataSheet(2) = "Owned+BenchData" DataSheet(3) = "RiskIndexExpData" DataSheet(4) = "ARIE Data" DataSheet(5) = "Owned+BenchData" RptSheet(1) = "Owned" RptSheet(2) = "Plus Benchmark" RptSheet(3) = "RiskIndexExposures" RptSheet(4) = "Asset Risk Index Exposures" RptSheet(5) = "ARIE - Plus Benchmark" Do Until i 5 Sheets(DataSheet(i)).Select Cells.Select Selection.Copy Sheets(RptSheet(i)).Select Range("A1").Select Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ :=False, Transpose:=False i = i + 1 Loop End Sub Thank you I appreciate it! Brian |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Sheets Loops error
Thank you both these suggestions worked beautifully!
Brian |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Ifs, Selects & Loops Error Statements | Excel Programming | |||
ADO & Loops: error in code? | Excel Programming | |||
On Error and Loops | Excel Programming | |||
Error Handling with Nested Loops | Excel Programming | |||
Error Handling and For-Next Loops | Excel Programming |