using a macro to change code

  • Thread starter Thread starter allen
  • Start date Start date
A

allen

If you post your code here we can add a little bit to it
which will give you the date in there

Allen
 
You can assign the sheet name to a string, and use the concatenate operator
to put it in formulas. For example:

strSheetName = "SMIS" & Format(Date, "YYYYMMDD")

ActiveChart.SetSourceData Source:=Sheets(strSheetName).Range("P2:R673"), _
PlotBy:=xlColumns

Also, you can assign the sheet object itself to a variable:

set wsSheet = sheets(strSheetName)

Which makes things even easier:

ActiveChart.SetSourceData Source:=wsSheet.Range("P2:R673"), _
PlotBy:=xlColumns


Also, code generated by the macro recorder should be viewed only as a
starting point. You can do much of what the code does below with WAY less
code; perhaps 1/3 or 1/4. It is not necessary to select cells before
operating on them, for example, and you could eliminate some code by
combining:

with ActiveChart
.ChartType = xlline
.SetSourceData Source:=wsSheet.Range("P2:R673") 'see above
etc
etc
etc
end with

Again, "ActiveChart" should be replaced by an object. I dislike using
ActiveAnything, because it relies on a selection that you understand now as
you create the code, but might break in 2 months when you revisit the code
and forget that it depended on a particular sheet being selected.
--
HTH -

-Frank
Microsoft Excel MVP
Dolphin Technology Corp.
http://vbapro.com
 
Mike,

If you have just one sheet in the workbook each time, this
is easy. Replace Sheets("SMIS20030912") by Sheets(1) each
time in your subroutine. It is just a different way to
refer to the sheet which does not involve the name.

If that doesn't work send me an email and I will give you
another solution.

Allen
 
Back
Top