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

dougzin11

New Member
Joined
Jul 16, 2017
Messages
4
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!
 

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.
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).
 
Upvote 0
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 ??
 
Upvote 0
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.
 
Upvote 0
MAybe this

Code:
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
 
Upvote 0

Forum statistics

Threads
1,213,561
Messages
6,114,312
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top