Another Excel Question

[Dennis G]

To the Excel Exspurts,
I'm looking to write a macro to transfer a bank of cells with formulae from a master WS '_TABLES.xlsm' to the current WS. Where the current WS can be named anywhere between '12-01-01.xlsm' to '12-12-31.xlms'.

I suppose I'm looking for a generic label to call to the CURRENT Ws whichever that may be.

Any one got any ideas?
Thanks,
Dennis

[Chrome Prince]

Dennis,

Sub Test1()
On Error Resume Next
Sheets(ActiveSheet.Index + 1).Activate
If err.number <> 0 Then Sheets(1).Activate
End Sub

This will move to the next sheet without the need for incorporating the name in the macro. It has error checking to prevent runtime errors when it gets to the last sheet in the workbook.

[Dennis G]

Thanks Mate,
but this is doing my head in...
I need to add another macro to each WB anyway so it's just as easy to do the washing by hand....

Den

[Chrome Prince]

Sorry Den, I thought it was worksheets within one workbook, not various workbooks. My mistake.

[Puntz]

Ok,
this is a start, but can be tweaked as required

What you need to test this is 2 Workbooks
Name the 1stWBk;
MASTER_WORKBOOK.xlsm
and place the macro code shown below in that WB

Name the 2nd WBk;
CURRENT_WORKBOOK.xlsm

The code assumes the WBk's are are in C:\

Use the F8 key to step through the macro for testing.



Sub Transfer_Test()

Application.DisplayAlerts = False

Workbooks.Open Filename:="C:\CURRENT_WORKBOOK.xlsm"
Windows("MASTER_WORKBOOK.xlsm").Activate
Sheets("Sheet1").Select
Sheets("Sheet1").Copy Before:=Workbooks("CURRENT_WORKBOOK.xlsm").Sheets(1)
Application.WindowState = xlMinimized
ActiveCell.FormulaR1C1 = ""
ActiveWorkbook.SaveAs Filename:= _
"C:\CURRENT_WORKBOOK.xlsm", FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False
ActiveWindow.Close
ActiveWorkbook.Save

Application.DisplayAlerts = True

End Sub