VBA - save as .csv and .text under one button

[STEEL010]

Hi There,

I have a problem that I can't figure out.
I have two sub's that needs to run under one button, and the thing is that it have to create sperate type of file to export (.csv and .txt)
normally they work, but when the CSV file needs that save then it will not?
Can someone help me on this matter.

see below code for impression:



Sub SHEET1_SAVE_AS()
'
If ActiveSheet.Name = "SHEET1" Then
Sheets("SHEET1").Select
Sheets("SHEET1").Range("A2:E50").Select
ActiveWindow.SmallScroll Down:=-27
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("SHEET1").Range("A1:F1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Sheets("SHEET1").Range("C1:E50").Select
Selection.Delete Shift:=xlToLeft
Sheets("SHEET1").Range("A1").Select

ChDir "\\FILEPATH"
ActiveWorkbook.SaveAs Filename:= _
"\\FILEPATH\" & Format(Now(), "yyyy-mm-dd") & " " & "SHEET1.csv", _
FileFormat:=xlCSV, CreateBackup:=False

Else
End If
End Sub



Sub SHEET2_SAVE_AS()
'
If ActiveSheet.Name = "SHEET2" Then
Sheets("SHEET2").Select
Sheets("SHEET2").Rows("2:2").Select
Selection.Insert Shift:=xlDown
Selection.Insert Shift:=xlDown
Sheets("SHEET2").Rows("2:3").Select
Selection.ClearContents
Sheets("SHEET2").Rows("2:3").Select
Sheets("SHEET2").Rows("2:3").EntireRow.AutoFit
Sheets("SHEET2").Range("A4:F54").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWindow.SmallScroll Down:=-9
Sheets("SHEET2").Range("A1").Select
Application.CutCopyMode = False

ChDir "\\FILEPATH"
ActiveWorkbook.SaveAs Filename:= _
"\\FILEPATH\" & Format(Now(), "yyyy-mm-dd") & " " & "SHEET2.txt", _
FileFormat:=xlText, CreateBackup:=False

Else
End If
End Sub

 

[DanteAmor]

Put the next codu in a button

Sub Sheet1_and_Sheet2_Save_AS()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    '
    'Save Sheet1
    Sheets("SHEET1").Copy
    Range("A2:E50").Copy
    Range("A2").PasteSpecial Paste:=xlPasteValues
    Range("A1:F1").Delete Shift:=xlUp
    Range("C1:E50").Delete Shift:=xlToLeft
    '
    ChDir "\\FILEPATH"
    ActiveWorkbook.SaveAs Filename:= _
        "\\FILEPATH\" & Format(Now(), "yyyy-mm-dd") & " " & "SHEET1.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close False
    '
    'Save Sheet2
    Sheets("SHEET2").Copy
    Rows("2:3").Insert Shift:=xlDown
    Rows("2:3").ClearContents
    Rows("2:3").EntireRow.AutoFit
    Range("A4:F54").Copy
    Range("A4").PasteSpecial Paste:=xlPasteValues
    '
    ActiveWorkbook.SaveAs Filename:= _
        "\\FILEPATH\" & Format(Now(), "yyyy-mm-dd") & " " & "SHEET2.txt", _
        FileFormat:=xlText, CreateBackup:=False
    ActiveWorkbook.Close False
    '
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Test and tell me

Regards Dante Amor

 

[STEEL010]

Hello Dante,



Thank you for reply.
your code runs and saves correct, but my layout is not as it should be.
Is there a way that it keeps my written macro???
If not I can not us your code, because the layout is crucial for further process.

And when sheet2 is not visible it gives me an error, how to solve this issue in your code?


Greetings Steel010

 

[DanteAmor]

but my layout is not as it should be.

On which sheet or file do you have layout problems?
If you execute my code step by step, which line does the layout lose?

And when sheet2 is not visible it gives me an error, how to solve this issue in your code?

Run this macro:

Sub Sheet1_and_Sheet2_Save_AS()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    '
    'Save Sheet1
    Sheets("SHEET1").Copy
    Range("A2:E50").Copy
    Range("A2").PasteSpecial Paste:=xlPasteValues
    Range("A1:F1").Delete Shift:=xlUp
    Range("C1:E50").Delete Shift:=xlToLeft
    '
    ChDir "\\FILEPATH"
    ActiveWorkbook.SaveAs Filename:= _
        "\\FILEPATH\" & Format(Now(), "yyyy-mm-dd") & " " & "SHEET1.csv", _
        FileFormat:=xlCSV, CreateBackup:=False
    ActiveWorkbook.Close False
    '
    'Save Sheet2
    wvisible = Sheets("SHEET2").Visible
    If wvisible <> -1 Then
        Sheets("SHEET2").Visible = -1
    End If
    Sheets("SHEET2").Copy
    Rows("2:3").Insert Shift:=xlDown
    Rows("2:3").ClearContents
    Rows("2:3").EntireRow.AutoFit
    Range("A4:F54").Copy
    Range("A4").PasteSpecial Paste:=xlPasteValues
    '
    ActiveWorkbook.SaveAs Filename:= _
        "\\FILEPATH\" & Format(Now(), "yyyy-mm-dd") & " " & "SHEET2.txt", _
        FileFormat:=xlText, CreateBackup:=False
    ActiveWorkbook.Close False
    Sheets("SHEET2").Visible = wvisible
    '
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

I await your comments

 

[STEEL010]

Hi Dante,

Sheet1 first row must be deleted, this not the case.

Sheet2 first row must be empty and text must start at row 4

And it must come back to original sheet in orginal format.

I hope this clearify......

Greetings,
Steel010

 

[STEEL010]

Dante,

sorry I was to quick, I have copied your macro in my VBA and its still the same, its not running my written macro as I explained here above.
saving the sheet is excellent.

Hi Dante,

Sheet1 first row must be deleted, this not the case.

Sheet2 first row must be empty and text must start at row 4

And it must come back to original sheet in orginal format.

I hope this clearify......

Greetings,
Steel010

 

[DanteAmor]

I'm sorry, I'm confused. do you have problems with my macro?

Your code:

        Sheets("SHEET1").Range("A1:F1").Select
        Application.CutCopyMode = False
        Selection.Delete Shift:=xlUp

My code:

    Range("A1:F1").Delete Shift:=xlUp

Both codes delete row 1 and move the rows upwards

I await your comments

 

[STEEL010]

Dante,

I have found the issue!!!......... you have to put "Sheets("SHEET1")." in front of "Range("A1:F1").Delete Shift:=xlUp"
then its like this

Sheets("SHEET1").Range("A1:F1").Delete Shift:=xlUp

I did at every row and range argument. And now it work splended.

thank you so much for your effort for helping me on this issue.


Muchas gracias amigo,
Steel010

 

[DanteAmor]

Un placer ayudarte!

 

[STEEL010]

Dante,


I need one more help, because if I think I'm there...... then something else accurse



next thing is... suppose that Sheet1 is not published and Sheet2 is created then it gives me an error to debug.
How can we wright, when a sheet is not visible/not created that it proceed to gone on to the next sheet and save the published sheet.

Greeting
Steel010