SaveAs VBA Error

[mar050703]

Hello

Please can someone help me?

What I am trying to do is with the press of a button, copy sheet2 (which has formulas) as values only and then save that sheet as another file. But I need to keep the original sheet with the formulas.

My code (all beit test code) is:

Sub Button1_Click()

Worksheets("Sheet2").Range("A1:F11") = Worksheets("Sheet2").Range("A1:F11").Value

Sheets("Sheet2").Copy
        ActiveWorkbook.SaveAs "C:\Test.xls"
        
        ActiveWorkbook.Close
        
End Sub

But what I appear to get is the original sheet having value only in Sheet2 and not the original formulas.

The problem is, if some one in error then saves the *.xlsm file in error we would have to code the formulas again.

Thanks for your help

 

[mintjulep]

Your code is only working with one (the original) file. If you want to work with a second file they you need....well, a second file.

 

[mar050703]

Thankyou for your comment. Sorry maybe I was not clear enough.

What I am trying to do is in the *.xlsm file (where the macro is) copy Sheet2 as a value only and save it as a *.xls (or *.xlsx) file and then go back to the original *.xlsm file with sheet2 still having the formulas.

Is this possible?

My current code works only if the user remembers NOT to save the .xlsm file when you close it (using the red cross)

Thanks again, I hope I have made this clear this time.

 

[SkipVought]

Add a new workbook.

COPY the DATA from Sheet2 and then PASTE VALUES to the active sheet in the new workbook. Then save.

Your original workbook will be pristine.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mintjulep]

What I am trying to do is in the *.xlsm file (where the macro is) copy Sheet2 as a value only and save it as a *.xls (or *.xlsx) file and then go back to the original

In order to "go back to" something you first have to be somewhere else. You code never is.

This writes over data in the "original". This is contrary to your desire to preserve the original data and formulae.

Worksheets("Sheet2").Range("A1:F11") = Worksheets("Sheet2").Range("A1:F11").Value

This does nothing, because there is no subsequent paste operation

Sheets("Sheet2").Copy

 

[mar050703]

Hi Guys

Sorry about this, I have the following code (which works for what I want):

Sheets("Sheet2").Copy
Cells.Copy
Range("A1").PasteSpecial Paste:=xlPasteValues
    

Application.CutCopyMode = False


        ActiveWorkbook.SaveAs "C:\Test.xlsx"
        
        ActiveWorkbook.Close

This code copies sheet2 into another book and pastes values only.

But I seem to be able to work out the code to do the same for Sheet3, at the same time.

I have tried (in place of the 1st line):

 Sheets(Array("Sheet2", "Sheet3")).copy

whilst this copies sheet 3, it does not copy values only.

What code am I missing please?

Thanks

 

[SkipVought]

As I stated earlier, you ought to copy the DATA to a new workbook!

Sub testit()
    With Workbooks.Add
        ThisWorkbook.Sheets("Sheet2").Cells.Copy
        .Sheets(1).Cells(1, 1).PasteSpecial xlPasteValues
        If .Sheets.Count = 1 Then .Sheets.Add
        
        ThisWorkbook.Sheets("Sheet3").Cells.Copy
        .Sheets(2).Cells(1, 1).PasteSpecial xlPasteValues
        
        .SaveAs "C:\Test.xlsx"
        .Close
    End With
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[mar050703]

Thanks Skip

Your code is great thanks, however it appears not to keep any cell colours.

I have managed to solve my problem (which keeps the formatting) with this code:

    With ThisWorkbook
        .Worksheets("Sheet2").Copy
        Cells.Copy
        Range("A1").PasteSpecial Paste:=xlPasteValues
        .Worksheets("Sheet3").Copy After:=ActiveWorkbook.Worksheets(ActiveWorkbook.Worksheets.Count)
        Cells.Copy
        Range("A1").PasteSpecial Paste:=xlPasteValues
   End With
   
   Application.CutCopyMode = False
        
      ActiveWorkbook.SaveAs "C:\Test.xlsx"
        
      ActiveWorkbook.Close

Sorry I did not mention formats before, I was not sure if they would copy or not.

Thanks again.

 

[SkipVought]

Sub testit()
    With Workbooks.Add
        ThisWorkbook.Sheets("Sheet2").Cells.Copy
        .Sheets(1).Cells(1, 1).PasteSpecial xlPasteValues
        .Sheets(1).Cells(1, 1).PasteSpecial [b]xlPasteFormats[/b]
        If .Sheets.Count = 1 Then .Sheets.Add
        
        ThisWorkbook.Sheets("Sheet3").Cells.Copy
        .Sheets(2).Cells(1, 1).PasteSpecial xlPasteValues
        .Sheets(2).Cells(1, 1).PasteSpecial [b]xlPasteFormats[/b]
        
        .SaveAs "C:\Test.xlsx"
        .Close
    End With
End Sub

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[strongm]

You might want to investigate the two following paste options:

xlPasteFormulasAndNumberFormats
xlPasteFormats

 

[SkipVought]

You insist on copying SHEETS rather than the DATA. This means that you need to maintain the structure of your table after doing the SaveAs. Very messy.

Did you try my approch: ADDING a NEW workbook and copying DATA to that NEW workbook? Nice an clean. You SAVE it and CLOSE it. Nothing happens to your workbook and you remain in the workbook as pristine as when you started the process.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[SkipVought]

You code is pasting values INTO Sheet3 in your original workbook.

Your code is NOT pasting values into Sheet2 in your new workbook!

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]