Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a Macro to autofill and save
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 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a Macro to autofill and save
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 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a Macro to autofill and save
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 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a Macro to autofill and save
I have also found another issue - When I close all the excell documents down.
then try it agin the macro has gone? Do I have to save it somewhere? Thanks Laura "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 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a Macro to autofill and save
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 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a Macro to autofill and save
I really appreciate your help however I think I need to explain what I am
trying to do a bit more. I am exporting data from our system into excel (weekly basis). Once this data has opened in excel I would like there to be a button on the toolbar that will run the macro. Once the macro has ran the user can save the file then email it to a customer. This means that adding a button to the worksheet wont work, I think there is a way to add a button on to the toolbar ribbon but Im not sure. Also would you be able to explain what is different about the first script compared to the second edit? Thanks Laura "Jacob Skaria" wrote: 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 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Creating a Macro to autofill and save
Can I just bring this to the top again.
Is there a way to save the macro so that I can use it in every excel document? Thanks "LauraB" wrote: I really appreciate your help however I think I need to explain what I am trying to do a bit more. I am exporting data from our system into excel (weekly basis). Once this data has opened in excel I would like there to be a button on the toolbar that will run the macro. Once the macro has ran the user can save the file then email it to a customer. This means that adding a button to the worksheet wont work, I think there is a way to add a button on to the toolbar ribbon but Im not sure. Also would you be able to explain what is different about the first script compared to the second edit? Thanks Laura "Jacob Skaria" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Creating an autofill type function from a data validation box | Excel Discussion (Misc queries) | |||
Creating controls to autofill a form | Excel Discussion (Misc queries) | |||
Creating a macro to save only partial data | Excel Discussion (Misc queries) | |||
creating a macro for a "Save As" box | Excel Discussion (Misc queries) | |||
autofill macro | Excel Discussion (Misc queries) |