Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
DataObject
Can anyone provide any help to me? I am trying to copy a dynamic range from
one workbook to another dynamic range workbook using the clipboard. The copy won't work because I don't know the destination because it is a dynamic range. I've typed the following code and the only thing that pastes is the word "True". If I take away the resize property it will copy the first field okay but I need the whole range. Is there anyway to do this? I'm using Excel 2003 and the XP Operating system. __________________________________________________ ________________ 'look at current spreadsheet to determine if the record has been processed by all departments. 'If there are initials in the 18th column that means the record has been completely processed. 'therefore the record will need to be removed from this spreadsheet and placed into the '"Archive_Cancel_Accounts workbook. Each record is 3 rows and 18 columns. Sub BAR_Archive_Procedure() Dim objArchive_Copy As New DataObject Dim Archive_Rec$ Workbooks("Cancel_Accounts_him.xls").Activate Sheets("BAR").Activate ActiveCell(6, 1).Activate x = Cells(Rows.Count, 1).End(xlUp).Row Set BAR_Range = Range(Cells(6, 1), Cells(x, 1)) For Each n In BAR_Range '"N" means for each row If n.Value Like "M*" Then 'checking to see if the record numbers match n.Offset(0, 17).Activate 'check that the done has initals in, which it means 'needs to be archived If ActiveCell < "" Then 'then initials exist and archive ActiveCell.Offset(0, -17).Activate 'resetting the cell to the start of record Archive_Rec$ = ActiveCell.Resize(3, 18).Cut 'resizing it to complete range of record objArchive_Copy.SetText Archive_Rec$ objArchive_Copy.PutInClipboard ActiveCell.Resize(3, 18).Delete Shift:=xlUp 'deleting record from master sheet Workbooks("Archive_Cancel_Accounts.xls").Activate Sheets("BAR_Arc").Activate 'make it current worksheet ActiveSheet.Range("a65536").End(xlUp).Activate 'find the next empty row If ActiveCell.Cells <= Worksheets("BAR_Arc").Cells(4, 1) Then ActiveSheet.Range("A6").Select Else Range("a65536").End(xlUp).Offset(3, 0).Select End If objArchive_Copy.GetFromClipboard ActiveSheet.Paste End If End If Next End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
DataObject
You only need to specify the upper left cell in the destination range when
copying from the clipboard. range("Name1").copy Destination:=range("Name2").Resize(1,1) as an example. -- Regards, Tom Ogilvy "Sheila" wrote: Can anyone provide any help to me? I am trying to copy a dynamic range from one workbook to another dynamic range workbook using the clipboard. The copy won't work because I don't know the destination because it is a dynamic range. I've typed the following code and the only thing that pastes is the word "True". If I take away the resize property it will copy the first field okay but I need the whole range. Is there anyway to do this? I'm using Excel 2003 and the XP Operating system. __________________________________________________ ________________ 'look at current spreadsheet to determine if the record has been processed by all departments. 'If there are initials in the 18th column that means the record has been completely processed. 'therefore the record will need to be removed from this spreadsheet and placed into the '"Archive_Cancel_Accounts workbook. Each record is 3 rows and 18 columns. Sub BAR_Archive_Procedure() Dim objArchive_Copy As New DataObject Dim Archive_Rec$ Workbooks("Cancel_Accounts_him.xls").Activate Sheets("BAR").Activate ActiveCell(6, 1).Activate x = Cells(Rows.Count, 1).End(xlUp).Row Set BAR_Range = Range(Cells(6, 1), Cells(x, 1)) For Each n In BAR_Range '"N" means for each row If n.Value Like "M*" Then 'checking to see if the record numbers match n.Offset(0, 17).Activate 'check that the done has initals in, which it means 'needs to be archived If ActiveCell < "" Then 'then initials exist and archive ActiveCell.Offset(0, -17).Activate 'resetting the cell to the start of record Archive_Rec$ = ActiveCell.Resize(3, 18).Cut 'resizing it to complete range of record objArchive_Copy.SetText Archive_Rec$ objArchive_Copy.PutInClipboard ActiveCell.Resize(3, 18).Delete Shift:=xlUp 'deleting record from master sheet Workbooks("Archive_Cancel_Accounts.xls").Activate Sheets("BAR_Arc").Activate 'make it current worksheet ActiveSheet.Range("a65536").End(xlUp).Activate 'find the next empty row If ActiveCell.Cells <= Worksheets("BAR_Arc").Cells(4, 1) Then ActiveSheet.Range("A6").Select Else Range("a65536").End(xlUp).Offset(3, 0).Select End If objArchive_Copy.GetFromClipboard ActiveSheet.Paste End If End If Next End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Error using DataObject to get clipboard data | Excel Discussion (Misc queries) | |||
DataObject in BeforeDragOver has no format | Excel Programming | |||
DataObject Error in Excel version 2002 | Excel Programming | |||
DataObject and cliboard | Excel Programming | |||
Can't Dim As DataObject?? | Excel Programming |