Sub UpdateSheet()

Dim SrcData(1 to 3) As String
Dim tgRange(1 to 3) as String
Dim sh as worksheet
Dim sh1 as Worksheet
Dim bk1 as Workbook
Dim i as Long

srcRange(1) = "A1:D3"
srcRange(2) = "A5:D16"
srcRange(3) = "A18:D22"
tgRange(1) = "A2"
tgRange(2) = "A8"
tgRange(3) = "A24"

set sh = Activesheet

'Open target workbook

set bk1 = Workbooks.Open(_
Filename:="C:\My Documents\Charts.xls", _
UpdateLinks:=0)

Set sh1 = bk1.worksheets("sheet1")


For i = 1 to 3
sh.Range(SrcRange(i)).copy
sh1.Range(tgRange(i)).PasteSpecial _
Paste:=xlPasteValues, _
Operation:=xlNone,
SkipBlanks:=False, _
Transpose:=False
Next i

End Sub

--
Regards,
Tom Ogilvy

"Samir_A" wrote in message
...
Hello All,

I'm a bit of a novice with Excel VBA and require a bit of help. I am
trying
to 'speed up' a macro which copies specific ranges of data from an active
workbook into specific location of a target workbook.

I start by naming the ranges (Source1, Source2, Target1, Target2 etc) then
the codes copies Source1 and pastes into Target1.

I thought rather then having to type out all the sperate copy and paste
commands I could create a temporary variable and use a loop to change the
range name (so Source1 is incremented to Source2 etc).

However the programs halts at the temporary variable giving me a run time
error 424 and the information "Object required".

Any assitance on what I am doing wrong will be most appreciated. I have
attached a shortened version of the code that I am using. I am working in
Excel 2003.

Thanks in advance.

The code:

Sub UpdateSheet()

Dim SrcData As String
SrcData = ActiveWindow.Caption

Dim srcRange1 As Excel.Range
Dim srcRange2 As Excel.Range
Dim srcRange3 As Excel.Range

Set srcRange1 = ActiveSheet.Range("A1:D3")
Set srcRange2 = ActiveSheet.Range("A5:D16")
Set srcRange3 = ActiveSheet.Range("A18:D22")

'Open target workbook

Workbooks.Open Filename:= _
"C:\My Documents\Charts.xls" _
, UpdateLinks:=0

Dim TargetFile As String
TargetFile = ActiveWindow.Caption

Dim TgSheet1 As String
Dim tgRange1 As String
Dim tgRange2 As String
Dim tgRange3 As String
Dim Source As String
Dim target As String


TgSheet1 = "Sheet1"
tgRange1 = "A2"
tgRange2 = "A8"
tgRange3 = "A24"

i = 1

Do While i < 4

Source = "srcRange" & i
target = "tgRange" & i

'Copy data range from source file
Windows(SrcData).Activate
'This is where the program halts
Source.Select
Source.Copy

'Paste data range in target file
Windows(TargetFile).Activate
Sheets(TgSheet1).Select
Range(Target).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

i = (i + 1)

Loop

End Sub