A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Excel » Worksheet Functions
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Creating a Macro to autofill and save



 
 
Thread Tools Display Modes
  #1  
Old February 9th, 2010, 03:06 PM posted to microsoft.public.excel.worksheet.functions
LauraB
external usenet poster
 
Posts: 23
Default 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  
Old February 9th, 2010, 03:27 PM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default 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  
Old February 9th, 2010, 03:44 PM posted to microsoft.public.excel.worksheet.functions
LauraB
external usenet poster
 
Posts: 23
Default 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  
Old February 9th, 2010, 03:52 PM posted to microsoft.public.excel.worksheet.functions
LauraB
external usenet poster
 
Posts: 23
Default 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  
Old February 9th, 2010, 04:01 PM posted to microsoft.public.excel.worksheet.functions
Jacob Skaria
external usenet poster
 
Posts: 5,952
Default 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  
Old February 9th, 2010, 04:27 PM posted to microsoft.public.excel.worksheet.functions
LauraB
external usenet poster
 
Posts: 23
Default 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 won’t work, I think there
is a way to add a button on to the toolbar ribbon but I’m 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  
Old February 11th, 2010, 02:16 PM posted to microsoft.public.excel.worksheet.functions
LauraB
external usenet poster
 
Posts: 23
Default 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 won’t work, I think there
is a way to add a button on to the toolbar ribbon but I’m 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

  #8  
Old February 11th, 2010, 06:01 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default Creating a Macro to autofill and save

Place it in your Personal.xls workbook or into a new workbook which you save
as an Add-in.


Gord Dibben MS Excel MVP

On Thu, 11 Feb 2010 05:16:01 -0800, LauraB
wrote:

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 won’t work, I think there
is a way to add a button on to the toolbar ribbon but I’m 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


  #9  
Old March 16th, 2010, 03:37 PM posted to microsoft.public.excel.worksheet.functions
LauraB
external usenet poster
 
Posts: 23
Default Creating a Macro to autofill and save

Hi,

Thanks for your advice however it is still not working for me.

Right I have opened a black worksheet, clicked on the macro, created new,
pasted the code into visual basic, colsed and returned to excel - What do I
do now?

I have tried saving it as and add in and an macro enabled work book.

I have also created a quick toolbar button for it but once i cloce excell
and click on the button it doesnt work as the macro isnt there anymore.

Laura

"Gord Dibben" wrote:

Place it in your Personal.xls workbook or into a new workbook which you save
as an Add-in.


Gord Dibben MS Excel MVP

On Thu, 11 Feb 2010 05:16:01 -0800, LauraB
wrote:

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 won’t work, I think there
is a way to add a button on to the toolbar ribbon but I’m 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


.

  #10  
Old March 16th, 2010, 05:46 PM posted to microsoft.public.excel.worksheet.functions
Gord Dibben
external usenet poster
 
Posts: 20,252
Default Creating a Macro to autofill and save

You placed your macro(s) into a General module in a new
workbook.........right?

You saved the workbook as an Add-in..........right?

Now you must load the add-in.

ButtonExcel OptionsAdd-insManageExcel Add-insGo

Browse to the Add-in and checkmark it.


Gord

On Tue, 16 Mar 2010 07:37:02 -0700, LauraB
wrote:

Hi,

Thanks for your advice however it is still not working for me.

Right I have opened a black worksheet, clicked on the macro, created new,
pasted the code into visual basic, colsed and returned to excel - What do I
do now?

I have tried saving it as and add in and an macro enabled work book.

I have also created a quick toolbar button for it but once i cloce excell
and click on the button it doesnt work as the macro isnt there anymore.

Laura

"Gord Dibben" wrote:

Place it in your Personal.xls workbook or into a new workbook which you save
as an Add-in.


Gord Dibben MS Excel MVP

On Thu, 11 Feb 2010 05:16:01 -0800, LauraB
wrote:

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 won’t work, I think there
is a way to add a button on to the toolbar ribbon but I’m 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


.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:17 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.