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
  #7   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'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 QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 9,101
Default Automate Copy and Paste
change that line back to the original line

Windows("Mario's Daily Timecard (Test).xls").Activate


when I tested the code I made some minort changes and forgot to put this
line back to original format.

"maperalia" wrote:

Joel;
Thanks very much for the tip. I run it and I got the following error message:
Run-Time error'424'
Object Requiredr

Then it is hightligthing at:
Windows(WB.Name).Activate

I wonder if you have a e-mail address where I can send both files so you can
test it.

Thanks.

Maperalia


"Joel" wrote:

I think this is now right

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/3/2007 by
'

'
Dim startdate As String
Dim enddate As String

startdate InputBox("Enter Start Date")
enddate InputBox("Enter End Date")

DateString = startdate + " to " + enddate

ChDir "H:\Mario' TimeCards\Mario's Daily Timecard"
Workbooks.Open Filename:= _
"H:\Mario' TimeCards\Mario's Daily Timecard\Mario's Daily Timecard " _
+ "(Test).xls"

Windows("Mario's Time Sheet.xls").Activate

Application.WindowState = xlMaximized

Windows(WB.Name).Activate

Range("G7").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R9C6"
Range("C14").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R9C6"
Range("C15").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R11C3"
Range("C16").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R11C1"
Range("C17").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R11C6"
Range("C18:L19").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R11C4"
Range("E16").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R11C2"


Range("C21").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R9C6"
Range("C22").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R12C3"
Range("C23").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R12C1"
Range("C24").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R12C6"
Range("C25:L26").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R12C4"
Range("E23").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R12C2"


Range("C28").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R9C6"
Range("C29").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R13C3"
Range("C30").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R13C1"
Range("C31").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R13C6"
Range("C32:L33").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R13C4"
Range("E30").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R13C2"


Range("C35").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R9C6"
Range("C36").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R14C1"
Range("C37").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R14C1"
Range("C38").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R14C6"
Range("C39:L40").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R14C4"
Range("E37").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R14C2"


Range("C42").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R9C6"
Range("C43").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R15C3"
Range("C44").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R15C1"
Range("C45").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R15C6"
Range("C46:L47").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R15C4"
Range("E44").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R15C2"


Range("L7").Select
End Sub



Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 258
Default Automate Copy and Paste
Joel;
Thanks for the e-mail. I really appreciatte your sending the macro.
Regarding the windows message I found the problem I was typing the date the
did not match with the excel tag. So I typed it exactly as was typed on the
tag and I do not have the error message anymore.

About the macro, I renamed the excel tag as you described to create a new
worksheet. However, after I click the macro is doing nothing. Did I miss
something?

Thanks.
Maperalia

"Joel" wrote:

Email me the file and I will look at it later.


"maperalia" wrote:

Joel;
I did not get any error message. However, Ii is showing a window "Update 7"
for each cell. Is there is any way to fix thios please?.
Thanks.
Maperalia

"Joel" wrote:

change that line back to the original line

Windows("Mario's Daily Timecard (Test).xls").Activate


when I tested the code I made some minort changes and forgot to put this
line back to original format.

"maperalia" wrote:

Joel;
Thanks very much for the tip. I run it and I got the following error message:
Run-Time error'424'
Object Requiredr

Then it is hightligthing at:
Windows(WB.Name).Activate

I wonder if you have a e-mail address where I can send both files so you can
test it.

Thanks.

Maperalia


"Joel" wrote:

I think this is now right

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/3/2007 by
'

'
Dim startdate As String
Dim enddate As String

startdate InputBox("Enter Start Date")
enddate InputBox("Enter End Date")

DateString = startdate + " to " + enddate

ChDir "H:\Mario' TimeCards\Mario's Daily Timecard"
Workbooks.Open Filename:= _
"H:\Mario' TimeCards\Mario's Daily Timecard\Mario's Daily Timecard " _
+ "(Test).xls"

Windows("Mario's Time Sheet.xls").Activate

Application.WindowState = xlMaximized

Windows(WB.Name).Activate

Range("G7").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R9C6"
Range("C14").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R9C6"
Range("C15").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R11C3"
Range("C16").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R11C1"
Range("C17").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R11C6"
Range("C18:L19").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R11C4"
Range("E16").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R11C2"


Range("C21").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R9C6"
Range("C22").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R12C3"
Range("C23").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R12C1"
Range("C24").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R12C6"
Range("C25:L26").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R12C4"
Range("E23").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R12C2"


