For Excel Experts

[hajduk1908]

Hello Expert

I have a macro to perform a function, essentially it will delete a certain number of rows then export the remaining worksheet as a csv. I was wondering if I could put a reset button on it so if the user made a mistake they could start again, ideally a drop list for example I could put at the start of the workbook. for
I have 20 worksheet. When I reset it means back to pre-macro run state.

Thanks

eg.

Reset Worksheet Orange
Reset Worksheet Black
Reset Worksheet Red

The following is an example Worksheet Orange that I use to delete the first 11 rows and save the rest of it as a CSV. I have used the recorder function in excel but unfortunately when replayed all the colours and writing are hardly legible

Sub Delete_Rows11_Loop()


ActiveWorkbook.Save

Dim WS As Worksheet
Dim path As String

path = ActiveWorkbook.path & "\" & Left(ActiveWorkbook.Name, InStr(ActiveWorkbook.Name, ".") - 1)

' Loop through all selected sheets.
For Each WS In ActiveWindow.SelectedSheets
WS.Rows("1:11").Delete ' delete 11 rows at top of each sheet.
WS.Copy

Range("A1:A" & ActiveSheet.Cells.SpecialCells(xlCellTypeLastCell).Row).Select 'select relevant cells
Selection.SpecialCells(xlBlanks).EntireRow.Delete 'remove empty rows
ActiveWorkbook.SaveAs Filename:=path & "_" & WS.Name & ".csv", FileFormat:=xlCSV, CreateBackup:=False
ActiveWorkbook.Close SaveChanges:=False

Next WS
MsgBox "Done! Please exit without saving"
MsgBox "If you have problems importing run the CleanCSV function and retry inport into PBRC"
End Sub

 

[chirayu]

There's no such reset option in a Macro. A workaround is that when the macro is run, it should first copy that sheet as is into a new sheet in the file e.g. "Working". Then it'll do its stuff you want. If you hit reset. It should copy back from "Working" sheet to the chosen sheet.

 

[Marc L]

Duplicate thread ‼

Anyway, easier using a template …

​

 

[hajduk1908]

Hi guys

Could I get an example please.... because the delete and copy I use contains a table....... when its copied back it doesnt look all that legible and there is no way I could give it to a user.

Thanks

 

[chirayu]

Please provide sample file

 

[hajduk1908]

Enclosed is a sample file, Thanks in advance, you will see the export to csv in one of the tables where it does the deletion of rows and exports.

 

[chirayu]

Oh seems simple enough. You know you have the ActiveWorkbook.SaveAs option. Use it at the start of the macro to save the entire file as is into your file path with a different naming convention.

Then use the Workbooks.Open command on the "Reset" option that you wanted. Which would open that saved file & copy back the old info.

 

[hajduk1908]

excellent...will try!!!!...Many Thanks!