Create New File & Name Via Macro Code

Sep 1, 2006

I have been trying to create a macro that creates a new workbook and prompts the user to enter a new filename for it. I'm getting stuck with the code.

View 3 Replies


ADVERTISEMENT

Create Macro Button And Assigning Macro Code

Aug 10, 2014

Refer to attached file.

I have below code which successfully create a macro button and assign the macro correctly.

This is only doing for one sheet and i need to modify the code so that it does for all sheets of the workbook.

[Code] ....

Test Macro_Botton.xlsm‎

View 3 Replies View Related

Create Macro Code With VBA Code

Feb 22, 2008

I'm trying to add buttons to an excel spreadsheet at runtime. Each row in the spreadsheet should have its own buttons. I was able to create and edit them with the ActiveSheet. OLEObjects.Add() function, but after that, when i was trying to create code dynamically to react on the buttons' click events excel crashes (actually it works for one button, but not if my routine for adding a new button and event code is called more than once in a row!)

The code below works if the AddCmdbuttonWithCode() is called once, but crashes if it is called two or more times. Excel tries to restore the document after the crash and the first button and its corresponding click event code is visible and works... but NOT the second button and its event code...

The only way I can create multiple buttons right now is by calling my method once per click, opening the vba editor, changing the parameters for my AddCmdbuttonWithCode() routine and execute it again. After that I have mutliple buttons in different lines which all work fine (so the concept seems to work).

The problem seems to be the insertLine method, since everything seems to work if i leave it out (except for the code generation of course, since this is done by this part of the code :-) ). Is it possible that calling the insertLine Method can't be called multiple times? I don't know... any ideas? Feel free to test my code - it's small, easy to understand and has comments.

'this code calls the method which creates the buttons in specific cells with specific names
Private Sub CommandButton3_Click()
'the first call always works!
AddCmdbuttonWithCode "Edit_111_111_114", 23

'the second one crashes excel
AddCmdbuttonWithCode "Edit_111_111_115", 27
End Sub

View 3 Replies View Related

How To Create Macro To Change Value In CSV File

Aug 21, 2012

I have a CSV file contain many fields and all fields have value (positive & negative values). I have 3 questions here:

1) This file is created on Friday. So the date in Field1 is Friday's date. I want to change the date to Saturday date. How can i change it using macro?
2) I want to set the value for fields 24 until 26 to 0. I want the script to force the field value to be 0. How can i do that by using macro?..and fyi, my CSV file have header in the first row. Below i illustrate my original csv file and the expected output.
3) How can i automate the macro to run every Friday afternoon?

Original csv
Field1, Field2,....,Field24, Field25, Field26, Field27
"20120803","ABC",...,9.032,24.52,-6.325,21.12
"20120803","ABC",...,5.242,-1.53,7.005,21.12

Expected output csv
Field1, Field2,....,Field24, Field25, Field26, Field27
"20120804","ABC",...,0,0,0,21.12
"20120804","ABC",...,0,0,0,21.12

View 1 Replies View Related

Create A Separate File After Macro Done

Apr 19, 2006

How can I code to create a copy of a worksheet in an excel file I am using a macro on, after the macro is done processing? In other words, when the macro completes processing, create a copy of the worksheet labelled "Output", and save it in a directory that the user chooses? Also, would it be possible to create a .pdf file?

View 9 Replies View Related

Create Workbook With Macro Code Within

Jan 11, 2007

Right now I use a VBA-Code to create new worksheets in a workbook. my Question:
is it possible to attach code to the newly created Worksheet via vba-code??

View 3 Replies View Related

Custom Macro To Create New File From Data

Jun 12, 2009

I'm looking to create a new file from data in my table. I don't want to even imagine having to do this manually again...I'm optimistic there is a solution. All the data needed to create the file is in the table, but i need it stacked and organized in a weird way. It's almost to hard to explain...so I color coded an attachement that basically says it all. It's pretty much the same thing repeated over and over except the last 2 lines. It's just a really messed up organization. In the real version I need the new file in a new workbook. I'm extremely grateful to anyone who can automate this thing

View 2 Replies View Related

Create A Border Macro When Open The File

Jul 26, 2009

I have recorded a macro which will create the border for me when the file is opened, but it turned out too long. if someone can show me how to reduce the codeing. I have the attached file.

Range A5 to E20 thin Border all sides and thick border allround (16 rows)
Range A21 to E36 thin border all sides and thick border allround (16 rows)

end range is A356.

View 5 Replies View Related

Fixing Macro: Only Create Around 53 Text File

Aug 23, 2009

First need to create a "Text" Folder in your desktop, then try running the macro. It will create a number of text files inside the "Text" folder.. And they contain the used cells from each row. It's supposed to create 982 text files, with the text name referred to the first column. Problem is, if you try running it, it will only create around 53 text file

View 2 Replies View Related

Macro To Create Automatic Backup Of File

Oct 27, 2013

Is it possible to create a macro that automatically saves a backup of the excel document in another desired location?

I have this formula:

Sub backupbutton()
Dim fname
fname = "D:" & Format(Now, "dd mmm yy hh mm") & ".xlsm"
ThisWorkbook.SaveAs Filename:=fname
End Sub

