Check out the below link with the modified macro
http://office.microsoft.com/en-us/ex...lBox%20toolbar)
Sub SaveASCSV()
Dim strFile As String, lngLastRow As Long, lngCol As Long
lngCol = ActiveSheet.Rows(1).Find("Line No", _
LookIn:=xlValues, Lookat:=xlWhole).Column
lngLastRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row
Cells(2, lngCol).Value = 1
Range(Cells(2, lngCol), Cells(lngLastRow, lngCol)).DataSeries _
Rowcol:=xlColumns, Type:=xlLinear, Date:=xlDay, Step:=1, _
Stop:=lngLastRow, Trend:=False
strFile = Application.GetSaveAsFilename("", "Text Files (*.csv), *.csv")
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=strFile, FileFormat:=xlCSVMSDOS
Application.DisplayAlerts = True
End Sub
--
Jacob
"LauraB" wrote:
Wow, thanks so much - That helped a great deal.
I wish I knew more about the actual script, I was attemping to record it
first then having a look at the script but I just got confused.
On the same worksheet can I create a button for the user to press that will
go on the toolbar so they dont have to go to the developer tab?
Also though I dont think this will be an issue in this case can you autofill
a column based on the header name ie autofill column called "Line No".
Thanks again,
Laura
"Jacob Skaria" wrote:
Hi Laura
You can try out the below macro. If you are new to macros..
--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()
Sub Macro()
Dim strFile As String, lngLastRow As Long
lngLastRow = ActiveSheet.Cells.Find(What:="*", _
SearchDirection:=xlPrevious, SearchOrder:=xlRows).Row
Range("K2") = 1
Range("K2:K" & lngLastRow).DataSeries Rowcol:=xlColumns, _
Type:=xlLinear, Date:=xlDay, Step:=1, Stop:=lngLastRow, Trend:=False
strFile = Application.GetSaveAsFilename("", "Text Files (*.csv), *.csv")
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:=strFile, FileFormat:=xlCSVMSDOS
Application.DisplayAlerts = True
End Sub
--
Jacob
"LauraB" wrote:
Hi,
I am trying to create a macro that will complete the following.
Autofill column K with sequential numbers starting from cell K2 with 1 to
however many rows of data there are.
Then for the file to automatically save as a CSV (Comma Deliminated) format
so that the user just has to typr the name of the file in.
I am not a competent using visual script however should be abl to navigate
myself to the right place.
Thanks
Laura