ExcelBanter - Sheets Loops error

ExcelBanter (https://www.excelbanter.com/)

- Excel Programming (https://www.excelbanter.com/excel-programming/)

- - Sheets Loops error (https://www.excelbanter.com/excel-programming/410929-sheets-loops-error.html)

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

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

Sheets Loops error

Thank you both these suggestions worked beautifully!

Brian