VBA macro for copying conditional data to specific cells

Question

I'm new to programming in VBA and I'm looking To take data from different worksheets that matches a condition. Then copy and paste from one specific cell to another specific cell 7 times. The code I have doesn't work and I'm looking to improve it. As I run the code I get flagged for Run time error '1004' Method 'Range of object '_Worksheet' failed at the beginning of the IF statement.

 Sub CopyValues()

 'Declare variables
 'Declare sheet variables
 Dim Sourcews As Worksheet
 Dim Pastews As Worksheet

 'Declare counter variables
 Dim i As Integer
 Dim n As Integer
 Dim lastrow As Long

 Set Sourcews = ThisWorkbook.Sheets("Source")
 Set Pastews = ThisWorkbook.Sheets("Paste")

  lastrow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

 For i = 3 To lastrow

 If Sourcews.Range(i, "AA").Value = "Needed Value" Then

    Pastews.Cells("C:18").Paste
    Pastews.Cells("D:18").Paste
    Pastews.Cells("E:18").Paste
    Pastews.Cells("F:18").Paste
    Pastews.Cells("G:18").Paste
    Pastews.Cells("H:18").Paste



End If

Next

I'm not sure what your question is. Does the code you posted work properly? — Jarom
– Jarom, Commented Aug 1, 2017 at 15:39
Probably the error is here Pastews.Cells("H:18").Paste Should it be Pastews.Range("H18").Paste? (Have not checked) And all values should be like this. — Vityata
– Vityata, Commented Aug 1, 2017 at 15:40
If it works and you are looking to make it better then check out Code Review — JG7
– JG7, Commented Aug 1, 2017 at 15:41

SJR · Accepted Answer · 2017-08-01 15:59:53Z

2

Try this. I'm assuming you want to paste into row 18 and then 19 etc, and not 18 repeatedly!

Sub CopyValues()

'Declare variables
'Declare sheet variables
Dim Sourcews As Worksheet
Dim Pastews As Worksheet

'Declare counter variables
Dim i As Long
Dim n As Long
Dim lastrow As Long

Set Sourcews = ThisWorkbook.Sheets("Source")
Set Pastews = ThisWorkbook.Sheets("Paste")

lastrow = Sourcews.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
n = 18

For i = 3 To lastrow
    If Sourcews.Cells(i, "AA").Value = "Needed Value" Then
        Sourcews.Cells(i, "AA").Copy Pastews.Cells(n, "C").Resize(, 6)
        n = n + 1
    End If
Next

End Sub

edited Aug 1, 2017 at 15:59

answered Aug 1, 2017 at 15:54

SJR

23.1k6 gold badges21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

chicken_milk Over a year ago

Well I just want row 18 and copy across the columns

SJR Over a year ago

Have you tried the code? Let me know how you get on. I don't understand what you want to happen the second time the value is found.

chicken_milk Over a year ago

There is no run time error but its not running. How come?

SJR Over a year ago

Is "Needed Value" or whatever actually in column AA? Check the value of lastrow - step through your code using F8.

chicken_milk Over a year ago

Yes the value that I'm checking against is in AA.

TBlock · Accepted Answer · 2017-08-01 15:42:00Z

0

Instead of using .Cells("C:18") use .Range("C18"). For questions like this, you can also try recording a macro and learning code from what Excel tells you.

answered Aug 1, 2017 at 15:42

TBlock

713 bronze badges

Comments

Vityata · Accepted Answer · 2017-08-01 16:13:01Z

0

The If should be like this:

If Sourcews.Range("AA"&i).Value = "Needed Value" Then

Then in the Pastews.Cells, it should be refered to the Worksheet like this:

pastews.Range("A18").Copy Destination:=pastews.Range("H18")

or

pastews.Cells(18,1).Copy Destination:=pastews.Cells(18,8)

Here is the MSDN article about ranges in VBA - it's worth reading - https://msdn.microsoft.com/en-us/vba/excel-vba/articles/range-object-excel

edited Aug 1, 2017 at 16:13

answered Aug 1, 2017 at 15:43

Vityata

43.8k10 gold badges64 silver badges110 bronze badges

1 Comment

SJR Over a year ago

