ajrob
06-13-2008, 05:39 PM
I'm hoping this is an easy one...
I've got multiple tabs named in a workbook called:
> 2008 Ladder
> 2009 Ladder
> 2010 Ladder
> etc.
I've written some basic code that copies a section from the 2008 Ladder tab, and pastes (as a value), to a tab called OpenProv1.
The structure of each of the tabs above is the same, and would I'd like to do is to specify which one to copy from. My thought was to put in a Message Box asking for the Year, then concatenating YYYY + Ladder, and referencing that in the code. But, any other ideas would be welcome.
Here's the code I've written, would appreciate any tips.
Thanks
' Copies info contained in 2007 Ladder, then
'formats contents in the "OpenProv1" worksheet.
Sheets("2008 Ladder").Select
Range("B9:J500").Select
Selection.Copy
Range("B9").Select
Sheets("OpenProv1").Select
Range("B4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("2008 Ladder").Select
Range("Q9:V500").Select
Selection.Copy
Range("B9").Select
Sheets("OpenProv1").Select
Range("L4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("2008 Ladder").Select
Range("Y9:AI500").Select
Selection.Copy
Range("B9").Select
Sheets("OpenProv1").Select
Range("S4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("2008 Ladder").Select
Range("DC9C500").Select
Selection.Copy
Range("B9").Select
Sheets("OpenProv1").Select
Range("AE4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("2008 Ladder").Select
Range("DG9R500").Select
Selection.Copy
Range("B9").Select
Sheets("OpenProv1").Select
Range("AG4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Rows("4:500").Select
With Selection.Font
.Name = "Arial Narrow"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.ColorIndex = 1
End With
I've got multiple tabs named in a workbook called:
> 2008 Ladder
> 2009 Ladder
> 2010 Ladder
> etc.
I've written some basic code that copies a section from the 2008 Ladder tab, and pastes (as a value), to a tab called OpenProv1.
The structure of each of the tabs above is the same, and would I'd like to do is to specify which one to copy from. My thought was to put in a Message Box asking for the Year, then concatenating YYYY + Ladder, and referencing that in the code. But, any other ideas would be welcome.
Here's the code I've written, would appreciate any tips.
Thanks
' Copies info contained in 2007 Ladder, then
'formats contents in the "OpenProv1" worksheet.
Sheets("2008 Ladder").Select
Range("B9:J500").Select
Selection.Copy
Range("B9").Select
Sheets("OpenProv1").Select
Range("B4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("2008 Ladder").Select
Range("Q9:V500").Select
Selection.Copy
Range("B9").Select
Sheets("OpenProv1").Select
Range("L4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("2008 Ladder").Select
Range("Y9:AI500").Select
Selection.Copy
Range("B9").Select
Sheets("OpenProv1").Select
Range("S4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("2008 Ladder").Select
Range("DC9C500").Select
Selection.Copy
Range("B9").Select
Sheets("OpenProv1").Select
Range("AE4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Sheets("2008 Ladder").Select
Range("DG9R500").Select
Selection.Copy
Range("B9").Select
Sheets("OpenProv1").Select
Range("AG4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Rows("4:500").Select
With Selection.Font
.Name = "Arial Narrow"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.ColorIndex = 1
End With