How do I save a WorkBook using only select cells macro

**jeremiasapaz** · 04-27-2017, 04:13 AM

Hey,

I need to save a Workbook using only selected sheets....

I have this at the moment which is working, but it is saving the whole workbook, and I need it to work with only the range "A1:K40"

Sub SaveInvWithNewName()
    Dim NewInv As Variant
    ActiveSheet.Copy
    NewInv = "C:\Invoices\LeeValley-Inv" & Range("F2").Value & ".xlsx"
    ActiveWorkbook.SaveAs NewInv
    ActiveWorkbook.Close
    MsgBox "File Saved to C:\Invoices"
    NextInvoice
End Sub

I tried this but didnt work - any ideas?

Sub SaveInvWithNewName()
    Dim NewInv As Variant
    ActiveSheet.Copy
    NewInv = "C:\Invoices\LeeValley-Inv" & Range("F2").Value & ".xlsx"
    ActiveWorkbook.Range("A1:K40").SaveAs NewInv
    ActiveWorkbook.Close
    MsgBox "File Saved to C:\Invoices"
    NextInvoice
End Sub

**Sintek** · 04-27-2017, 04:24 AM

Hi jeremiasapaz

Welcome to the forum
In future please view the forum rules before posting. Your post does not comply with rule #3.

3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the [#] button at the top of the post window.

Are you wanting to have only Range("A1:K40").copied to a new workbook and save that workbook?

**Sintek** · 04-27-2017, 04:48 AM

Give this a try...

Option Explicit

Sub SaveInvWithNewName()
    Dim NewInv As Variant
    Dim NewBook As Workbook
    NewInv = "C:\Invoices\LeeValley-Inv" & "-" & ActiveSheet.Range("F2").Value & ".xlsx"
    ActiveSheet.Range("A1:K40").Copy
    Set NewBook = Workbooks.Add
    NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial (xlPasteValues)
    NewBook.SaveAs Filename:=NewInv
    MsgBox "File Saved to C:\Invoices"
End Sub

**jeremiasapaz** · 04-27-2017, 05:21 AM

Originally Posted by sintek

Give this a try...

Option Explicit

Sub SaveInvWithNewName()
    Dim NewInv As Variant
    Dim NewBook As Workbook
    NewInv = "C:\Invoices\LeeValley-Inv" & "-" & ActiveSheet.Range("F2").Value & ".xlsx"
    ActiveSheet.Range("A1:K40").Copy
    Set NewBook = Workbooks.Add
    NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial (xlPasteValues)
    NewBook.SaveAs Filename:=NewInv
    MsgBox "File Saved to C:\Invoices"
End Sub

I am receiving an error on this line

NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial (xlPasteValues)

**Sintek** · 04-27-2017, 06:05 AM

Originally Posted by Pete_UK
To attach a file, click on Go Advanced (below the Edit Window) while you are composing a reply, then scroll down to and click on Manage Attachments and the Upload window will open. Click on Browse and navigate to (and double-click) the file icon that you want to attach, then click on Upload and then on Close this Window to return to the Edit window. When you have finished composing your post, click on Submit Post.

**jeremiasapaz** · 04-27-2017, 06:15 AM

SpreadsheetProject-JeremiasPaz-R00132786.xlsm

I was trying that but it is jerky for me... I think it worked now...

**Sintek** · 04-27-2017, 05:28 AM

What information in is your copy range...Any chance you can upload a sample workbook. Worked fine when testing on my workbook.

**jeremiasapaz** · 04-27-2017, 06:00 AM

The attach option is not working - tried from my mobile and didnt work either

I added to a temp FTP - not sure if you have access to that

sftp vfdZ61LGD@ftp.emc.com
Password: Bj8A5AB558

Connecting via a web browser:

https://ftp.emc.com/action/login?dom...ord=Bj8A5AB558

**Sintek** · 04-27-2017, 06:19 AM

Rather go this route as it is a invoice template...

Option Explicit

Sub SaveInvWithNewName()
    Dim NewInv As Variant
    Dim NewBook As Workbook
    NewInv = "C:\Invoices\LeeValley-Inv" & "-" & ActiveSheet.Range("F2").Value & ".xlsx"
    ThisWorkbook.ActiveSheet.Copy
    ActiveSheet.Pictures.Delete
    ActiveWorkbook.SaveAs NewInv
    MsgBox "File Saved to C:\Invoices"
End Sub

**jeremiasapaz** · 04-27-2017, 06:23 AM

Originally Posted by sintek

Rather go this route as it is a invoice template...

Option Explicit

Sub SaveInvWithNewName()
    Dim NewInv As Variant
    Dim NewBook As Workbook
    NewInv = "C:\Invoices\LeeValley-Inv" & "-" & ActiveSheet.Range("F2").Value & ".xlsx"
    ThisWorkbook.ActiveSheet.Copy
    ActiveWorkbook.SaveAs NewInv
    MsgBox "File Saved to C:\Invoices"
End Sub

But that will not save the selection I want, it will save everything...

Also I am getting an error in "ActiveWorkbook.SaveAs NewInv"

**jeremiasapaz** · 04-27-2017, 05:33 AM

I will upload one in a min... just need to add some dummy data instead and change some info on it...
Give me 5 min...

**Sintek** · 04-27-2017, 06:32 AM

Nope...It only saves the Invoice...This is the result I get after running code

**jeremiasapaz** · 04-27-2017, 06:54 AM

Originally Posted by sintek

Nope...It only saves the Invoice...This is the result I get after running code

Tnx but I will need a version that save only that range..

tnx anyway

**Sintek** · 04-27-2017, 07:18 AM

Jeremia

Please explain what you are wanting to save cause the ActiveSheet.Range("A1:K40") is in fact the invoice not so....

EDIT:

Option Explicit

Sub SaveInvWithNewName()
    Dim NewInv As Variant
    Dim NewBook As Workbook
    NewInv = "C:\Invoices\LeeValley-Inv" & "-" & ActiveSheet.Range("F2").Value & ".xlsx"
    ActiveSheet.Range("A1:K40").Copy
    Set NewBook = Workbooks.Add
    NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    NewBook.SaveAs Filename:=NewInv
    MsgBox "File Saved to C:\Invoices"
End Sub

**jeremiasapaz** · 04-28-2017, 01:15 AM

Originally Posted by sintek

Jeremia

Please explain what you are wanting to save cause the ActiveSheet.Range("A1:K40") is in fact the invoice not so....

EDIT:

Option Explicit

Sub SaveInvWithNewName()
    Dim NewInv As Variant
    Dim NewBook As Workbook
    NewInv = "C:\Invoices\LeeValley-Inv" & "-" & ActiveSheet.Range("F2").Value & ".xlsx"
    ActiveSheet.Range("A1:K40").Copy
    Set NewBook = Workbooks.Add
    NewBook.Worksheets("Sheet1").Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    NewBook.SaveAs Filename:=NewInv
    MsgBox "File Saved to C:\Invoices"
End Sub

Hi - The reason why I want only that Range is to remove the 3 buttons I have there so the invoice will only have the data not the Macro Buttons...

**Sintek** · 04-28-2017, 01:57 AM

May I suggest deleting pictures before saving....i.e then the code in post 10 which I amended by adding below line will work.

ActiveSheet.Pictures.Delete

**jeremiasapaz** · 04-28-2017, 03:03 AM

Originally Posted by sintek

May I suggest deleting pictures before saving....i.e then the code in post 10 which I amended by adding below line will work.

ActiveSheet.Pictures.Delete

you are the man - spot on....

do you mind if I ask another question?

I am trying to use the macro below to just paste a formula back in a cell - but it is not accepting the YES in the formula

do you know how I can do that?

    Range("B12:C12").Formula = "=IF(B6="","",IF($C$11="Yes",B6,"Type Customer Name Manually"))"
    Range("B13:C13").Formula = "=IF(B7="","",IF($C$11="Yes",B7,"Type Address Manually"))"
    Range("B15:C15").Formula = "=IF(B9="","",IF($C$11="Yes",B9,"Type County Manually"))"
    Range("B16:C16").Formula = "=IF(B10="","",IF($C$11="Yes",B10,"Type Country Manually"))"

**Sintek** · 04-28-2017, 03:20 AM

Not ideal to post question about something else in this post, but try this....
""Yes""
Please amrk this thread as solved . Thanks for rep point.

**jeremiasapaz** · 04-28-2017, 03:46 AM

Originally Posted by sintek

Not ideal to post question about something else in this post, but try this....
""Yes""
Please amrk this thread as solved . Thanks for rep point.

Again thank you so much, that fixed the issue, I will mark as solved now

How do I save a WorkBook using only select cells macro

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

How do I save a WorkBook using only select cells macro

Re: How do I save a WorkBook using only select cells macro

Re: How do I save a WorkBook using only select cells macro

Re: How do I save a WorkBook using only select cells macro

Re: How do I save a WorkBook using only select cells macro

Re: How do I save a WorkBook using only select cells macro

Re: How do I save a WorkBook using only select cells macro

Re: How do I save a WorkBook using only select cells macro

Re: How do I save a WorkBook using only select cells macro

Re: How do I save a WorkBook using only select cells macro

Re: How do I save a WorkBook using only select cells macro

Re: How do I save a WorkBook using only select cells macro

Re: How do I save a WorkBook using only select cells macro

Re: How do I save a WorkBook using only select cells macro

Re: How do I save a WorkBook using only select cells macro

Re: How do I save a WorkBook using only select cells macro

Re: How do I save a WorkBook using only select cells macro

Re: How do I save a WorkBook using only select cells macro

Re: How do I save a WorkBook using only select cells macro

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] VBA / Macro to Select File and Save As

[SOLVED] Macro to save workbook - Create Directories / Sub-Directories Based using text in cells

[SOLVED] Save the split the workbook file type as Excel Binary Workbook From Run Macro

Macro to open a closed workbook and select copy cells into an open workbook

[SOLVED] Macro to pull out data from specific cells and save it in another workbook

Macro to select cells and save as html

[SOLVED] Select sheet tabs in workbook & save to separate workbook files

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions