Greetings all!
I am trying to figure out (starting slowly here) how to use the VBA in excel. It seems that
we do a lot of the same thing everyday using a excel workbook to make reports, etc.
Here are my questions / goals:
I can plot a command button in excel using the toolbox...but how do I name the button?
Can a command be done to create a new workbook based off of a range given from the
current workbook? I only want to copy and paste a certain range...and the values of
each test block...no formulas. Can I use a template so report will not have to be formatted
my way every time? Concurrently, can this new workbook have a specific name given to
it and saved in a particular place on my C: drive?
These questions will get me started...I just think that we can save a lot of time with some automation.
Thank you all!!!
VR
DW
Full Version: Excel Custom Functions
The answer to most of the questions is yes.
One way to get started is to use the record macro function. This function writes VB code. Normally it is very specific to the actions you just performed, but often it is easy to edit them to be more general applications.
One way to get started is to use the record macro function. This function writes VB code. Normally it is very specific to the actions you just performed, but often it is easy to edit them to be more general applications.
Great thanks....
Can anyone help me with examples and such?
VR
DW
Can anyone help me with examples and such?
VR
DW
Here is an example of one of the things you requested. I got this code by turning on the record macro function and doing the actions to copy a range from sheet1 to sheet2 and past values only.
You could make this code more general by deleting the first line (the range.select statement). This will make the macro respond to whatever range is currently selected on the currently active sheet when is is run, and not just the specified range of cells that the macro recorded for you.
However, once the macro is running, you can't intervene. So it will select sheet2, and paste the values, I presume into whatever cell was originally selected in sheet two. For your application you might want to select a spcific target cell in the code.
Still this is quicker than writing it from scratch. It also teaches you things like what options go along with paste special (like xlPasteValues).
The recorder doesn't always write the most effiicient code, but it does wrie code that works. Eventually, you will learrn what you can "weed out" or tweak.
CODE
Sub Macro1()
'
' Macro1 Macro
'
'
Range("B4:E10").Select
Selection.Copy
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
'
' Macro1 Macro
'
'
Range("B4:E10").Select
Selection.Copy
Sheets("Sheet2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub
You could make this code more general by deleting the first line (the range.select statement). This will make the macro respond to whatever range is currently selected on the currently active sheet when is is run, and not just the specified range of cells that the macro recorded for you.
However, once the macro is running, you can't intervene. So it will select sheet2, and paste the values, I presume into whatever cell was originally selected in sheet two. For your application you might want to select a spcific target cell in the code.
Still this is quicker than writing it from scratch. It also teaches you things like what options go along with paste special (like xlPasteValues).
The recorder doesn't always write the most effiicient code, but it does wrie code that works. Eventually, you will learrn what you can "weed out" or tweak.
Daniel
Can you help us help you by giving some examples?
You've asked questions that can be regarded as a bit vague.
Like Dan said the answer to them is basically yes.
But do give specific code/advice we would need more information.
Can you help us help you by giving some examples?
You've asked questions that can be regarded as a bit vague.
Like Dan said the answer to them is basically yes.
But do give specific code/advice we would need more information.
Sorry for the vagueness...sometimes have a hard time explaining what I am trying to do.
Here is what I am trying to do..this is one of many daily tasks I am trying desperately to simplify.
On my workbook is a tab called Main. I would like to copy cells A2:AP36 from Main and paste the values and format of the cells (no formulas) into a new workbook. This workbook would then automatically save on the desktop as Report1_20071028...well, the current date in yyyymmdd format.
I hope I am explaining a little better
Thank you very much for the help!
Here is what I am trying to do..this is one of many daily tasks I am trying desperately to simplify.
On my workbook is a tab called Main. I would like to copy cells A2:AP36 from Main and paste the values and format of the cells (no formulas) into a new workbook. This workbook would then automatically save on the desktop as Report1_20071028...well, the current date in yyyymmdd format.
I hope I am explaining a little better
Thank you very much for the help!
Daniel
Thanks, that's helpful and should be pretty straightforward.
The only thing is, will it always be that range and where in the new workbook should it go?
Thanks, that's helpful and should be pretty straightforward.
The only thing is, will it always be that range and where in the new workbook should it go?
CODE
Sub Test()
Dim wbNew As Workbook
Dim wbThis As Workbook
Set wbThis = ThisWorkbook
Set wbNew = Workbooks.Add(xlWBATWorksheet)
ThisWorkbook.Sheets("Main").Range("A2:AP36").Copy
wbNew.Worksheets(1).Range("A1").PasteSpecial xlPasteValues
wbNew.Worksheets(1).Range("A1").PasteSpecial xlPasteFormats
wbNew.SaveAs wbThis.Path & "\Report_" & Format(Date, "yyyymmdd")
wbNew.Close
Application.CutCopyMode = False
End Sub
Dim wbNew As Workbook
Dim wbThis As Workbook
Set wbThis = ThisWorkbook
Set wbNew = Workbooks.Add(xlWBATWorksheet)
ThisWorkbook.Sheets("Main").Range("A2:AP36").Copy
wbNew.Worksheets(1).Range("A1").PasteSpecial xlPasteValues
wbNew.Worksheets(1).Range("A1").PasteSpecial xlPasteFormats
wbNew.SaveAs wbThis.Path & "\Report_" & Format(Date, "yyyymmdd")
wbNew.Close
Application.CutCopyMode = False
End Sub
Thank you so much for the timely reply!!
You are awesome
It can go on any tab in the new workbook...preferably the 1st tab (can we set the tab to be named Blue 2?
Additionally, how can I ensure the format will be the same. I have the row heights, column wodths set a specific way.
Thank you again for your help!
You are awesome
It can go on any tab in the new workbook...preferably the 1st tab (can we set the tab to be named Blue 2?
Additionally, how can I ensure the format will be the same. I have the row heights, column wodths set a specific way.
Thank you again for your help!
Daniel
Well the code I posted should deal with some of those issues.
It creates a workbook with 1 sheet, and the data is always copied to
that sheet.
It also copies the formatting, though I'm not 100% sure if it does the row heights
and column widths.
As to naming the sheet.
Well the code I posted should deal with some of those issues.
It creates a workbook with 1 sheet, and the data is always copied to
that sheet.
It also copies the formatting, though I'm not 100% sure if it does the row heights
and column widths.
As to naming the sheet.
CODE
wbNew.Worksheets(1).Name = "Blue 2"
Great...everything works fine except for the colum heights/widths.
Thank you so much for helping me...
Thanks again
Thank you so much for helping me...
Thanks again
Daniel
I thought they might be a problem.
There is another PasteSpecial option that deals with column widths.
But as far as I know there isn't anything for row heights.
I thought they might be a problem.
There is another PasteSpecial option that deals with column widths.
But as far as I know there isn't anything for row heights.
I can deal with the row heights...they are pretty close as default...but the column widths is what needs
to be addressed...
Any ideas?
Thank you so much for taking the time!
to be addressed...
Any ideas?
Thank you so much for taking the time!
Also wanted to add...sorry
Is there a way to add header and footer data.
I would like to put the name of report as header...and the as of date and time as a footer.
Thank you again!!
Is there a way to add header and footer data.
I would like to put the name of report as header...and the as of date and time as a footer.
Thank you again!!
As has been stated, use the Macro Recorder, then go into the Page Setup interface and set them up.
You'll end up with extra stuff, like this:
http://www.mrexcel.com/board2/viewtopic.ph...1422202#1422202
Note, you want the following properties:
But it's a heck of a start.
You'll end up with extra stuff, like this:
http://www.mrexcel.com/board2/viewtopic.ph...1422202#1422202
Note, you want the following properties:
CODE
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
But it's a heck of a start.
Daniel
Can you give more information on the data you actually want in this new workbook?
Also where is the name of the report you want in the header going to come from?
Any chance of attaching a sample workbook?
It might actually be easier to create a new workbook like this and then doing the paste special values.
Can you give more information on the data you actually want in this new workbook?
Also where is the name of the report you want in the header going to come from?
Any chance of attaching a sample workbook?
It might actually be easier to create a new workbook like this and then doing the paste special values.
CODE
ThisWorkbook.Worksheets("Main").Copy
Set wbNew = ActiveWorkbook
Set wbNew = ActiveWorkbook
Thanks!
The data is coming from a worksheet that is in my workbook. The worksheet is pretty big, and I only need a portion of it for this report...hence the reason I only need A2:AP36.
What is your suggestion....????
Also..when I run the Macro I can see the new workbook...is there anyway to make this transparent to the user?
Thanks!
The data is coming from a worksheet that is in my workbook. The worksheet is pretty big, and I only need a portion of it for this report...hence the reason I only need A2:AP36.
What is your suggestion....????
Also..when I run the Macro I can see the new workbook...is there anyway to make this transparent to the user?
Thanks!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.