Trying to copy a row of data from one worksheet to another, after
getting the location of the data to be copied from the user thru a
userform. But I'm getting "method range of object worksheet failed"
message.

Option Explicit
Public bHdr As Boolean
Public lTop As Long
Public rFirstData As Range
Public lLastCol As Long
Public lNumbrCol As Long


Sub Extr10L()
Dim wbCtyData As Workbook
Dim oWS As Object
Dim wsTop10List As Worksheet
Dim wsCtyData As Worksheet
Dim lFirstDataRow As Long
Dim lArea1FirstRow As Long
Dim lArea2FirstRow As Long
Dim lArea3FirstRow As Long
Dim lHdrRow As Long
Dim lFirstDataCol As Long
Dim wsTop As Worksheet
Dim rCtyDataHdr As Range

Set wsTop10List = ThisWorkbook.Worksheets("CtyLst")
Set wsCtyData = ActiveSheet
Set wbCtyData = ActiveWorkbook

'Test is Mark Top 10 workbook is active
If ActiveWorkbook.Name = ThisWorkbook.Name Then
MsgBox "You have selected the workbook that contains the macro." &
_
Chr(13) & "Please click Ok and select the correct workbook and " &
_
Chr(13) & "worksheet and restart the macro.", vbOKOnly
Exit Sub
End If

'TEST for existing sheet named "Top"
For Each oWS In wbCtyData.Sheets
If oWS.Name = "Top" Then
If MsgBox("A worksheet named Top already exists in this
workbook." _
& Chr(13) & "Please remove or rename it and run the macro
again.", _
vbOKOnly) = vbOK Then Exit Sub
End If

Next

lTop = 0
bHdr = False

uf1021Mid.Show

With rFirstData
lLastCol = .Columns(.Columns.Count).Column
End With

lFirstDataRow = rFirstData.Row
lFirstDataCol = rFirstData.Column

Set rCtyDataHdr = wsCtyData.Range(Cells(rFirstData.Row - 1,
lFirstDataCol), Cells(rFirstData.Row - 1, lLastCol))
' Create new ws "Top"
wbCtyData.Sheets.Add.Activate
ActiveSheet.Name = "Top"
Set wsTop = ActiveSheet

rCtyDataHdr.Copy Destination:=wsTop.Range(Cells(2, 1)) <----- METHOD
RANGE OF OBJECT WORKSHEET FAILED
'End If

End Sub

I thought maybe the range I copied from had to be on the activesheet
or that the range I copied to had to be on the activesheet and tried
both, but neither helped. So can someone tell me why this code isn't
running? Thanks!

On Dec 11, 3:03 pm, davegb <daveg...@comcast.net> wrote:
> Trying to copy a row of data from one worksheet to another, after
> getting the location of the data to be copied from the user thru a
> userform. But I'm getting "method range of object worksheet failed"
> message.
>
> Option Explicit
> Public bHdr As Boolean
> Public lTop As Long
> Public rFirstData As Range
> Public lLastCol As Long
> Public lNumbrCol As Long
>
> Sub Extr10L()
> Dim wbCtyData As Workbook
> Dim oWS As Object
> Dim wsTop10List As Worksheet
> Dim wsCtyData As Worksheet
> Dim lFirstDataRow As Long
> Dim lArea1FirstRow As Long
> Dim lArea2FirstRow As Long
> Dim lArea3FirstRow As Long
> Dim lHdrRow As Long
> Dim lFirstDataCol As Long
> Dim wsTop As Worksheet
> Dim rCtyDataHdr As Range
>
> Set wsTop10List = ThisWorkbook.Worksheets("CtyLst")
> Set wsCtyData = ActiveSheet
> Set wbCtyData = ActiveWorkbook
>
> 'Test is Mark Top 10 workbook is active
> If ActiveWorkbook.Name = ThisWorkbook.Name Then
> MsgBox "You have selected the workbook that contains the macro." &
> _
> Chr(13) & "Please click Ok and select the correct workbook and " &
> _
> Chr(13) & "worksheet and restart the macro.", vbOKOnly
> Exit Sub
> End If
>
> 'TEST for existing sheet named "Top"
> For Each oWS In wbCtyData.Sheets
> If oWS.Name = "Top" Then
> If MsgBox("A worksheet named Top already exists in this
> workbook." _
> & Chr(13) & "Please remove or rename it and run the macro
> again.", _
> vbOKOnly) = vbOK Then Exit Sub
> End If
>
> Next
>
> lTop = 0
> bHdr = False
>
> uf1021Mid.Show
>
> With rFirstData
> lLastCol = .Columns(.Columns.Count).Column
> End With
>
> lFirstDataRow = rFirstData.Row
> lFirstDataCol = rFirstData.Column
>
> Set rCtyDataHdr = wsCtyData.Range(Cells(rFirstData.Row - 1,
> lFirstDataCol), Cells(rFirstData.Row - 1, lLastCol))
> ' Create new ws "Top"
> wbCtyData.Sheets.Add.Activate
> ActiveSheet.Name = "Top"
> Set wsTop = ActiveSheet
>
> rCtyDataHdr.Copy Destination:=wsTop.Range(Cells(2, 1)) <----- METHOD
> RANGE OF OBJECT WORKSHEET FAILED
> 'End If
>
> End Sub
>
> I thought maybe the range I copied from had to be on the activesheet
> or that the range I copied to had to be on the activesheet and tried
> both, but neither helped. So can someone tell me why this code isn't
> running? Thanks!

Nevermind! I got it to work a little while after I posted.