Defining first part of file name

[biertje]

I have a macro that creates a copy of 11 sheets within a workbook and creates a new workbook for each of the 11 sheets. The macro then saves each workbook with a specific name e.g. Bob, John etc etc.
What i'd like is at the start for a text box to come up so that the user can define the first part of the name so that each new workbook isn't just called Bob and John. The user can say I want it to be 11/10/2017 Work for Bob. Is there a way to define this?

I have the following code to create and save a copy to the desktop:

Sub CopyBob()'
' Copy Macro
' copies 1 workers sheet and opens it in a new sheet
'


'
    Sheets("Bob").Select
    Sheets("Bob").Copy
    Application.WindowState = xlMaximized
    Range("B4").Select
        Columns("AV:BA").Select
    Selection.Delete Shift:=xlToLeft
ActiveWorkbook.SaveAs "h:\desktop\Bob" 
ActiveWorkbook.Close False
End Sub

I assume that you have to define something at the beginning and then put it in the file name where "Bob" is.

Thanks for any help.

 

[Scott T]

You can add an inputbox and then join that with the name. something like

fname = InputBox("enter filename include name")
ActiveWorkbook.SaveAs "h:\desktop\"& fname

Edit: you probably should also test fname to make sure it is a valid file name.

 

[Joe4]

If you are going to allow the user to enter in whatever name they want, you will want/need to add error handling in, in case they enter invalid characters in it.
For example, a file name cannot contain / characters (so if you wanted a date, you would need to enter it differently, maybe like 10-11-2017).

 

[biertje]

Thanks Joe. Good point I'll put something in.

Ah sorry I don't think I articulated this well. So I want each of the 11 new workbooks to have the same prefix e.g. the date or whatever. I want this to be defined once at the start of the macro, by user input. I have tried defining a string, but it doesn't seem to remember it when it opens up a new workbook.

Sub TLB_CT_Prefix()
' saves the value of the users input in A1 and saves as a string
Dim MyVal As String


MyVal = Sheets("Macro").Range("A1").Value


Debug.Print MyVal


End Sub

 

[Scott T]

Is the code to open the new workbooks in the same sub?

In order for the variable to available to multiple subs you must decare it outside of the subs

Public myval as string

Sub subname()

code here

end sub

Sub sub3()

code here
end sub

<code style='margin: 0px; padding: 0px; border: 0px currentColor; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; font-size: 13px; font-style: inherit; font-variant: inherit; font-weight: inherit; vertical-align: baseline; white-space: inherit; font-stretch: inherit; background-color: rgb(239, 240, 241);'></code>

 

[biertje]

Thanks, sorry I am not very hot with vba yet. no I have 1 main sub that runs through all the other subs I have made. So it goes something like:

Public myval As String
Sub GenerateCT()
TLB_CT_Prefix
copy_Bob
end sub

' below are the subs that I have defined. 
Sub TLB_CT_Prefix()
' saves the value of the users input
Dim myval As String


myval = Sheets("Macro").Range("A1").Value


Debug.Print myval


End Sub
CopyBob()'
' Copy Macro
' copies 1 workers sheet and opens it in a new sheet
'
 
 
'
    Sheets("Bob").Select
    Sheets("Bob").Copy
    Application.WindowState = xlMaximized
    Range("B4").Select
        Columns("AV:BA").Select
    Selection.Delete Shift:=xlToLeft
ActiveWorkbook.SaveAs "h:\desktop\Bob"
ActiveWorkbook.Close False
End Sub

but If I put the string as public sitting above when I hit my form control it just ignores it. Sorry I am being slow.

 

[Scott T]

The only thing you are doing with myval is to print it to the Immediate window.

Debug.Print myval

If you want myval to be part of the file name you need to join it with the file name in your code something like

ActiveWorkbook.SaveAs "h:\desktop\" & myval & "Bob"