copy/paste selected worksheets

  • Thread starter Thread starter mjack003
  • Start date Start date
M

mjack003

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
 
You have another reply at the original thread, but...

this line:

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

Has two paths in it.

Thisworkbook.path refers to the path of the workbook that contains the code.
(If that workbook hasn't been saved, it exists!)

So your code may look like this:

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

So you may just have to get rid of that thisworkbook.path stuff:

newWkbk.SaveAs Filename:="J:\Rec_Share\Customers for Shipping\" _
& "PO# " & myStr & " " & Format(Date, "(mm-dd-yy)") & ".xls", _
FileFormat:=xlWorkbookNormal
 
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
 
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
 
Back
Top