Finding Next Available Sheet?

[mlopes1]

I have a macro written that selects specified sheets one at a time, takes a picture of them (using .copypicture), creates a new workbook and then pastes the pictures in. Most of the time users will only want to take one picture so it will go into sheet one with no problems. But what if they want multiple sheets to be copied over? Is there a way for the macro to check what the last sheet was that had a picture pasted into it and then 'add one' to that value for the next paste. Here is my code:

newBookName = InputBox("What would you like to name the file?")

Set newbook = Workbooks.Add
With newbook
.title = newBookName
.SaveAs FileName:=(newBookName)
End With
activateCurrent 'activates the source workbook

If Cells(1, 2) = "x" Then
Sheets("Proforma").Visible = True
Worksheets("Proforma").Activate
Worskheets("Proforma").Range("a1:k150").CopyPicture xlScreen, xlBitmap
activateNew 'activates the new workbook
Worksheets("Sheet1").Paste _
Destination:=Worksheets("Sheet1").Range("A1")


Is there a way for the Destination to be the last sheet + 1 ? I hope this was clear. thanks in advance,


Mark

 

[ChrisM]

Yes, you can use the sheets.count or sheets(index) properties.

for example
for i = 1 to numyoursheets
paste into sheets(i)
next i

It seems as if you're pasting your data into the new workbook's default sheets. What happens if your default is 3 sheets and you need to paste in 4 or 5 sheets? I prefer to add a new sheet and name it like this:

ActiveWorkbook.Sheets.Add after:=Worksheets(Worksheets.count)
ActiveSheet.Name = "name"