Empty Column to Copy and Paste

[waderw]

Ok...I have code to search for a specified column heading. Once it is found a function to copy and paste the data into a selected cell in another workbook. I am getting a "Run Time error" because the column is completely empty and it is selecting all the way down to the very last row in Excel and trying to paste it when there are not enough rows to paste. Sometimes it may have values and sometimes it may be empty. Any ideas or code suggestions that might prevent or check to seee if the selection is empty? Here is my copy and paste function.

Thanks,
Robert

--------------------
Function CopyandPasteData()
Dim Top, Bottom As String

Top = ActiveCell.Address
Selection.End(xlDown).Select
Bottom = ActiveCell.Address
Range(Top, Bottom).Select
Selection.Copy
Workbooks(XlS1).Activate
ActiveSheet.Paste

End Function

[Ivan F Moala]

On 2002-05-21 20:25, waderw wrote:
Ok...I have code to search for a specified column heading. Once it is found a function to copy and paste the data into a selected cell in another workbook. I am getting a "Run Time error" because the column is completely empty and it is selecting all the way down to the very last row in Excel and trying to paste it when there are not enough rows to paste. Sometimes it may have values and sometimes it may be empty. Any ideas or code suggestions that might prevent or check to seee if the selection is empty? Here is my copy and paste function.

Thanks,
Robert

--------------------
Function CopyandPasteData()
Dim Top, Bottom As String

Top = ActiveCell.Address
Selection.End(xlDown).Select
Bottom = ActiveCell.Address
Range(Top, Bottom).Select
Selection.Copy
Workbooks(XlS1).Activate
ActiveSheet.Paste

End Function

Hi waderw
This may work for you;
Your'll also note that I have Dim your Top
variable as a String as the way you have
declared it is in fact a Variant.

Function CopyandPasteData()

Dim Top As String, Bottom As String



Top = ActiveCell.Address

Selection.End(xlDown).Select

Bottom = ActiveCell.Address



If Bottom = Range(Bottom).End(xlDown).Address Then Exit Function



Range(Top, Bottom).Copy

Workbooks(XlS1).Activate

ActiveSheet.Paste



End Function

[waderw]

That worked!

Thanks!!!