VBA - Copy specific columns onto another sheet based on value of Cell B13

[dougzin11]

Hello everyone,

I am new to VBA and I've looked for a lot of threads which could answer my questions but couldn't find any.

Here is my code so far:

Sub copyrows()

Dim tfCol As Range, Cell As Object

Set tfCol = Range("B36:B45")

For Each Cell In tfCol

If Cell.Value = [b13] Then
Cell.EntireRow.Copy
Sheets("Carteira").Activate
Selection.Offset(1).Insert
Selection.Offset(1).Select
ActiveSheet.PasteSpecial xlPasteValues
End If

Next

End Sub

I am having 3 problems with this code:
1 - I do not want to copy the entire row, but only the columns B to BH from that specific row (I guess I would need to do a loop, but I don't know how);
2 - I want to copy only the values and not the formulas or format.
3 - Since I am copying the entire row, the VBA code only runs when I select the column A from the worksheet "Carteira". Otherwise it gives me the run-time error '1004': "The copy area and the paste area are not the same size".

Thanks in advance!

 

[dougzin11]

Oh, I forgot 1 more problem:

4 - I would like to paste the values in specific columns and rows in the "Carteira" sheet. (More specific: Row 6 and Column C).

 

[Michael M]

What happens if there is more than one lot of data to copy ??
You said the data has to be copied to C6......so any further data will overwrite that data ??

 

[dougzin11]

What happens if there is more than one lot of data to copy ??
You said the data has to be copied to C6......so any further data will overwrite that data ??

No. It should not overwrite it.
In my code I added the lines:
"Selection.Offset(1).Insert
Selection.Offset(1).Select"

So everytime I run the code, it should identify the last row with data written in the sheet "Carteira" and add the data below this last row.

 

[Michael M]

MAybe this

Sub MM1()
Dim r As Long, lr As Long
lr = Sheets("Carteira").Cells(Rows.Count, "A").End(xlUp).Row + 1
For r = 36 To 45
    If Range("B" & r).Value = Range("B13").Value Then
    Range("B" & r & ":BH" & r).Copy
        With Sheets("Carteira")
            Range("A" & lr).PasteSpecial xlPasteValues
        End With
    End If
Next r

End Sub

 

[dougzin11]

It worked perfectly!

Thanks Michael M. !!