Copy/Paste macro...with a few twists

[mjack003]

Hi,

I needed to create a macro that would copy/paste all the selected rows
into a new workbook which the macro creates. Before creating the
workbook however I need it to ask the user for a six digit number,
which it then takes and names the workbook (eg. PO######) and attaches
the current date on the end. I'm still a newb to VBA so any help is
appreciated.

Best Regards,
Mjack003

 

[Dave Peterson]

Something like this might get you started:

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
Set newWkbk = Workbooks.Add(1)

newWkbk.SaveAs Filename:=ThisWorkbook.Path & "\" _
& "PO" & myStr & "_" & Format(Date, "yyyymmdd") & ".xls", _
FileFormat:=xlWorkbookNormal

End Sub

I didn't know where (drive/folder) to save to, though.

 

[BrianB]

'-------------------------------------------
Sub test()
Dim MyNewname As String
MyNewname = InputBox("New name please")
MyNewname = MyNewname & " " & Format(Now, "dd-mm-yy")
ActiveWorkbook.SaveAs FileName:=MyNewname & ".xls"
End Sub
'-------------------------------------------

 

[mjack003]

Thanks Brian. It works like a charm. I've played with it a bit but
can't figure out where to enter the file path. After the "=", befor
the colon? Any help is appreciated.

Best Regards,
Mjac

 

[Dave Peterson]

This line:

newWkbk.SaveAs Filename:=ThisWorkbook.Path & "\" _
& "PO" & myStr & "_" & Format(Date, "yyyymmdd") & ".xls", _
FileFormat:=xlWorkbookNormal

Says to save it in the same folder that contains the workbook with the code.

If you want a different folder, you can change it here:

newWkbk.SaveAs Filename:="C:\yourfolderhere\andhere\and & "\" _
& "PO" & myStr & "_" & Format(Date, "yyyymmdd") & ".xls", _
FileFormat:=xlWorkbookNormal

(Brian???)