Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with named ranges
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Named Ranges Not Working in Data Validation | Excel Worksheet Functions | |||
Working with named ranges | Excel Programming | |||
Working with named ranges | Excel Programming | |||
Working with named ranges | Excel Programming | |||
Problem Working with Named Ranges | Excel Discussion (Misc queries) |