Copy a Macro to a new workbook with different file name

[cathyban]

Hello
I'm quite new to macros and use them rarely.
I have a workbook with several graphs in it that I want to update monthly. I've written a macro for the current workbook - and it successfully updates my graphs.

However each month I want to save this file as a new file (ie open previous month's file and Save As new month's file). When I do this though, the macro I recorded doesn't successfully copy across/ run.

In Visual Basics I know how to copy the module from the last month's file to the new month's file, but I think the problem is to do with the name of my workbooks

For example the macro was recorded in the file named 'Version 1', and I need this to change when I Save As a new month (i.e. I might call the next one 'Version 2' (so below Windows("Version 1") would need to change to Windows("Version 2") - is there something I can put in there i.e. 'Currentbook' or something so it will work?)

Can anyone please help!?
I've posted example of the code below


Columns("B:B").Select
Selection.Copy
Sheets("Historic Months' Data").Select
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Sheets("Board Report").Select
Columns("B:B").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Sheets("Graphs 12 Month ").Select
ActiveSheet.ChartObjects("Chart 14").Activate
ActiveWindow.Visible = False
Windows("Version 1").Activate
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = "='Board Report'!R1C2:R1C18"
ActiveChart.SeriesCollection(1).Values = "='Board Report'!R58C2:R58C18"
ActiveChart.SeriesCollection(2).XValues = "='Board Report'!R1C2:R1C18"
ActiveChart.SeriesCollection(2).Values = "='Board Report'!R71C2:R71C18"
ActiveChart.SeriesCollection(3).Values = "='Board Report'!R44C2:R44C18"
ActiveChart.SeriesCollection(4).Values = "='Board Report'!R124C2:R124C18"
ActiveWindow.Visible = False
Windows("Version 1").Activate
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = "='Board Report'!R1C2:R1C18"
ActiveChart.SeriesCollection(1).Values = "='Board Report'!R177C2:R177C18"
ActiveChart.SeriesCollection(2).XValues = "='Board Report'!R1C2:R1C18"
ActiveChart.SeriesCollection(2).Values = "='Board Report'!R196C2:R196C18"
ActiveChart.SeriesCollection(3).XValues = "='Board Report'!R1C2:R1C18"
ActiveChart.SeriesCollection(3).Values = "='Board Report'!R249C2:R249C18"
ActiveChart.SeriesCollection(4).XValues = "='Board Report'!R1C2:R1C18"
ActiveChart.SeriesCollection(4).Values = "='Board Report'!R250C2:R250C18"
Windows("Version 1").SmallScroll Down:=42
ActiveWindow.Visible = False
Windows("Version 1").Activate
ActiveSheet.ChartObjects("Chart 13").Activate
ActiveChart.PlotArea.Select
ActiveWindow.Visible = False
Windows("Version 1").Activate

 

[StructuredWater]

Hello cathyban,

Perhaps Windows(ThisWorkbook.Name). etc?

Kind regards,
JohnM

 

[cathyban]

Thanks for your suggestion! I'm not familar with ThisWorkbook (although have just been googling but still not sure if I understand....)

Is this how the macro should look??:

Many thanks again

Columns("B:B").Select
Selection.Copy
Sheets("Historic Months' Data").Select
Selection.Insert Shift:=xlToRight
Sheets("Board Report").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Sheets("Graphs 12 Month ").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.PlotArea.Select
ActiveChart.SeriesCollection(1).XValues = "='Board Report'!R1C2:R1C18"
ActiveChart.SeriesCollection(1).Values = "='Board Report'!R58C2:R58C18"
ActiveChart.SeriesCollection(2).XValues = "='Board Report'!R1C2:R1C18"
ActiveChart.SeriesCollection(2).Values = "='Board Report'!R71C2:R71C18"
ActiveChart.SeriesCollection(3).Values = "='Board Report'!R44C2:R44C18"
ActiveChart.SeriesCollection(4).Values = "='Board Report'!R124C2:R124C18"
ActiveWindow.Visible = False
Windows(ThisWorkbook.Name).Activate
ActiveSheet.ChartObjects("Chart 2").Activate
ActiveChart.ChartArea.Select
ActiveChart.SeriesCollection(1).XValues = "='Board Report'!R1C2:R1C18"
ActiveChart.SeriesCollection(1).Values = "='Board Report'!R177C2:R177C18"
ActiveChart.SeriesCollection(2).XValues = "='Board Report'!R1C2:R1C18"
ActiveChart.SeriesCollection(2).Values = "='Board Report'!R196C2:R196C18"
ActiveChart.SeriesCollection(3).XValues = "='Board Report'!R1C2:R1C18"
ActiveChart.SeriesCollection(3).Values = "='Board Report'!R249C2:R249C18"
ActiveChart.SeriesCollection(4).XValues = "='Board Report'!R1C2:R1C18"
ActiveChart.SeriesCollection(4).Values = "='Board Report'!R250C2:R250C18"

 

[xenou]

Typically you don't need to worry about the workbook name unless your code is operating in more than one workbook at the same time. Is there two (or even three workbooks) involved when this code runs? If so, what are they and what does each workbook do?

ξ

 

[cathyban]

Hi Xenou, thanks for your reply.

The code should only run with this workbook involved I think.
What I'd like the macro to do is: delete a column from a worksheet and move it to a new worksheet. This works fine if I save the workbook as a new file.
The problem is when the macro gets a bit more complicated - i.e. I want to change the series reference in a number of graphs (on a different worksheet within the same book) once I've copied & pasted an old month to a new worksheet.
The series of the graphs are all in the same workbook (I'm just trying to change the months shown on the graph - old month gone new month in - which happens when the column is copy & pasted).


thanks!!