Home |
Search |
Today's Posts |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Working with named ranges
Use continuation character (space and underline) to correct word wrap. _ Sub copytoworkbook() For i = 1 To 4 ActiveSheet.Rows(i).Copy _ Workbooks("destination.xls").Sheets("sheet3").Rows (i) Next i End Sub -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... Try changing this example to suit your needs. WithOUT any selections it copies rows 1-4 from the active sheet to rows 1-4 of sheet 3 in the destination workbook. Sub copytoworkbook() For i = 1 To 4 ActiveSheet.Rows(i).Copy Workbooks("destination.xls").Sheets("sheet3").Rows (i) Next i End Sub -- Don Guillett SalesAid Software "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 | |||
Hyperlink to named ranges and sheets in Excel not working in 2007 | Excel Discussion (Misc queries) | |||
Named Ranges Not Working in Data Validation | Excel Worksheet Functions | |||
Working with named ranges | Excel Programming | |||
Copy data in named ranges to a newer version of the same template to identical ranges | Excel Programming | |||
Problem Working with Named Ranges | Excel Discussion (Misc queries) |