create new sheet

HI
Hope i explain this correct.

I have a macro that will look in a folder for a name based on cell info in(E8) ie name of smith. when it fines the file it opens up . all works ok.


But what i want to do is , if the file name dont excist i want it open a blank template called "Blank" and save the file name based on the name in (E8)

this is the code i use at the moment

Workbooks.Open Filename:="S:\Administration\Administration\000 Taxi Requests 000\" & Range("e8") & ".xlsm"

Sub T()
Set wb1 = ActiveWorkbook
On Error Resume Next
Workbooks.Open Filename:="S:\Administration\Administration\000 Taxi Requests 000\" & Range("e8") & ".xlsm"
Set wb = Workbooks.Add
MsgBox wb1.Sheets("Sheet1").Range("e8")
wb.SaveAs Filename:="S:\Administration\Administration\000 Taxi Requests 000\" & wb1.Sheets("Sheet1").Range("e8"), FileFormat:=52
End Sub

HI

Thank you for the code. but it only opens a blank page.

I want it to open a blank template in Workbooks.Open Filename:="S:\Administration\Administration\000 Taxi Requests 000\ 000 balnk 000

HI

This is what i have used, but if it dont find the file name it still eorrors out . i want it to open the next file on error

Sub Taxi2()
    Workbooks.Open Filename:="S:\Administration\Administration\000 Taxi Requests 000\" & Range("e8") & ".xlsm"
    On Error Resume Next


Workbooks.Open Filename:="S:\Administration\Administration\000 Taxi Requests 000\000 Blank 000.xlsm"

you could try but your going to need the full file name to have to work proper.

Sub Test()
On Error GoTo Error1
Workbooks.Open Filename:="S:\Administration\Administration\000 Taxi Requests 000\" & Range("e8") & ".xlsm"
Exit Sub
Error1:
Set wb1 = ActiveWorkbook
Set wb = Workbooks.Open (Filename:="S:\Administration\Administration\000 Taxi Requests 000\ 000 balnk 000")
wb.SaveAs Filename:="S:\Administration\Administration\000 Taxi Requests 000\" & wb1.Sheets("Sheet1").Range("e8"), FileFormat:=52
End Sub

Hi

Thank you the code works but it fails to save the file it gets stuck on that line of code

What is the error there ???

I get a box pop up saying

Run time error '9'
subscript out of range.

then it higlights the line

wb.SaveAs Filename:="S:\Administration\Administration\000 Taxi Requests 000\" & wb1.Sheets("Sheet1").Range("e8"), FileFormat:=52

You have changed my code at workbook add method to open method try this again !! Make sure E8 contains some valid name value so that file can be saved without error.

Sub Test()
On Error GoTo Error1
Workbooks.Open Filename:="S:\Administration\Administration\000 Taxi Requests 000\" & Range("e8") & ".xlsm"
Exit Sub
Error1:
Set wb1 = ActiveWorkbook
Set wb = Workbooks.Add
wb.SaveAs Filename:="S:\Administration\Administration\000 Taxi Requests 000\" & wb1.Sheets("Sheet1").Range("e8"), FileFormat:=52
End Sub

HI its ok i found the problem.

it's sheet 4 and not sheet1.

Thnk you for your help

Haha M going to Suicide !! glad to help

Oh sorry a small problem

if the file dont excist it creats a new one which is what i want but

if the file dose excist it opens it up and also creates a new one. i i have 2 folder opens with the same name ??

Here is the code im using

Sub Test()
On Error GoTo Error1
Workbooks.Open Filename:="S:\Administration\Administration\000 Taxi Requests 000\" & Range("e8") & ".xlsm"

Error1:
Set wb1 = ActiveWorkbook
Set wb = Workbooks.Open(Filename:="S:\Administration\Administration\000 Taxi Requests 000\Blank.xlsm")
Sheets(1).Range("d12").Value = ThisWorkbook.Sheets(1).Range("b8").Value & " " & ThisWorkbook.Sheets(1).Range("e8").Value
wb.SaveAs Filename:="S:\Administration\Administration\000 Taxi Requests 000\" & wb1.Sheets("Sheet4").Range("e8") & ".xlsm"

Sub Test()
On Error GoTo Error1
Workbooks.Open Filename:="S:\Administration\Administration\000 Taxi Requests 000\" & Range("e8") & ".xlsm"
exit sub
Error1:
Set wb1 = ActiveWorkbook
Set wb = Workbooks.Open(Filename:="S:\Administration\Administration\000 Taxi Requests 000\Blank.xlsm")
Sheets(1).Range("d12").Value = ThisWorkbook.Sheets(1).Range("b8").Value & " " & ThisWorkbook.Sheets(1).Range("e8").Value
wb.SaveAs Filename:="S:\Administration\Administration\000 Taxi Requests 000\" & wb1.Sheets("Sheet4").Range("e8") & ".xlsm"

HI

Thank you

After using your code it dont copy the data from the sheet from my code. here is the full code. when it opens the new page its just blank with no data

Sub Test()
On Error GoTo Error1
Workbooks.Open Filename:="S:\Administration\Administration\000 Taxi Requests 000\" & Range("e8") & ".xlsm"
Exit Sub
Error1:
Set wb1 = ActiveWorkbook
Set wb = Workbooks.Open(Filename:="S:\Administration\Administration\000 Taxi Requests 000\Blank.xlsm")
Sheets(1).Range("d12").Value = ThisWorkbook.Sheets(1).Range("b8").Value & " " & ThisWorkbook.Sheets(1).Range("e8").Value
wb.SaveAs Filename:="S:\Administration\Administration\000 Taxi Requests 000\" & wb1.Sheets("Sheet4").Range("e8") & ".xlsm"



    Sheets(1).Range("d13").Value = ThisWorkbook.Sheets(1).Range("j8").Value
    Sheets(1).Range("d14").Value = ThisWorkbook.Sheets(1).Range("b9").Value & " " & ThisWorkbook.Sheets(1).Range("e9").Value
    Sheets(1).Range("d15").Value = ThisWorkbook.Sheets(1).Range("j9").Value
    Sheets(1).Range("d16").Value = ThisWorkbook.Sheets(1).Range("b10").Value & " " & ThisWorkbook.Sheets(1).Range("e10").Value
    Sheets(1).Range("d17").Value = ThisWorkbook.Sheets(1).Range("j10").Value
    Sheets(1).Range("d18").Value = ThisWorkbook.Sheets(1).Range("b11").Value & " " & ThisWorkbook.Sheets(1).Range("e11").Value
    Sheets(1).Range("d19").Value = ThisWorkbook.Sheets(1).Range("j11").Value
    Sheets(1).Range("d20").Value = ThisWorkbook.Sheets(1).Range("b12").Value & " " & ThisWorkbook.Sheets(1).Range("e12").Value
    Sheets(1).Range("d21").Value = ThisWorkbook.Sheets(1).Range("j12").Value
    Sheets(1).Range("r4").Value = ThisWorkbook.Sheets(1).Range("p8").Value & " " & ThisWorkbook.Sheets(1).Range("k8").Value
    Sheets(1).Range("o11").Value = ThisWorkbook.Sheets(1).Range("b33").Value
    Sheets(1).Range("m12").Value = ThisWorkbook.Sheets(1).Range("e33").Value
    Sheets(1).Range("ab11").Value = ThisWorkbook.Sheets(1).Range("b35").Value

End Sub