Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

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

  1. #1
    New Member
    Join Date
    Jul 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  2. #2
    New Member
    Join Date
    Jul 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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).

  3. #3
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    Western NSW
    Posts
    15,520
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

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

    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 ??
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  4. #4
    New Member
    Join Date
    Jul 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    Quote Originally Posted by Michael M View Post
    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.

  5. #5
    Board Regular Michael M's Avatar
    Join Date
    Oct 2005
    Location
    Western NSW
    Posts
    15,520
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

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

    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
    Regards
    Michael M
    ---------------------------------------
    The more I learn, the less I seem to know.....A Please and Thank You cost nothing !
    It's easier to debug if we can see the whole macro !
    Home 2007 & 2013

    - Posting guidelines, forum rules and terms of use

    - To download Mr Excel HTML Maker

    - Try searching for your answer first, see how

    - Read the FAQs

    - List of BB codes


    [CODE]Place Your Code Here[/CODE]

  6. #6
    New Member
    Join Date
    Jul 2017
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    It worked perfectly!

    Thanks Michael M. !!

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


DMCA.com