PC Review
Home Forums Reviews Articles Register
Member Login:

PC Review > Forums > Newsgroups > Microsoft Excel > Microsoft Excel Programming > Automate Copy and Paste

Reply
Page 1 of 2 1 2
Thread Tools Rate Thread

Automate Copy and Paste

 
 
=?Utf-8?B?bWFwZXJhbGlh?=
Guest
Posts: n/a
 
      9th Apr 2007
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
 
Reply With Quote
 
 
 
 
=?Utf-8?B?Sm9lbA==?=
Guest
Posts: n/a
 
      9th Apr 2007
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
>
 
Reply With Quote
 
=?Utf-8?B?bWFwZXJhbGlh?=
Guest
Posts: n/a
 
      9th Apr 2007
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
> >
 
Reply With Quote
 
=?Utf-8?B?Sm9lbA==?=
Guest
Posts: n/a
 
      10th Apr 2007
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
> > >
 
Reply With Quote
 
=?Utf-8?B?bWFwZXJhbGlh?=
Guest
Posts: n/a
 
      10th Apr 2007
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
> > > >
 
Reply With Quote
 
=?Utf-8?B?Sm9lbA==?=
Guest
Posts: n/a
 
      10th Apr 2007
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
 
Reply With Quote
 
=?Utf-8?B?bWFwZXJhbGlh?=
Guest
Posts: n/a
 
      10th Apr 2007
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
>
>
>
 
Reply With Quote
 
=?Utf-8?B?Sm9lbA==?=
Guest
Posts: n/a
 
      10th Apr 2007
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
> >
> >
> >
 
Reply With Quote
 
=?Utf-8?B?bWFwZXJhbGlh?=
Guest
Posts: n/a
 
      10th Apr 2007
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
> > >
> > >
> > >
 
Reply With Quote
 
=?Utf-8?B?Sm9lbA==?=
Guest
Posts: n/a
 
      10th Apr 2007
Email me the file and I will look at it later.
(E-Mail Removed)

"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
> > > >
> > > >
> > > >
 
Reply With Quote
 
 
 
Reply
Page 1 of 2 1 2

« Keep getting Errors in Code | scroll to activecell? »
Thread Tools
Rate This Thread
Rate This Thread:

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Automate Copy and Paste? Quin Microsoft Excel Programming 10 16th Feb 2010 06:42 AM
automate copy and paste Richard Microsoft Excel Misc 1 23rd Sep 2008 11:32 PM
Any (clever) way to automate ROW:COL in copy/paste??? Zilla Microsoft Excel Worksheet Functions 7 26th Mar 2007 03:14 AM
Automate Cut/Copy/Paste from Excel Cells Do it the Easy Way Microsoft Excel Programming 0 21st Mar 2006 04:14 AM
automate copy paste row to new book saint Microsoft Excel Programming 0 15th Feb 2006 12:11 PM


Features
Forums Startup Database Reviews Articles FAQ / Guidelines About Us
 

Advertising
 

Newsgroups
Windows Vista Windows XP Microsoft Word Microsoft DotNet Windows 2000 AntiSpyware Microsoft Access Microsoft Excel Microsoft Outlook Hardware
 


All times are GMT +1. The time now is 12:56 AM.
Powered by vBulletin®. Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
SEO by vBSEO ©2010, Crawlability, Inc.
Contact Us - Archive - Privacy Statement - Top