Sheets Loops error

  • Thread starter Thread starter Bongard
  • Start date Start date
B

Bongard

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
 
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
 
Try this idea

Sub dosheets()

Dim DataSheet(1 To 5) As String
Dim RptSheet(1 To 5) As String

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"

For i = 1 To 5
Sheets(DataSheet(i)).cells.Copy
Sheets(RptSheet(i)).Range("a1").PasteSpecial Paste:=xlPasteValues
Next i

End Sub
 
Back
Top