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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

DanteAmor

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

Watch MrExcel Video

Forum statistics

Threads
1,101,936
Messages
5,483,783
Members
407,410
Latest member
catherinejoy

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top