CODEWITHSUNDEEP
excelvba
user11024211
user11024211

Reputation:

Variable range base on cell value

Not sure if I did the code correctly but it is there.

My quandary is this line ws.Range("B" & J, Range("K" & J)).copy. It is giving me a Run-time error 1004 Method range of object worksheet failed.

What I am trying to do is copy/paste any row b:k if column "P" indicated "recorded".

Your assistance is greatly appreciated. Thank you.

Sub Clear_Recorded()

Dim ws As Worksheet
Dim ws1 As Worksheet
Dim lRow As Integer 'Data Tab
Dim count As Integer

Set ws = Sheet1 'Data
Set ws1 = Sheet11 'Archive
count = 0

lRow = ws.Range("B" & Rows.count).End(xlUp).Row

For J = 2 To lRow

    If ws.Range("P" & J).Value = "Recorded" Then
        count = count + 1
        ws.Range("B" & J, Range("K" & J)).copy
        ws1.Range("A" & count).PasteSpecial
    End If

Next J

Upvotes: 0

Views: 60

Answers (2)

roses56
roses56

Reputation: 130

You just need to get rid of the second "Range" and the extra parenthesis. Hope this helps!

ws.Range("B" & J, "K" & J).Copy

Edit: Spelling

Upvotes: 1

Marc
Marc

Reputation: 19

You are trying to set a worksheet object without referencing any kind of sheet.

How to fix this issue:

set ws = Thisworkbook.Sheets("SheetName")

Also you should rather use this:

 if ws.Range("P2").Offset(J-1) = "Recorded" then
'Rest of code goes here

.Offset has the parameters RowOffset ,ColumnOffset This should solve your problem.

Upvotes: 0

Related Questions