More questions about copying
 

I've got a new challenge for you (or is it just a question) regarding the
script you have provided earlier this week.



This here lets one copy the worksheets and save individually as xls files.
GREAT!


Sub Make_New_Books()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
w.Copy
ActiveWorkbook.SaveAs FileName:=ThisWorkbook.Path & "\" &
w.Name
ActiveWorkbook.Close
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub




I needed to make my copied worksheets so they are saved as csv files. I
replaced the ActiveWorkbook.SaveAs line with the following



ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & w.Name,
FileFormat:=xlCSV, CreateBackup:=False

and it works GREAT!

I now have a request.


In my example I have 10 worksheets that I need saved as csv files. They are
currently named Sheet1 all the way to Sheet10

So with the above changes Sheet1 will save as Sheet.csv
GREAT!

But now we (company) need to start keeping track of things better. The boss
wants a numerical prefix added (8-10 digits long) and two suffixes (Code1
and Code2)
separated by a hyphen.



Each Code will have a list of choices, like this:
Code1
A
B
C
D
E

Code2
A
B
C
D
E

What we need is this:

When I select to save the file (before the files are copied) I need a window
to appear where we will enter the numerical prefix, then with a CONTINUE
button it would give me the option to select the Code1 choices with a Radio
Button, then Code2's choices.

I would like at the end a confirmation window with the option to CONTINUE
and CANCEL.

From my explanation I will give you the following example.
For our first trial of 10 worksheets, we want a numerical prefix of
987654321 Code1 suffix will be E Code2 will be B
Sheet1 and Sheet10, once saved would look like this:


987654321-Sheet1-E-B.csv and 987654321-Sheet1-E-B.csv, respectively.

Is this possible?

Thank You

Mr BT

More questions about copying Can anyone help me with this?
 

"Mr BT" wrote in message
news:eG7xi.65448$fJ5.11277@pd7urf1no...
I've got a new challenge for you (or is it just a question) regarding the
script you have provided earlier this week.



This here lets one copy the worksheets and save individually as xls files.
GREAT!


Sub Make_New_Books()
Dim w As Worksheet
Application.ScreenUpdating = False
Application.DisplayAlerts = False
For Each w In ActiveWorkbook.Worksheets
w.Copy
ActiveWorkbook.SaveAs FileName:=ThisWorkbook.Path & "\" &
w.Name
ActiveWorkbook.Close
Next w
Application.DisplayAlerts = True
Application.ScreenUpdating = True
End Sub




I needed to make my copied worksheets so they are saved as csv files. I
replaced the ActiveWorkbook.SaveAs line with the following



ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & w.Name,
FileFormat:=xlCSV, CreateBackup:=False

and it works GREAT!

I now have a request.


In my example I have 10 worksheets that I need saved as csv files. They
are
currently named Sheet1 all the way to Sheet10

So with the above changes Sheet1 will save as Sheet.csv
GREAT!

But now we (company) need to start keeping track of things better. The
boss
wants a numerical prefix added (8-10 digits long) and two suffixes (Code1
and Code2)
separated by a hyphen.



Each Code will have a list of choices, like this:
Code1
A
B
C
D
E

Code2
A
B
C
D
E

What we need is this:

When I select to save the file (before the files are copied) I need a
window
to appear where we will enter the numerical prefix, then with a CONTINUE
button it would give me the option to select the Code1 choices with a
Radio
Button, then Code2's choices.

I would like at the end a confirmation window with the option to CONTINUE
and CANCEL.

From my explanation I will give you the following example.
For our first trial of 10 worksheets, we want a numerical prefix of
987654321 Code1 suffix will be E Code2 will be B
Sheet1 and Sheet10, once saved would look like this:


987654321-Sheet1-E-B.csv and 987654321-Sheet1-E-B.csv, respectively.

Is this possible?

Thank You

Mr BT