That will error because Paste is a method of the Sheet object, not the Range object (I'm sure you know this but just missed it on this occasion).

Jarom · Accepted Answer · 2017-08-01 16:40:03Z

0

Aside from some syntax errors that others have discussed, you haven't specified what is bieng copied before you try to use the .paste method. I would just avoid the copy and paste methods (they are inefficient) and set the cells equal to the value of the range in the if statement like so:

Sub CopyValues()

 'Declare variables
 'Declare sheet variables
 Dim Sourcews As Worksheet
 Dim Pastews As Worksheet

 'Declare counter variables
 Dim i As Integer
 Dim n As Integer
 Dim lastrow As Long

 Set Sourcews = ThisWorkbook.Sheets("sheet1")
 Set Pastews = ThisWorkbook.Sheets("sheet2")

  lastrow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

 For i = 3 To lastrow

If Sourcews.Range("AA" & i).Value = "Needed Value" Then

    Pastews.Range("C18") = Sourcews.Range("AA" & i).Value
    Pastews.Range("D18") = Sourcews.Range("AA" & i).Value
    Pastews.Range("E18") = Sourcews.Range("AA" & i).Value
    Pastews.Range("F18") = Sourcews.Range("AA" & i).Value
    Pastews.Range("G18") = Sourcews.Range("AA" & i).Value



End If

Next

End Sub

Or you could set the value as a variable for cleaner looking code, like this:

Sub CopyValues()

 'Declare variables
 'Declare sheet variables
 Dim Sourcews As Worksheet
 Dim Pastews As Worksheet

 'Declare counter variables
 Dim i As Integer
 Dim n As Integer
 Dim lastrow As Long
 Dim x As String

 Set Sourcews = ThisWorkbook.Sheets("sheet1")
 Set Pastews = ThisWorkbook.Sheets("sheet2")

  lastrow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

 For i = 3 To lastrow

If Sourcews.Range("AA" & i).Value = "Needed Value" Then

    x = Sourcews.Range("AA" & i).Value
    Pastews.Range("C18") = x
    Pastews.Range("D18") = x
    Pastews.Range("E18") = x
    Pastews.Range("F18") = x
    Pastews.Range("G18") = x



End If

Next

End Sub

Or, to make the code even more concise, you can combine the range that is receiving the copied value as Pastews.Range("C18:G18") = x like this:

Sub CopyValues()

 'Declare variables
 'Declare sheet variables
 Dim Sourcews As Worksheet
 Dim Pastews As Worksheet

 'Declare counter variables
 Dim i As Integer
 Dim n As Integer
 Dim lastrow As Long
 Dim x As String

 Set Sourcews = ThisWorkbook.Sheets("sheet1")
 Set Pastews = ThisWorkbook.Sheets("sheet2")

  lastrow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

 For i = 3 To lastrow

If Sourcews.Range("AA" & i).Value = "Needed Value" Then

    x = Sourcews.Range("AA" & i).Value
    Pastews.Range("C18:G18") = x

End If

Next

End Sub

I know I posted a lot, but I wanted to show you a progression of how your could can be more concise and efficient. I hope it helps.

edited Aug 1, 2017 at 16:40

answered Aug 1, 2017 at 15:50

Jarom

1,0871 gold badge14 silver badges38 bronze badges

17 Comments

chicken_milk Over a year ago

Hey Jarom I tired running the first block of code but nothing is happening. not even a run time error

Jarom Over a year ago

did you change the name of the sheets from sheet1 and sheet2 back to source and paste? I changed the names to debug on my machine. I edited the answer, but I forgot on my original post to switch them back.

chicken_milk Over a year ago

Source is where the data is, paste is where I'm trying to get it to

Jarom Over a year ago

If it is not that the sheet names are incorrect (which was my mistake) then it probably has something to do with the for loop not having the correct counters. Do you know how to debug in the VBA environment?

Jarom Over a year ago

No problem! In the editor where you see your code you can execute the code line by line by hitting the F8 key. This is really important to be able to see what your code is doing. So go into the editor, click anywhere inside your code and start pressing F8. The editor will highlight the line it will execute next in yellow. F8 thru the lines until you get the yellow line on the for statment. Then hover your mouse over the lastrow variable to see the value of the variable. Let me know how it goes.

|

Cyril · Accepted Answer · 2017-08-01 20:45:06Z

I was working through the duplicate question for this and provided this answer:

Try:

Sub CopyValues()

'Declare counter variables
Dim i As Integer, j as Integer, lastrow As Long
'Declare variables
Dim Sourcedataws As Worksheet, WStotransfer As Worksheet
'Declare sheet variables
Set Sourcedataws = ThisWorkbook.Sheets("Source Data")
Set WStotransferws = ThisWorkbook.Sheets("WStotransfer")

lastrow = Sourcedataws.Cells(Sourcedataws.Rows.Count, "A").End(xlUp).Row

WStotransferws.Range("C18:I18").ClearContents

For i = 2 To lastrow
If WStotransferws.Range("I18").Value="" Then
    If Sourcedataws.Range("AA" & i).Value = "Condition" Then
        Sourcedataws.Range("A"&i).Copy 
        j=WStotransferws.Cells(18, WStotransferws.Columns.Count).End(xlToLeft).Column
        WStotransferws.Cells(18,j+1).PasteSpecial xlPasteValues
        End If
    Else
    End If
Next i

End Sub

Other post found: VBA Need a Nested Loop to shift columns

There's a long conversation with the poster about details that were not in the post.

Collectives™ on Stack Overflow

VBA macro for copying conditional data to specific cells

5 Answers 5

5 Comments

Comments

1 Comment

17 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

5 Comments

Comments

1 Comment

17 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related