Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't copy
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! |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Can't copy
On Dec 11, 3:03 pm, davegb 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. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy and paste versus copy and insert copied cells | New Users to Excel | |||
Copy/Paste how to avoid the copy of formula cells w/o calc values | Excel Discussion (Misc queries) | |||
copy formulas from a contiguous range to a safe place and copy them back later | Excel Programming | |||
EXCEL FILE a copy/a copy/a copy ....filename | New Users to Excel | |||
How copy format, font, color and border without copy/paste? | Excel Programming |