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

VBE313

Well-known Member
Joined
Mar 22, 2019
Messages
508
Office Version
365
Platform
Windows
Code:
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
 
Last edited by a moderator:

Some videos you may like

Excel Facts

How to change case of text in Excel?
Use =UPPER() for upper case, =LOWER() for lower case, and =PROPER() for proper case. PROPER won't capitalize second c in Mccartney

Kamolga

Well-known Member
Joined
Jan 28, 2015
Messages
1,172
Code:
[LEFT][COLOR=#333333][FONT=Verdana]path1 = Range("I7").[/FONT][/COLOR][U][B][I][FONT=Verdana]Value[/FONT][/I][/B][/U][/LEFT]
and
Code:
[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
Code:
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
Code:
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.
 
Last edited:

Rick Rothstein

MrExcel MVP
Joined
Apr 18, 2011
Messages
35,960
Office Version
2010
Platform
Windows
Code:
[LEFT][COLOR=#333333][FONT=Verdana]path1 = Range("I7").[/FONT][/COLOR][U][B][I][FONT=Verdana]Value[/FONT][/I][/B][/U][/LEFT]
and
Code:
[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
Code:
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

Well-known Member
Joined
Mar 22, 2019
Messages
508
Office Version
365
Platform
Windows
Thank you,

Code:
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?
 
Last edited by a moderator:

Kamolga

Well-known Member
Joined
Jan 28, 2015
Messages
1,172
Hi,


Code:
[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
 
Last edited:

Kamolga

Well-known Member
Joined
Jan 28, 2015
Messages
1,172
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
Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,100,142
Messages
5,472,754
Members
406,835
Latest member
steve43040

This Week's Hot Topics

Top