Automate Copy and Paste
Look closely. I lost a single quote on 2nd of the "'!R9C6". The wrong code
shows "!R9C6"
"maperalia" wrote:
Joel;
Thanks very much for the tip. I run it and I got the following error message:
Run-Time error'1004'
Application-defined or object-defined error
Then it is hightligthing at:
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R9C6"
Could you please tell me how can I fix this.
Thanks.
Maperalia
"Joel" wrote:
Try this
Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/3/2007 by
'
'
Dim startdate As String
Dim enddate As String
startdate InputBox("Enter Start Date")
enddate InputBox("Enter End Date")
DateString = startdate + " to " + enddate
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]" + DateString + "'!R9C6"
Range("C14").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "!R9C6"
Range("C15").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R11C3"
Range("C16").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R11C1"
Range("C17").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R11C6"
Range("C18:L19").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R11C4"
Range("E16").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R11C2"
Range("C21").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R9C6"
Range("C22").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R12C3"
Range("C23").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R12C1"
Range("C24").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R12C6"
Range("C25:L26").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R12C4"
Range("E23").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R12C2"
Range("C28").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R9C6"
Range("C29").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R13C3"
Range("C30").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R13C1"
Range("C31").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R13C6"
Range("C32:L33").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R13C4"
Range("E30").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R13C2"
Range("C35").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R9C6"
Range("C36").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R14C1"
Range("C37").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R14C1"
Range("C38").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R14C6"
Range("C39:L40").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R14C4"
Range("E37").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R14C2"
Range("C42").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R9C6"
Range("C43").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R15C3"
Range("C44").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R15C1"
Range("C45").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R15C6"
Range("C46:L47").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R15C4"
Range("E44").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R15C2"
Range("L7").Select
End Sub
"maperalia" wrote:
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
|