OZmium Sports Betting and Horse Racing Forums

OZmium Sports Betting and Horse Racing Forums (http://forums.ozmium.com.au/index.php)

- General Topics (http://forums.ozmium.com.au/forumdisplay.php?f=59)

- - Another Excel Question (http://forums.ozmium.com.au/showthread.php?t=26655)

Another Excel Question

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

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.

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

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

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.

Code:


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