M
maperalia
How can I automate the program that is copy and paste (see program below). In
order to run it I have to change the date of the time sheet each time
(04-01-07 to 04-15-07) then Unfortunately, I have to repeat this copy and
paste process each time I want to create my time card.
Thanks.
Maperalia
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/3/2007 by
'
'
ChDir "H:\Mario' TimeCards\Mario's Daily Timecard"
Workbooks.Open Filename:= _
"H:\Mario' TimeCards\Mario's Daily Timecard\Mario's Daily Timecard
(Test).xls"
Windows("Mario's Time Sheet.xls").Activate
Application.WindowState = xlMaximized
Windows("Mario's Daily Timecard (Test).xls").Activate
Range("G7").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R9C6"
Range("C14").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R9C6"
Range("C15").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R11C3"
Range("C16").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R11C1"
Range("C17").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R11C6"
Range("C18:L19").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R11C4"
Range("E16").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R11C2"
Range("C21").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R9C6"
Range("C22").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R12C3"
Range("C23").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R12C1"
Range("C24").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R12C6"
Range("C25:L26").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R12C4"
Range("E23").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R12C2"
Range("C28").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R9C6"
Range("C29").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R13C3"
Range("C30").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R13C1"
Range("C31").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R13C6"
Range("C32:L33").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R13C4"
Range("E30").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R13C2"
Range("C35").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R9C6"
Range("C36").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R14C1"
Range("C37").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R14C1"
Range("C38").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R14C6"
Range("C39:L40").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R14C4"
Range("E37").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R14C2"
Range("C42").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R9C6"
Range("C43").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R15C3"
Range("C44").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R15C1"
Range("C45").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R15C6"
Range("C46:L47").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R15C4"
Range("E44").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R15C2"
Range("L7").Select
End Sub
order to run it I have to change the date of the time sheet each time
(04-01-07 to 04-15-07) then Unfortunately, I have to repeat this copy and
paste process each time I want to create my time card.
Thanks.
Maperalia
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/3/2007 by
'
'
ChDir "H:\Mario' TimeCards\Mario's Daily Timecard"
Workbooks.Open Filename:= _
"H:\Mario' TimeCards\Mario's Daily Timecard\Mario's Daily Timecard
(Test).xls"
Windows("Mario's Time Sheet.xls").Activate
Application.WindowState = xlMaximized
Windows("Mario's Daily Timecard (Test).xls").Activate
Range("G7").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R9C6"
Range("C14").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R9C6"
Range("C15").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R11C3"
Range("C16").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R11C1"
Range("C17").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R11C6"
Range("C18:L19").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R11C4"
Range("E16").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R11C2"
Range("C21").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R9C6"
Range("C22").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R12C3"
Range("C23").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R12C1"
Range("C24").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R12C6"
Range("C25:L26").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R12C4"
Range("E23").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R12C2"
Range("C28").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R9C6"
Range("C29").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R13C3"
Range("C30").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R13C1"
Range("C31").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R13C6"
Range("C32:L33").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R13C4"
Range("E30").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R13C2"
Range("C35").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R9C6"
Range("C36").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R14C1"
Range("C37").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R14C1"
Range("C38").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R14C6"
Range("C39:L40").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R14C4"
Range("E37").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R14C2"
Range("C42").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R9C6"
Range("C43").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R15C3"
Range("C44").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R15C1"
Range("C45").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R15C6"
Range("C46:L47").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R15C4"
Range("E44").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]04-01-07 to 04-15-07'!R15C2"
Range("L7").Select
End Sub