But that just keeps on making multiple copies of the file rather than overwriting the backup in the D: location.

Also, that particular macro requires me to have to click on a button in order for it to work but I would prefer that it happens automatically when the original file is saved.

View 2 Replies View Related

Macro To Create New Folder And Save File

May 27, 2009

I want to create a macro that will create a new folder called "Fungicide Quotes" under my documents and will save the workbook using the cell reference d4:f4 for the file name, which are merged cells. I have tried the following but can't get it to work. Any help would be appreciated, Thanks

Sub Save_wrkbk()

Dim strFilename, strDirname, strPathname, strDefpath As String
On Error Resume Next
strDirname = "Fungicide Quotes"

strFilename = Range("d4:f4").Value
strDefpath = "C:My Documents"
If IsEmpty(Filename) Then Exit Sub

MkDir strDefpath & strDirname
strPathname = strDefpath & strDirname & "" & strFilename.......................

View 9 Replies View Related

Macro Code To Create Line Graph

May 2, 2014

I have a macro code that will create line graph referring the data given in defined column A1-C4,

Code with Static column range:-

ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=Range("'Sheet2'!$A$1:$C$4")
ActiveChart.ChartType = xlLineStacked

I tried to modify the above code, so it will refer undefined/dynamic data column, but getting an error during execution "Run Time Error - 424:" "Object required"

Code with Dynamic column range:-

ActiveSheet.Shapes.AddChart.Select
ActiveChart.SetSourceData Source:=ActiveSheet.Range("a1", _
ActiveSheet.Range("a1").End(xlDown).End(xlToRight)).Select
ActiveChart.ChartType = xlLineStacked

View 1 Replies View Related

VBA Macro Code To Create Multiple Charts

May 9, 2009

I need to create 63 charts from data which I have in two columns. I want to create multiple charts using one macro. For the first chart I want it to use cells K2:K80 as the x values, and M2:M80 as the y values. For the next chart I want it to use cells K81:K159 as the x values and M81:159 as the y values. For the next chart I want it to use cells K160:K238 as the x values and M160:M238 as the y values. I want to continue this, creating a chart for every 78 cells of data, all the way until the 63rd chart which uses K4900:K4978 as the x values and M4900:M4978 as the y values. I have created the following macro by " recording." This macro generates the first chart that I want:

Sub Macro5()
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).XValues = "=Sheet1!$K$2:$K$80"
ActiveChart.SeriesCollection(1).Values = "=Sheet1!$M$2:$M$80"
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveWindow.SmallScroll Down:=-3
End Sub

How can I alter this macro to create all 63 charts?. It seems like there is an easy way to do this, but I don't use macros very much (at all).

View 2 Replies View Related

Create UserForm With Controls Via Macro Code

Apr 20, 2008

This code create a userform on the workbook opening .I'm having problems with

1. Closeing the Visual basic window after this procedure runs
2. Being able to use the create userformstext box value in other procedures
3. how to delete the userform on close

Sub AddUserFormInputRequest()
Dim objVBProj As VBProject
Dim objVBComp As VBComponent
Dim objVBFrm As UserForm
Dim objChkBox As Object
Dim x As Integer
Dim strCode As String
Dim firstLine As Long, SecondLine As Long
Set objVBProj = Application.VBE.ActiveVBProject
Set objVBComp = objVBProj.VBComponents.Add(vbext_ct_MSForm)
With objVBComp
. Name = "InputRequest"
. Properties("Width") = 200
.Properties("Height") = 100......................

View 6 Replies View Related

How To Create And Add Password To Default Windows Zip File Using VBA Macro

Mar 17, 2013

how to create and add password to a default windows zip file using vba excel macro.

View 2 Replies View Related

Need To Embed A Sound File & Create A Macro To Play It..

Jan 20, 2010

embed a audio file (.wav or .mp3) into a Excel Sheet and be able to have it play from a macro? I need the Clip to stay in the file not refer to a location on the pc and have it play from the sheet not open media player. Can this be done?

View 7 Replies View Related

Create & Save Workbook To Same Path As Macro File

Aug 21, 2008

I have code to create a new workbook, and when I try to rename it I get "Compile error: Can't assign to read-only property."

Dim objXlApp As Object
Dim wkb As Workbook
Dim wks As Worksheet
Set objXlApp = CreateObject("Excel.Application")
' Create a workbook
Set wkb = objXlApp.Workbooks.Add
' Delete all worksheets bar the first one.
For Each wks In wkb.Worksheets
If Not wks.Index = 1 Then
wks.Delete

End If
Next wks
'Create some worksheets and names
With wkb
.Worksheets(1).Name = "myWorksheet1"
.Worksheets.Add.Name = "myWorksheet2"
.Worksheets.Add.Name = "myWorksheet3"
.Worksheets.Add.Name = "myWorksheet4"
End With...................................

View 5 Replies View Related

Create Multiple Buttons All Assigned To Macro Code

Mar 6, 2008

