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
684
Office Version
  1. 365
Platform
  1. 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:

Excel Facts

When did Power Query debut in Excel?
Although it was an add-in in Excel 2010 & Excel 2013, Power Query became a part of Excel in 2016, in Data, Get & Transform Data.

Kamolga

Well-known Member
Joined
Jan 28, 2015
Messages
1,176
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
36,970
Office Version
  1. 2016
Platform
  1. 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
684
Office Version
  1. 365
Platform
  1. 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,176
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,176
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,129,373
Messages
5,635,884
Members
416,886
Latest member
coreyalaurence37

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top