Think this should be straightforward...

[phunktonic]

Hello,

Is there a better way for me to ask for the help I need? I am new but want to learn!!

So, here is what I would like to do:

1. My first task was to name the Blank Example.xls file from two different cells data. I have done this with this code

Option Explicit

[VBA]Sub SaveAsExample()

Dim FName As String
Dim FPath As String

FPath = "C:\Documents\Quotes TEST"
FName = Sheets("blank quote").Range("C11") & " - " & Range("h17").Text
ThisWorkbook.SaveAs Filename:=FPath & "\" & FName

End Sub[/VBA]


2. From Blank Example.xls, summary sheet, copy A2:I2 to TurnoverTEST, next available blank row. And if this can go in to the appropiate month, even better.

3. Take a copy of Blank Example.xls, blank quote sheet, A1:J76. Paste it in to a new work book. Set the print area to these cells. And lastly,name it as in point 1 above, but with 'client' listed between the name and date, saving it in the same folder.

[Kenneth Hobs]

Welcome to the forum!

By asking more than one question, you limit your expected responses as few will take that much trouble to do your whole project. Many forums have strict rules about asking one question per thread. When you have a 2nd question that relates to the first question a bit, just add a link to it.

When posting code, press the VBA button and then post between the tags.

For your first question, you have two things that you need to address. (1) Does fPath exist? (2) Is the filename valid? This means that it must follow Windows naming conventions. Here is a stab at your question item (1).

Check like this in a Module:
[vba]Sub SaveAsExample()
Dim FName As String
Dim FPath As String

FPath = "C:\Documents\Quotes TEST"
If Dir(FPath, vbDirectory) = "" Then
MsgBox FPath & " does not exist.", vbCritical, "Ending Macro"
Exit Sub
End If
FName = Sheets("blank quote").Range("C11") & " - " & Sheets("blank quote").Range("h17").Text & ".xls"
Debug.Print Sheets("blank quote").Range("C11"), "C11"
Debug.Print Sheets("blank quote").Range("h17").Text, "H17"
Debug.Print FPath & "\" & FName
ThisWorkbook.SaveAs Filename:=FPath & "\" & FName
End Sub[/vba]
The results of debug.print is posted to VBE's Immediate window. Note that appended an "*.xls" for the saveas filename.

Your question (2) is easy to solve if I knew which folder the TurnOverTest.xls would be in if not Thisworkbook.Path.

[phunktonic]

Kenneth,

Thank you so much for your reply.

The code you suggested has worked a treat so far. To answer your Q's (1) Does fPath exist? YES(2) Is the filename valid? YES

Question (2) - From Blank Example.xls, summary sheet, copy A2:I2 to TurnoverTEST, next available blank row. And if this can go in to the appropiate month, even better.

I recorded the macro below:

[VBA]Sub SummaryToTurnover()
'
'
Sheets("Summary").Select
Range("A2:I2").Select
Application.CutCopyMode = False
Selection.Copy
Windows("AVCTurnoverTEST.xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("B11").Select
Windows("Blank Example.xls").Activate
Sheets("Blank Quote").Select
End Sub
[/VBA]

TurnOverTest.xls is located in C:\Documents\Useful Info\Reports

[Kenneth Hobs]

What determines the sheet (month) to put the data into?

I would recommend that you change your Start and Finish date columns to dates. You can set a custom numeric format to show the date format as the hard coded text value used in your Blank Example.xls. If these were dates, you could use them to determine your sheets to copy to I suspect.

Your month sheets in TurnoverTest.xls has an extra column showing row-4 numbers. Your production version probably does not have that. If it does, that is fine, I just need to know.

[phunktonic]

I see your point, this is what happens when you have to use someones old crazy system! (old boss, I'm the boss now :-))

So, if I now reformat the cells with date to appear: 12 March 2009, then each quote's summary can be placed in the correct month sheet? This is better as the filenames now appear as: 'THE BIG COMPANY - 12 March 2009'

Could you clarify "Your month sheets in TurnoverTest.xls has an extra column showing row-4 numbers." I've tried to interpret this with no success.

Once again help is appreciated.

[Kenneth Hobs]

The column is column A. In your Summary Sheet, Column A is the Company Name. It is Column B in your TurnoverTest.xls month sheets.

Your custom numeric format would be: "ddd mm-dd-yy". Enter the date 12/03/09 as 12/03/2009.

Your Blank Quote sheet dates are wrong. 12/03/09 is a Thursday, not a Tuesday. In another thread, I would suggest that you ask for help to convert the text string dates into real dates and use the custom numeric format that I suggested or another.