I'd like to write a macro to create buttons with the caption "Fix" in over 300 cells. Furthermore, I would like each button, when pressed to run a macro that would copy and paste the values (paste special) of the entire row in which the button is situated as well as copy and "paste special" the values in the fixed cells $J$2, $K$2,$L$2, and $M$2. I've attached a file to clarify what I'm sure is an extremely convaluted statement of my problem.

View 5 Replies View Related

Macro To Create New Line In Inventory Pointing To Specific File

Jul 9, 2014

I created the following macro by recording the macro and going through the steps manually, however I need to make some changes and can't seem to accomplish what I'm trying to do.

The Macro opens a master inventory file, creates a new line, and then links certain column cells in the inventory to corresponding places within the original form (the macro is executed from the original form once it's completed).

Problem is, the macro is written using the form "template" so whenever I save the template as the name of the unique item, it won't update the macro language as well.

What I'm trying to accomplish is when someone opens the template, the save immediately with a different file name, and once the form is completed and the macro is run, it's creating the new line in the inventory pointing to that specific file.

I thought somehow utilizing ThisWorkbook within the macro instead of explicitly using something like

"='[Control Sheets (JOHN TEST).xls]FUND SET_UP PG_1'!R2C3" would accomplish what I'm trying to do.

View 5 Replies View Related

Create Master File With Macro Built In That Will Allow Multiple Users To Use It At Once

Feb 7, 2014

I am trying to create a master file, with a macro built in, that will allow multiple users to use it at once. The macro is to open a dilouge(sp sorry) box showing the contents of a specific folder, allow the user to selct one of the sheets, then copy and paste the set details from the hidden tab on this sheet (All sheets will be the same barring title), append the details to the master list in the first empty row.

I've got this far thus
Sub Macro1()
'
' Macro1 Macro
'
'
' Modify this folder path to point to the files you want to use.
FolderPath = "My Folder name here"

' Set the current directory to the the folder path.
ChDrive FolderPath

[Code] .....

So I can copy the row, but I can't get the first part to open .

View 3 Replies View Related

Create Macro Code That Auto Fill Cell With Formula?

May 23, 2014

When I enter data in the cells A1 and B1, C1 needs to calculate the result. (not copy and paste)

For examle: I have formula C1=(A1+B1)/2

C column for formula, but shows result only when there is a data in A and B

View 7 Replies View Related

Create Surface Chart Via Macro Code Based On Selection

May 28, 2008

I want to write a macro to add a surface chart for a Data Range which includes 6000 data points. But the series selected for the chart don't cover all Data Ranges, only part of them.

Public Sub AddChart2(LastRow As Integer, LastCol As Integer)

Dim cht As ChartObject, currentSheet As Worksheet
Dim rng As Range, newRow As Range, srcRange As Range
Dim colIdx As Integer

colIdx = 5

View 7 Replies View Related

Macro To Save Duplicate File And Create Folder Name Based On Cell Value

Nov 25, 2009

I currently use the following code to create a duplicate file based on two cells within a directory and folder i specify. These cells consist of the team and week commencing date (mondays date of week which is cell 'Main Menu'!K8)

View 7 Replies View Related

Excel 2003 :: Macro To Create Text File - Runtime Error 91

Jun 18, 2012

When I am running a macro in excel 2003/windows 7 and trying to create a text file, it is giving this error. I am attaching two pictures.

View 1 Replies View Related

Macro VBA Code To Run Video File?

Nov 14, 2011

What line of code do you need to include in you macro vba code to run a video file?

View 4 Replies View Related

Open A Pdf File In Macro Code

Aug 11, 2004

how to open a pdf file (located: "C:myfile.pdf") in macro code?

View 4 Replies View Related

Macro Code Create Pivot Table Based On Dynamic Source Data

Nov 28, 2006

The "Sum" sheet can change its number of rows. The pivot table is based on it. I'm having trouble with the SourceData portion of the code in my macro ....

View 9 Replies View Related

Call Private Macro But Do Not Copy Code To New File

Apr 27, 2012

I would like to have something that will auto run the code in Module1 when the Excel File is opened, but I do not want to code tansfered into the file when it is saved in its location.

I don't mind if the code is tranfered to saved file if in fact I can make sure the code is not run when the new file is opened...

Everything is working as intended at the moment, but when openeing the saved file the entire macro tried to run again. This is what I do not want...

I currently have the following code in Module1.

Private Sub Workbook_Open()
'
' Format_Copy_&_Paste
'
Application.OnKey "^+r", "Warehouse_Cost_Summary"
'
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "DataSet"

[Code] ..........

View 4 Replies View Related

Save File Using Cells For Path & Name Macro Code

Mar 19, 2008

I want to create a macro that saves my file this way:

in a folder named after B2
and
as a file named after B4n.

View 4 Replies View Related

VBA Code To Convert Excel To Text File Of DML Statements (Insert) Using Macro

Jun 11, 2014

I need a VBA code to create a Text file which should be in the form of DML (my scenario is to get Insert Statements for the Data available in the Excel) from Excel Sheet. And this has to be done by assigning a macro to command button where by clicking on it the DML text file should be generated. Even the syntax for the scenario will do.

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved