|
Automate Copy and Paste
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 |
Automate Copy and Paste
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 |
Automate Copy and Paste
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 |
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 |
Automate Copy and Paste
Joel;
Thanks for your quick response. I adjusted the quote on 2nd of the "'!R9C6, however, I still getting the sample error message I sent you in my prevoius e-mail. Could you please you tell me how to fix it?. Thabks. Maperalia "Joel" wrote: 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 |
Automate Copy and Paste
I think this is now right
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(WB.Name).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 |
Automate Copy and Paste
Joel;
Thanks very much for the tip. I run it and I got the following error message: Run-Time error'424' Object Requiredr Then it is hightligthing at: Windows(WB.Name).Activate I wonder if you have a e-mail address where I can send both files so you can test it. Thanks. Maperalia "Joel" wrote: I think this is now right 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(WB.Name).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 |
Automate Copy and Paste
change that line back to the original line
Windows("Mario's Daily Timecard (Test).xls").Activate when I tested the code I made some minort changes and forgot to put this line back to original format. "maperalia" wrote: Joel; Thanks very much for the tip. I run it and I got the following error message: Run-Time error'424' Object Requiredr Then it is hightligthing at: Windows(WB.Name).Activate I wonder if you have a e-mail address where I can send both files so you can test it. Thanks. Maperalia "Joel" wrote: I think this is now right 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(WB.Name).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 |
Automate Copy and Paste
Joel;
Thanks very much for your quick response and attention. I tested it and it is working perfectly!!!!. However, I wonder why the program is just taken information from "4/1/07 to "4/14/07". I created another sheet "4/15/07 to "4/28/07" and it is not taken this sheet just the "4/1/07 to "4/14/07" instead. Could you please help me also to make it work for different columns? The macro just select information from the date typed on "G9" and the hours for this date which are located in "G11,G12,G13,G14, and G15" and the data from "A11:D15". I do not know if is possible the program to ask me to select the timecard's date I want to print that could be one of thes following cells: "E9,F9,G9,H9,I9,J9,K9,L9,M9,N9,O9,P9,Q9,R9,or S9" . Most of time the number of rows below these dates varies. I could have one row or seven rows of data which is the limit. I sorry to ask you so many questions. Perhaps it will take too much time of your valuable time. If you believe is too much of work do not worry. Thanks again. "Joel" wrote: Email me the file and I will look at it later. "maperalia" wrote: Joel; I did not get any error message. However, Ii is showing a window "Update 7" for each cell. Is there is any way to fix thios please?. Thanks. Maperalia "Joel" wrote: change that line back to the original line Windows("Mario's Daily Timecard (Test).xls").Activate when I tested the code I made some minort changes and forgot to put this line back to original format. "maperalia" wrote: Joel; Thanks very much for the tip. I run it and I got the following error message: Run-Time error'424' Object Requiredr Then it is hightligthing at: Windows(WB.Name).Activate I wonder if you have a e-mail address where I can send both files so you can test it. Thanks. Maperalia "Joel" wrote: I think this is now right 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(WB.Name).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 |
| All times are GMT +1. The time now is 09:37 AM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com