TempoTours
Board Regular
- Joined
- Aug 23, 2007
- Messages
- 89
Hello, I have the following code as part of my macro. What I was wondering is a) Can anyone think of a way to shorten this? and b) I currently have this exact code copied 8 times, I did a find and replace to change 'if year = 2003 then' to the year 2010. What I would like is to have 1 code that will automatically change depending on the year the user inputs in a cell. (I should mention that 'Year' is a cell on my input sheet where the user types in the year they are currently working on and the macro automatically runs)
Any help/thoughts would be appreciated, thanks
Code:
'2003
Sheets("Cash Crops").Select
If Year = 2003 Then
' This copies the cash crop BPU's.
ActiveSheet.Unprotect
Workbooks.Open Filename:="F:\xdata\CAIS\CAIS spreadsheets\BPU's.xls"
Application.Goto reference:="Cash03"
Selection.Copy
Windows(Filename).Activate
ActiveWindow.SmallScroll Down:=21
Application.Goto reference:="CashCrops"
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-30
Range("A2").Select
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
' This copies the livestock BPU's
Sheets("Livestock").Select
ActiveWindow.ScrollRow = 1
ActiveSheet.Unprotect
Windows("BPU's.xls").Activate
Application.Goto reference:="Live03"
Selection.Copy
Windows(Filename).Activate
Application.Goto reference:="Livestock"
ActiveSheet.Paste
Range("A3").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
ActiveWindow.ScrollWorkbookTabs Sheets:=1
' This copies the supply managed cash crops BPU's.
Sheets("Sup Cash Crops").Select
ActiveSheet.Unprotect
Windows("BPU's.xls").Activate
Application.Goto reference:="SupCash03"
Selection.Copy
Windows(Filename).Activate
Application.Goto reference:="SupCashCrops"
ActiveSheet.Paste
Application.CutCopyMode = False
Range("B18").Select
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
' This copies the supply managed livestock BPU's.
Sheets("Sup Livestock").Select
ActiveSheet.Unprotect
Windows("BPU's.xls").Activate
Application.Goto reference:="SupLive03"
Selection.Copy
Windows(Filename).Activate
ActiveWindow.SmallScroll Down:=24
Application.Goto reference:="suplivestock"
ActiveSheet.Paste
ActiveWindow.SmallScroll Down:=-33
Range("A3").Select
Application.CutCopyMode = False
ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
ActiveWindow.ScrollWorkbookTabs Position:=xlFirst
Sheets("Cash Crops").Select
Range("A2").Select
Windows("BPU's.xls").Activate
ActiveWindow.Close
Any help/thoughts would be appreciated, thanks