Macro

Laszlo

New Member
Joined
Feb 11, 2004
Messages
25
I'm just recently started to making in my work macros, so there is a lot to learn. At this time I need a really big help to create a macro I would like to use. :oops:
Here is what my macro should do:
-open a blank file named "Blank trip"
- some of the cells has to be linked to a cells in a worksheet in the different workbook, where from the macro can copy & paste some data
- save it under a new name ("Blank trip #1")
Thanx a lot
Laz
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

CraigM

Active Member
Joined
Feb 27, 2003
Messages
320
It sounds like the macro recorder would be a good place to start. Record the actions you want using tools>macros>Record New Macro. Then Step into the code, and try to understand it, and play around with it.
 

Laszlo

New Member
Joined
Feb 11, 2004
Messages
25
CraigM said:
It sounds like the macro recorder would be a good place to start. Record the actions you want using tools>macros>Record New Macro. Then Step into the code, and try to understand it, and play around with it.

Hi CraigM
Yeah, I did play around. The problem is the end > Save As. I would like to have a box where I can type the new name, and press OK. Every week I have to make a copy of the Blank Trip and give a trip number to it. I'm sorry if it is a simple thing, I just can't get it, yet.
Thank you
 

CraigM

Active Member
Joined
Feb 27, 2003
Messages
320
Hi,

I assume that somewhere in your recorded code you get something like this:

ActiveWorkbook.SaveAs Filename:= _
"G:\MyFolder\MySpreadsheet" _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

Replace it with:

sPath = "G:\MyFolder\"
sFileName = InputBox("Type the name of the file:", "Save As")
ActiveWorkbook.SaveAs Filename:= sPath & sFileName _
, FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False


You'll need to change the path name that I've made up, but make sure you include the \ at the end of it. When run, this should then give you an input box to type the new name, and then save it in the specified folder.
 

Laszlo

New Member
Joined
Feb 11, 2004
Messages
25

ADVERTISEMENT

Hi CraigM,

Thanks for the help, it did what I wanted. :pray:
I like the inbox messages, so I would like to use one more. So far I could get the result on my own. After I opened the "Blank trip" file I want to copy some data from cells of another workbook's sheet. When I put the location I would like to have a messagebox coming up asking for the folder name and the workbook name where the data can be find to copy the cells to my "Blank trip". :confused:
Thanks!
Laz
 

CraigM

Active Member
Joined
Feb 27, 2003
Messages
320
If I understand you correctly I think the following code may be better than typing the file name and path of the file you want to open.

FileToOpen = Application.GetOpenFilename("Microsoft Excel Files(*.xls),*.xls")
If FileToOpen <> False Then Workbooks.Open (FileToOpen)

This shows the usual open box where you can select a file to open and assigns the name of the selected file to the variable FileToOpen. The second line checks that FileToOpen is not false (this happens if you cancel, instead of selectinf a file) and then opens the file. There is much less room for error, and it's faster than having to type the file name and path.
 

Laszlo

New Member
Joined
Feb 11, 2004
Messages
25

ADVERTISEMENT

Hi CraigM,

Thank you so much for your help. Now it seems I have the macro working the way I want. In the next few days I finalize my project, hopefully without any problem. (y)
Laz
 

north19701

Active Member
Joined
Jun 17, 2003
Messages
491
Code:
Sub Macro11()
Application.Dialogs(xlDialogSaveAs).Show
PthandFile = ActiveWorkbook.FullName
End Sub

Also, you could use the dialogs method, which will bring up the "Save As" option. This could give you more freedom to decide where to save the file. In addition, you can use a variable to get the path and file name for future reference.
 

Laszlo

New Member
Joined
Feb 11, 2004
Messages
25
Hi,
I though to "polish" my macro will be easy. :confused: Unfortunately not. I used the code
Application.ScreenUpdate=False/True
which didn't give me the result I need.
The macro opens two workbooks, links the range of cells, saves one under a new name. But I don't want to see anything on the screen just the InputBoxes and the MessageBoxes. How can I do it? Please help!!!
Laz
 

Watch MrExcel Video

Forum statistics

Threads
1,123,489
Messages
5,601,982
Members
414,489
Latest member
Xlambda

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top