Hi,

I have this function that will create and save the workbook under the
name input by the user. Now, I need to enter a copy/paste function
somewhere in there that will copy the the selected worksheets in the
master workbook and paste them into the newly created workbook. This
might be simple but I can't figure this one out. Thanks in advance.
What I have is this:

Private Sub CommandButton1_Click()

Dim myStr As String
Dim newWkbk As Workbook

Do
myStr = InputBox(prompt:="Enter PO# :")
If Trim(myStr) = "" Then
Exit Sub ' or what??
End If

If IsNumeric(myStr) Then
If Val(myStr) = CLng(myStr) _
And Val(myStr) < 999999 Then
myStr = Format(Val(myStr), "000000")
Exit Do
End If
End If
Loop

Set newWkbk = Workbooks.Add(1)

newWkbk.SaveAs Filename:=ThisWorkbook.Path & "J:\Rec_Share\Customers
for Shipping\" _
& "PO# " & myStr & " " & Format(Date, "(mm-dd-yy)") & ".xls", _
FileFormat:=xlWorkbookNormal


End Sub

Best Regards,
Mjack


---
Message posted from http://www.ExcelForum.com/

Let me see if I can explain this a little better. I connected th
function that you have given me to a command button so that when it i
clicked in the master workbook it will prompt for a six digit shippin
number...take that number and create a workbook with the date and tha
number. That was the hard part for me. But now I don't know where t
put the line to copy the highlighted worksheets in the master book an
paste them to the newly created shipping workbook, or if it is eve
possible due to the variable name. I do hundreds of these a day s
this will be a great help if I don't have to manually drag and drop.

Thanks,
Mjac

--
Message posted from http://www.ExcelForum.com

Are "highlighted" worksheets the ones that are selected (grouped)?

if yes:

Option Explicit
Sub testme()

Dim myStr As String
Dim newWkbk As Workbook

Do
myStr = InputBox(prompt:="Enter a 6 digit number")
If Trim(myStr) = "" Then
Exit Sub ' or what??
End If

If IsNumeric(myStr) Then
If Val(myStr) = CLng(myStr) _
And Val(myStr) < 999999 Then
myStr = Format(Val(myStr), "000000")
Exit Do
End If
End If
Loop

'create the new workbook code here
ActiveWindow.SelectedSheets.Copy 'copies to a new workbook

Set newWkbk = ActiveWorkbook

newWkbk.SaveAs Filename:="J:\Rec_Share\Customers for Shipping\" _
& "PO# " & myStr & " " & Format(Date, "(mm-dd-yy)") & ".xls", _
FileFormat:=xlWorkbookNormal

newWkbk.Close savechanges:=False

End Sub



"mjack003 <" wrote:

Let me see if I can explain this a little better. I connected the
function that you have given me to a command button so that when it is
clicked in the master workbook it will prompt for a six digit shipping
number...take that number and create a workbook with the date and that
number. That was the hard part for me. But now I don't know where to
put the line to copy the highlighted worksheets in the master book and
paste them to the newly created shipping workbook, or if it is even
possible due to the variable name. I do hundreds of these a day so
this will be a great help if I don't have to manually drag and drop.

Thanks,
Mjack

---
Message posted from http://www.ExcelForum.com/

--

Dave Peterson

Thanks Dave! It works great, just what I need. Thank you for your
patience.


---
Message posted from http://www.ExcelForum.com/