Range("C28").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R9C6"
Range("C29").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R13C3"
Range("C30").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R13C1"
Range("C31").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R13C6"
Range("C32:L33").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R13C4"
Range("E30").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R13C2"


Range("C35").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R9C6"
Range("C36").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R14C1"
Range("C37").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R14C1"
Range("C38").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R14C6"
Range("C39:L40").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R14C4"
Range("E37").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R14C2"


Range("C42").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R9C6"
Range("C43").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R15C3"
Range("C44").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R15C1"
Range("C45").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R15C6"
Range("C46:L47").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R15C4"
Range("E44").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R15C2"


Range("L7").Select
End Sub



Reply With QuoteReply With Quote
  #10   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 258
Default Automate Copy and Paste
Joel;
Thanks very much for your quick response and attention. I tested it and it
is working perfectly!!!!.
However, I wonder why the program is just taken information from "4/1/07 to
"4/14/07". I created another sheet "4/15/07 to "4/28/07" and it is not taken
this sheet just the "4/1/07 to "4/14/07" instead.

Could you please help me also to make it work for different columns? The
macro just select information from the date typed on "G9" and the hours for
this date which are located in "G11,G12,G13,G14, and G15" and the data from
"A11:D15". I do not know if is possible the program to ask me to select the
timecard's date I want to print that could be one of thes following cells:
"E9,F9,G9,H9,I9,J9,K9,L9,M9,N9,O9,P9,Q9,R9,or S9" .
Most of time the number of rows below these dates varies. I could have one
row or seven rows of data which is the limit.

I sorry to ask you so many questions. Perhaps it will take too much time of
your valuable time. If you believe is too much of work do not worry.


Thanks again.


"Joel" wrote:

Email me the file and I will look at it later.


"maperalia" wrote:

Joel;
I did not get any error message. However, Ii is showing a window "Update 7"
for each cell. Is there is any way to fix thios please?.
Thanks.
Maperalia

"Joel" wrote:

change that line back to the original line

Windows("Mario's Daily Timecard (Test).xls").Activate


when I tested the code I made some minort changes and forgot to put this
line back to original format.

"maperalia" wrote:

Joel;
Thanks very much for the tip. I run it and I got the following error message:
Run-Time error'424'
Object Requiredr

Then it is hightligthing at:
Windows(WB.Name).Activate

I wonder if you have a e-mail address where I can send both files so you can
test it.

Thanks.

Maperalia


"Joel" wrote:

I think this is now right

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 4/3/2007 by
'

'
Dim startdate As String
Dim enddate As String

startdate InputBox("Enter Start Date")
enddate InputBox("Enter End Date")

DateString = startdate + " to " + enddate

ChDir "H:\Mario' TimeCards\Mario's Daily Timecard"
Workbooks.Open Filename:= _
"H:\Mario' TimeCards\Mario's Daily Timecard\Mario's Daily Timecard " _
+ "(Test).xls"

Windows("Mario's Time Sheet.xls").Activate

Application.WindowState = xlMaximized

Windows(WB.Name).Activate

Range("G7").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R9C6"
Range("C14").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R9C6"
Range("C15").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R11C3"
Range("C16").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R11C1"
Range("C17").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R11C6"
Range("C18:L19").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R11C4"
Range("E16").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R11C2"


Range("C21").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R9C6"
Range("C22").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R12C3"
Range("C23").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R12C1"
Range("C24").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R12C6"
Range("C25:L26").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R12C4"
Range("E23").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R12C2"


Range("C28").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R9C6"
Range("C29").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R13C3"
Range("C30").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R13C1"
Range("C31").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R13C6"
Range("C32:L33").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R13C4"
Range("E30").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R13C2"


Range("C35").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R9C6"
Range("C36").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R14C1"
Range("C37").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R14C1"
Range("C38").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R14C6"
Range("C39:L40").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R14C4"
Range("E37").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R14C2"


Range("C42").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R9C6"
Range("C43").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R15C3"
Range("C44").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R15C1"
Range("C45").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R15C6"
Range("C46:L47").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R15C4"
Range("E44").Select
ActiveCell.FormulaR1C1 = _
"='[Mario''s Time Sheet.xls]" + DateString + "'!R15C2"


Range("L7").Select
End Sub



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

Advanced Search
Display Modes
Linear Mode Linear 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 05:58 PM.

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

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement