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
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