windows 2000
excel and access 2002
Hi,
Looking for help.
Running Excel from Access.
Have created several Worksheets in an excel spreadsheet Labeling all
starting with R-
I did this from Excel and it put the sheets in R-1, R-2 etc with R-1 on the
far left and R-22 on the far right.
When I do it from Access I get it reversed.
When I did the following macro from excel I got R-1 first and R-22 last on
my combined sheet.
Now from Access I get R-22 first and R-1 last.
Is there a way in the following macro to tell it to start with the first
sheet?
I have tried: with no effect
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Worksheets(1).Activate

Would sure appreciate any help.
Thanks.....

Public Sub Simulation_All()
'To combine all the Simulation Calculation dBA into one Worksheet
Dim strSheetName As String
Dim strRange As String
Dim i As Integer

Sheets.Add Type:="Worksheet"
ActiveSheet.Name = "All-dBA"

Range("A2").Value = "Combine all sheets R-"
Range("A3").Value = "Tab"
Range("B3").Value = "Lvl 10"
Range("C3").Value = "Lvl 50"
Range("D3").Value = "Lvl 90"
Range("E3").Value = "Lvl 99"

'Loop through all Sheets for those starting with "R-"
i = 4
'ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Worksheets(1).Activate
Set FirstBook = Workbooks.Item(1)
' Sheets(1).Select
For Each shtNext In Sheets
strSheetName = shtNext.Name
'for only the sheets starting with R-
If Left(strSheetName, 2) = "R-" Then
Sheets(strSheetName).Activate
Range("K3:K6").Copy
Sheets("All-dBA").Activate
strRange = "B" & i
Range(strRange).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
strRange = "A" & i
Range(strRange).Value = strSheetName
i = i + 1
End If
Next shtNext

End Sub

If I understand this correctly, your problem is that the loop "For Each
shtNext in Sheets" does not necessarily return the sheets in the order
R-1, R-2, etc. This is happening because Sheets is a collection, and
you can not control the order.

If you know there are exactly 22 sheets, then do something like this:

For i = 1 to 22
strName = "R-" + CStr (i)
set shtNext = Sheets(strName)
.....
next i

Of course, this will need to be doctored if there are a variable number
of sheets, or if some names do not occur.

Hope this helps,
Dom




FGM wrote:
windows 2000
excel and access 2002
Hi,
Looking for help.
Running Excel from Access.
Have created several Worksheets in an excel spreadsheet Labeling all
starting with R-
I did this from Excel and it put the sheets in R-1, R-2 etc with R-1 on the
far left and R-22 on the far right.
When I do it from Access I get it reversed.
When I did the following macro from excel I got R-1 first and R-22 last on
my combined sheet.
Now from Access I get R-22 first and R-1 last.
Is there a way in the following macro to tell it to start with the first
sheet?
I have tried: with no effect
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Worksheets(1).Activate

Would sure appreciate any help.
Thanks.....

Public Sub Simulation_All()
'To combine all the Simulation Calculation dBA into one Worksheet
Dim strSheetName As String
Dim strRange As String
Dim i As Integer

Sheets.Add Type:="Worksheet"
ActiveSheet.Name = "All-dBA"

Range("A2").Value = "Combine all sheets R-"
Range("A3").Value = "Tab"
Range("B3").Value = "Lvl 10"
Range("C3").Value = "Lvl 50"
Range("D3").Value = "Lvl 90"
Range("E3").Value = "Lvl 99"

'Loop through all Sheets for those starting with "R-"
i = 4
'ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Worksheets(1).Activate
Set FirstBook = Workbooks.Item(1)
' Sheets(1).Select
For Each shtNext In Sheets
strSheetName = shtNext.Name
'for only the sheets starting with R-
If Left(strSheetName, 2) = "R-" Then
Sheets(strSheetName).Activate
Range("K3:K6").Copy
Sheets("All-dBA").Activate
strRange = "B" & i
Range(strRange).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
strRange = "A" & i
Range(strRange).Value = strSheetName
i = i + 1
End If
Next shtNext

End Sub

Hi,
thank you for your suggestion. I will look at it. I did find something
that would work for me.....

Sheets.Add After:=ActiveSheet
I knew there was most likely a simple solution... but not always easy to find.
Thank you it is most appreciated.


" wrote:

If I understand this correctly, your problem is that the loop "For Each
shtNext in Sheets" does not necessarily return the sheets in the order
R-1, R-2, etc. This is happening because Sheets is a collection, and
you can not control the order.

If you know there are exactly 22 sheets, then do something like this:

For i = 1 to 22
strName = "R-" + CStr (i)
set shtNext = Sheets(strName)
.....
next i

Of course, this will need to be doctored if there are a variable number
of sheets, or if some names do not occur.

Hope this helps,
Dom




FGM wrote:
windows 2000
excel and access 2002
Hi,
Looking for help.
Running Excel from Access.
Have created several Worksheets in an excel spreadsheet Labeling all
starting with R-
I did this from Excel and it put the sheets in R-1, R-2 etc with R-1 on the
far left and R-22 on the far right.
When I do it from Access I get it reversed.
When I did the following macro from excel I got R-1 first and R-22 last on
my combined sheet.
Now from Access I get R-22 first and R-1 last.
Is there a way in the following macro to tell it to start with the first
sheet?
I have tried: with no effect
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Worksheets(1).Activate

Would sure appreciate any help.
Thanks.....

Public Sub Simulation_All()
'To combine all the Simulation Calculation dBA into one Worksheet
Dim strSheetName As String
Dim strRange As String
Dim i As Integer

Sheets.Add Type:="Worksheet"
ActiveSheet.Name = "All-dBA"

Range("A2").Value = "Combine all sheets R-"
Range("A3").Value = "Tab"
Range("B3").Value = "Lvl 10"
Range("C3").Value = "Lvl 50"
Range("D3").Value = "Lvl 90"
Range("E3").Value = "Lvl 99"

'Loop through all Sheets for those starting with "R-"
i = 4
'ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Worksheets(1).Activate
Set FirstBook = Workbooks.Item(1)
' Sheets(1).Select
For Each shtNext In Sheets
strSheetName = shtNext.Name
'for only the sheets starting with R-
If Left(strSheetName, 2) = "R-" Then
Sheets(strSheetName).Activate
Range("K3:K6").Copy
Sheets("All-dBA").Activate
strRange = "B" & i
Range(strRange).Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True
strRange = "A" & i
Range(strRange).Value = strSheetName
i = i + 1
End If
Next shtNext

End Sub