Try dim source and target as range, NOT string please

cheers!

Samir_A wrote:
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