Macro

[Laszlo]

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.
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

 

[CraigM]

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]

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]

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]

Hi CraigM,

Thanks for the help, it did what I wanted. ray:
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".
Thanks!
Laz

 

[CraigM]

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]

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.
Laz

 

[north19701]

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]

Hi,
I though to "polish" my macro will be easy. 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