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

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
>