What is wrong with my code? The line that is not responding is underlined and bolded.

[VBE313]

Dim path1 As String
    Dim Filename1 As String
    
    Application.ScreenUpdating = False
    path1 = Range("I7").Text
    Range("A1:f40").Select
    Selection.Copy
    Sheets("Laser").Select
    Sheets.Add After:=ActiveSheet
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveSheet.Select
    ActiveSheet.Move
    Filename1 = Range("B4").Text
    Application.DisplayAlerts = False
[U][B]    ActiveWorkbook.SaveAs Filename:=path1 & Filename1 & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False[/B][/U]
    Application.DisplayAlerts = True
    ActiveWorkbook.Close
    Application.ScreenUpdating = True
    Sheets("Laser").Select
   End Sub

 

[Kamolga]

[LEFT][COLOR=#333333][FONT=Verdana]path1 = Range("I7").[/FONT][/COLOR][U][B][I][FONT=Verdana]Value[/FONT][/I][/B][/U][/LEFT]

and

[LEFT][COLOR=#333333][FONT=Verdana]Filename1 = Range("B4").[B][I][U]Value[/U][/I][/B][/FONT][/COLOR][/LEFT]

and you should specify the sheet for both values, so something line

Dim sh as worksheet: set sh=activesheet
 [FONT=Verdana][COLOR=#333333]path1 = sh.Range("I7").[/COLOR][/FONT][U][B][I][FONT=Verdana]Value[/FONT][/I][/B][/U]

. It seems you create a sheet before setting filename1, since you don't specify which sheet for B4, I guess filename1 is empty. there is a watch window (view menu). If you

debug.print filename1

right after saying what it should be, you will see what its value is in that window when you run the macro
If error, rather the path in I7 is wrong/incomplete or you already have a file with that name in that folder.

 

[Rick Rothstein]

[LEFT][COLOR=#333333][FONT=Verdana]path1 = Range("I7").[/FONT][/COLOR][U][B][I][FONT=Verdana]Value[/FONT][/I][/B][/U][/LEFT]

and

[LEFT][COLOR=#333333][FONT=Verdana]Filename1 = Range("B4").[B][I][U]Value[/U][/I][/B][/FONT][/COLOR][/LEFT]

and you should specify the sheet. It seems you create a sheet before setting filename1, since you don't specify which sheet for B4, I guess filename1 is empty. there is a watch window (view menu). If you

debug.print filename1

right after saying what it should be, you will see what its value in that window wen you run the macro
If error, rather the path in I7 is wrong/incomplete or you already have a file with that name in that folder.

Just to follow up on Kamolga response... check to make sure that both your path has a backslash after it (that is the most common cause of problems when concatenating path-filename text strings together.

 

[VBE313]

Thank you,

Sub test()
Dim path1 As String
Dim Filename1 As String
 Dim sh As Worksheet
Set sh = ActiveSheet
Application.ScreenUpdating = False
path1 = sh.Range("I9").Value
Range("A1:f40").Select
Selection.Copy
Sheets("Laser").Select
Sheets.Add After:=ActiveSheet
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Select
ActiveSheet.Move
Filename1 = sh.Range("B4").Value
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=path1 & Filename1 & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
Application.DisplayAlerts = True
ActiveWorkbook.Close
Application.ScreenUpdating = True
Sheets("Laser").Select
End Sub

what do I need to edit now? where does debug.print go?

 

[Kamolga]

Hi,

[LEFT][COLOR=#333333][FONT=monospace]Sub test()
Dim path1 As String
Dim Filename1 As String
 Dim sh As Worksheet
Set sh = ActiveSheet
Application.ScreenUpdating = False
path1 = sh.Range("I9").Value
Range("A1:f40").Select
Selection.Copy
Sheets("Laser").Select
Sheets.Add After:=ActiveSheet
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Select
ActiveSheet.Move
Filename1 = sh.Range("B4").Value
Application.DisplayAlerts = False[/FONT][/COLOR][/LEFT]
[COLOR=#ff0000][LEFT]
Debug.print path1 & Filename1 & ".xlsx"[/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=monospace]
[/FONT][/COLOR][/LEFT]
[COLOR=#006400][LEFT]'ActiveWorkbook.SaveAs Filename:=path1 & Filename1 & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
'Application.DisplayAlerts = True
'ActiveWorkbook.Close
'Application.ScreenUpdating = True
'Sheets("Laser").Select[/LEFT]
[/COLOR][LEFT][COLOR=#333333][FONT=monospace]
End Sub[/FONT][/COLOR][/LEFT]

would show you what Excel tries to save as and might give you a hint on why it can not. 'Locals Windows' should also show you wat values are associated to each variable

 

[Kamolga]

I realise tat te extension is not necessary when using file format. In addition, by stopping display alert, you don't get the message about macro-enabled to save the macro. This worked for me

Dim path1 As String: path1 = "[COLOR=#0000cd]C:\New transfert\Excel\Bureau\[/COLOR]"
Dim Filename1 As String: Filename1 = [COLOR=#0000cd]"test[/COLOR]"
 [I][B]ActiveWorkbook.SaveAs Filename:=path1 & Filename1, FileFormat:=xlOpenXMLWorkbookMacroEnabled[/B][/I]

so using last line of code and having the blue in your cells (with \ at the end of the path I mean) should work