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

STEEL010

Board Regular
Joined
Dec 29, 2017
Messages
74
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
 

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,582
Office Version
2007
Platform
Windows
Put the next codu in a button

Code:
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

Board Regular
Joined
Dec 29, 2017
Messages
74
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,582
Office Version
2007
Platform
Windows
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:
Code:
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

Board Regular
Joined
Dec 29, 2017
Messages
74

ADVERTISEMENT

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

Board Regular
Joined
Dec 29, 2017
Messages
74
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,582
Office Version
2007
Platform
Windows

ADVERTISEMENT

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

Your code:
Code:
        Sheets("SHEET1").Range("A1:F1").Select
        Application.CutCopyMode = False
        Selection.Delete Shift:=xlUp
My code:
Code:
    Range("A1:F1").Delete Shift:=xlUp
Both codes delete row 1 and move the rows upwards

I await your comments
 
Last edited:

STEEL010

Board Regular
Joined
Dec 29, 2017
Messages
74
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
 

STEEL010

Board Regular
Joined
Dec 29, 2017
Messages
74
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
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,338
Messages
5,510,705
Members
408,808
Latest member
TheTomar

This Week's Hot Topics

  • Turn fraction around
    Hello I need to turn a fraction around, for example I have 1/3 but I need to present as 3/1
  • TIme Clock record reformatting to ???
    Hello All, I'd like some help formatting this (Tbl-A)(Loaded via Power Query) [ATTACH type="full" width="511px" alt="PQdata.png"]22252[/ATTACH]...
  • TextBox Match
    hi, I am having a few issues with my code below, what I need it to do is when they enter a value in textbox8 (QTY) either 1,2 or 3 the 3 textboxes...
  • Using Large function based on Multiple Criteria
    Hello, I can't seem to get a Large formula to work based on two criteria's. I can easily get a oldest value based one value, but I'm struggling...
  • Can you check my code please
    Hi, Im going round in circles with a Compil Error End With Without With Here is the code [CODE=rich] Private Sub...
  • Combining 2 pivot tables into 1 chart
    Hello everyone, My question sounds simple but I do not know the answer. I have 2 pivot tables and 2 charts that go with this. However I want to...
Top