Iterating Through Workbook if Don't Know Sheet Names
 

I've got an Access database that produces an "Active Project" report.
It creates a new worksheet tab for every project we have running in
our office. I chose to give the worksheet the same name as the
project, thus allowing for easy browsing when a user opens the Excel.

Now I want to create a macro that copies the worksheet over to a
PowerPoint slide. I am using this line of code:

Worksheets("Test_Project").Range("B1:P28").CopyPic ture
Appearance:=xlPrinter, Format:=xlPicture

But notice I had to hard code "Test_Project" as the Worksheet name. I
don't want to hard code the name because our project list is always
changing.

I could use "ActiveSheet.Name", but then I would have to run the macro
on every worksheet.

Is there a way I can tell Excel to select the first worksheet
(whatever that may be called) and then go on to the second, and third
-- ignoring the tab names and caring only about the tab position?

Thanks,

Chris

Iterating Through Workbook if Don't Know Sheet Names
 

You can refer to sheets with an index rather than a name:

Sub sht()
nsh = Sheets.Count
For n = 1 To nsh
MsgBox (Sheets(n).Name)
Next
End Sub

--
Gary''s Student - gsnu200729


"wrldruler" wrote:

I've got an Access database that produces an "Active Project" report.
It creates a new worksheet tab for every project we have running in
our office. I chose to give the worksheet the same name as the
project, thus allowing for easy browsing when a user opens the Excel.

Now I want to create a macro that copies the worksheet over to a
PowerPoint slide. I am using this line of code:

Worksheets("Test_Project").Range("B1:P28").CopyPic ture
Appearance:=xlPrinter, Format:=xlPicture

But notice I had to hard code "Test_Project" as the Worksheet name. I
don't want to hard code the name because our project list is always
changing.

I could use "ActiveSheet.Name", but then I would have to run the macro
on every worksheet.

Is there a way I can tell Excel to select the first worksheet
(whatever that may be called) and then go on to the second, and third
-- ignoring the tab names and caring only about the tab position?

Thanks,

Chris

Iterating Through Workbook if Don't Know Sheet Names
 

Hi wldruler

Try something like this. Using the ActiveSheet.UsedRange will mean that
you don't need to know where on the sheet the data is either.

Sub sh()
For Each s In ActiveWorkbook.Sheets
s.Activate
ActiveSheet.UsedRange.CopyPicture _
Appearance:=xlPrinter, Format:=xlPicture
Next
End Sub


"wrldruler" wrote:

I've got an Access database that produces an "Active Project" report.
It creates a new worksheet tab for every project we have running in
our office. I chose to give the worksheet the same name as the
project, thus allowing for easy browsing when a user opens the Excel.

Now I want to create a macro that copies the worksheet over to a
PowerPoint slide. I am using this line of code:

Worksheets("Test_Project").Range("B1:P28").CopyPic ture
Appearance:=xlPrinter, Format:=xlPicture

But notice I had to hard code "Test_Project" as the Worksheet name. I
don't want to hard code the name because our project list is always
changing.

I could use "ActiveSheet.Name", but then I would have to run the macro
on every worksheet.

Is there a way I can tell Excel to select the first worksheet
(whatever that may be called) and then go on to the second, and third
-- ignoring the tab names and caring only about the tab position?

Thanks,

Chris

Iterating Through Workbook if Don't Know Sheet Names
 

Thanks guys. Worked perfect. In case anyone cares, here is the code I
used to export multiple tabs from Excel into PowerPoint.

Sub Export_Excel_MBR_to_PowerPoint()

Dim PPApp As PowerPoint.Application
Dim ppFile As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide
Dim ppShape As PowerPoint.Shape

'Create PowerPoint Object
Set PPApp = CreateObject("PowerPoint.Application")
PPApp.Visible = True

'Open Template
Set ppFile = PPApp.Presentations.Open("G:\emplate.ppt")

Set PPSlide =
ppFile.Slides(PPApp.ActiveWindow.Selection.SlideRa nge.SlideIndex)

'Tracks Slide Index
Dim intI As Integer
intI = 1

'Iterates through each tab in the Excel file
For Each s In ActiveWorkbook.Sheets

'Activate Excel tab
s.Activate

'Copies the contents of the tab as a Picture
ActiveSheet.UsedRange.CopyPicture Appearance:=xlPrinter,
Format:=xlPicture

'Activate PowerPoint slide
PPApp.ActiveWindow.View.GotoSlide (intI)

' Paste the range
PPSlide.Shapes.Paste.Select

'Resizes the picture
With PPApp.ActiveWindow.Selection.ShapeRange
.ScaleWidth 0.84, msoFalse, msoScaleFromTopLeft
.ScaleHeight 0.84, msoFalse, msoScaleFromBottomRight
End With

'Moves the picture to align
With PPApp.ActiveWindow.Selection.ShapeRange
.IncrementLeft 388
.IncrementTop 140
End With

'Add new slides to presentation
Set PPSlide = ppFile.Slides.Add(Index:=ppFile.Slides.Count +
1, Layout:=ppLayoutBlank)

'Tracks Slide Index
intI = intI + 1

Next

'ppFile.Close
'PPApp.Quit

'Clean-Up
Set ppShape = Nothing
Set PPSlide = Nothing
Set ppFile = Nothing
Set PPApp = Nothing

End Sub