Finding Next Available Sheet?

mlopes1

Board Regular
Joined
Jul 14, 2002
Messages
114
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.

ChrisM

Well-known Member
Joined
Jun 11, 2002
Messages
2,129
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"
 

Forum statistics

Threads
1,143,923
Messages
5,721,557
Members
422,370
Latest member
A Nonomus

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top