Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Save Excel Sheet in a new location with name as two cell reference

0

Hi, So i have this code that copies a sheet and then moves it to another workbook make some formatting changes and then save it's name by the values of Cell B1 and J1.

The error i am facing is that there is some kind of sharing violation.

Could somone have a look ?  is there a shorter way to do do this ? Thanks in advance.

Sub CreateBill_Print()
'
' CreateBill_Print Macro
'
'
    Columns("A:M").Select
    Selection.Copy
    Sheets.Add After:=ActiveSheet
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    ActiveSheet.Select
    Application.CutCopyMode = False
    ActiveSheet.Move
    Rows("4:4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.RowHeight = 13
    Selection.End(xlUp).Select
    Range("B6").Select
    ChDir _
        "/Users/asif/Library/Mobile Documents/com~apple~CloudDocs/Work/LNL/1/Cash Invoice/"
    ActiveWorkbook.SaveAs FileName:= _
        "/Users/asif/Library/Mobile Documents/com~apple~CloudDocs/Work/LNL/1/Cash Invoice/" & Range("B1").Value & "" & Range("J1").Value & "" _
        , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
    ActiveWindow.Close
    Range("B4").Select
End Sub
excel
MS Office Version: 365
posted by: asif_187 (rep: 6) on Mon Oct 25, 2021 at 11:33 am
Answer
Discuss

Answers

0

Asif

Looks like you used Excel's Record Macro feature so your code has several lines which aren't really needed. You don't need to select sheets after copying/creating or use ChDir (change directory) before saving a file with a full path specified (so that line has gone, for example)

If the code below is in a standard Module (not as a sheet macro), it should work when run with your invoice sheet active- but be aware it could run on any active sheet!

I haven't tried to rewrite your code but editted it slightly. I've highlighted some bits in bold (for the Message box added at the end and where a change has saved a line):

Sub CreateBill_Print()
'
' CreateBill_Print Macro
'
Columns("A:M").Copy
Sheets.Add After:=ActiveSheet
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
ActiveSheet.Move
Rows(4).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.RowHeight = 13
Range("B6").Select
ActiveWorkbook.SaveAs Filename:= _
    "/Users/asif/Library/Mobile Documents/com~apple~CloudDocs/Work/LNL/1/Cash Invoice/" & Range("B1").Value & "" & Range("J1").Value & "" _
    , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWindow.Close
Range("B4").Select
MsgBox "Invoice saved as "  & Range("B1").Value & "" & Range("J1").Value & ".xlsx"

End Sub
Hope this works for you. 
posted by: John_Ru (rep: 6142) on Tue Oct 26, 2021 at 8:40 am
Discuss

Discussion

Hi, Thanks works good.    Right now when I am opening the saved file I am getting an alert saying that the format and the extension of the file don't match. the icon of the file is also like a blank paper (with no excel marking)   Is it possible to save it in excel format ? I use Excel 365 for mac. 
asif_187 (rep: 6) Oct 26, '21 at 10:07 am
Hi,
It worked well the first time. Saved the file without giving it a format. After that it is not working. its showing sharing violation error at line:
ActiveWorkbook.SaveAs Filename:= _
    "/Users/asif/Library/Mobile Documents/com~apple~CloudDocs/Work/LNL/1/Cash Invoice/" & Range("B1").Value & "" & Range("J1").Value & "" _ 
    , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
asif_187 (rep: 6) Oct 26, '21 at 10:29 am
Asif, I'm not familiar with Excel on a Mac and I won't get back to my PC for a few hours. From memory, suggest you either remove  the optional argument   FileFormat:=xlOpenXMLWorkbook  or replace the last part (format) with   xlWorkbookDefault
John_Ru (rep: 6142) Oct 26, '21 at 10:36 am
Oops, just saw your last message (after I posted mine) 
John_Ru (rep: 6142) Oct 26, '21 at 10:38 am
Asif.

Tried again on my PC and the macro saves a file which can be opened.

Suggest you change the line to 
ActiveWorkbook.SaveAs Filename:= _
    "/Users/asif/Library/Mobile Documents/com~apple~CloudDocs/Work/LNL/1/Cash Invoice/" & Range("B1").Value & "" & Range("J1").Value & ".xlsx" _
    , FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
noting the ".xlsx" bit in bold.
John_Ru (rep: 6142) Oct 26, '21 at 12:51 pm
Persists
asif_187 (rep: 6) Oct 26, '21 at 9:49 pm
Asif, I think this is a probably Mac issue not an Excel one e.g. see Microsoft note How to get past sharing violation on Mac with Office 2019 or check on YouTube. 
John_Ru (rep: 6142) Oct 27, '21 at 1:37 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login