OZmium Sports Betting and Horse Racing Forums

17th June 2013, 07:22 PM

Dennis G

Member

Join Date: Mar 2006

Posts: 112

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

18th June 2013, 07:50 AM

Chrome Prince Chrome Prince is offline

Member

Join Date: Jan 1970

Posts: 4,365

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.

__________________
RaceCensus - powerful system testing software.
Now with over 399,000 Metropolitan, Provincial and Country races!
http://www.propun.com.au/horse_raci...ng_systems.html
*RaceCensus now updated to 31/03/2024
Video overview of RaceCensus here:
http://www.youtube.com/watch?v=W821YP_b0Pg

19th June 2013, 05:17 PM

Dennis G

Member

Join Date: Mar 2006

Posts: 112

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

19th June 2013, 07:47 PM

Chrome Prince Chrome Prince is offline

Member

Join Date: Jan 1970

Posts: 4,365

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

3rd July 2013, 07:55 AM

Puntz

Member

Join Date: Jan 2012

Posts: 291

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

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

	To advertise on these forums, e-mail us.