Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Automate Copy and Paste
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 258
Default 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

Reply With QuoteReply With Quote
  #2   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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

Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 258
Default 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

Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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

Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 258
Default 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

Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default 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



Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Hybrid Mode Hybrid Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Automate copy / paste PAULGOR via OfficeKB.com Excel Discussion (Misc queries) 1 December 24th 09 03:38 PM
automate copy and paste Richard Excel Discussion (Misc queries) 1 September 23rd 08 11:32 PM
Any (clever) way to automate ROW:COL in copy/paste??? Zilla[_2_] Excel Worksheet Functions 7 March 26th 07 02:30 AM
Automate Cut/Copy/Paste from Excel Cells Do it the Easy Way Excel Programming 0 March 21st 06 04:14 AM
HELP....Is there a way to automate copy/paste special/transpose - Mel Excel Worksheet Functions 7 March 28th 05 11:17 PM


All times are GMT +1. The time now is 03:14 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement