Results 1 to 9 of 9

Thread: VBA Copy Paste
Thanks Thanks: 0 Likes Likes: 0

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

    Default VBA Copy Paste

    can anybody explain how to copy from a range of cells in a column and paste into a data driver cell. the thought is building the copy paste out so it takes the first number (sheet a cell b1) copies it. pastes it into a different page in lets say sheet b c4 goes back to the other page copies the next number( sheet a cell b2) and pastes it in sheet B in cell c4 again. and continues until blank. Below is the code I have currently it will copy and paste fine it just will not move to the next cell to move down the copy list.


    ---------------------------------------------------------

    Sub Check_Macro()




    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = False


    start_row = Sheets("Well Info").Range("start_api").Row + 1
    start_column = Sheets("Well Info").Range("start_api").Column


    end_row = Sheets("Well Info").Range("start_api").End(xlDown).Row
    end_column = Sheets("Well Info").Range("start_api").End(xlToRight).Column


    initial_api = Sheets("Rev_Summary").Range("API_Driver")


    For r = start_row To end_row

    Sheets("Well Info").Range("N5").Copy
    Sheets("Rev_Summary").Range("API_Driver").PasteSpecial xlPasteValues



    Next r


    Application.ScreenUpdating = True


    End Sub

  2. #2
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,908
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA Copy Paste

    Welcome to the forum @tgmvba

    Here is one way
    - using a Do Loop

    Code:
    Sub tgmvba()
        Dim cel As Range                                    'declare variable
        Set cel = Sheets("A").Range("B1")                   'first cell to copy
    
        Do
            Sheets("B").Range("C4") = cel                   'attribute value (faster than copy & paste)
            Set cel = cel.Offset(1)                         'move down one cell
        Loop Until cel = ""                                 'when to stop looping
    
    End Sub

  3. #3
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,908
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA Copy Paste

    Another way using a For Loop

    Code:
    Sub tgmvba_ForLoop()
        Dim r As Long, lastR As Long                        'declare variables
        Dim wsA As Worksheet, Driver As Range
        Set wsA = Sheets("A")                               'set sheet variable
        Set Driver = Sheets("B").Range("C4")                'set range variable
        lastR = wsA.Cells(Rows.Count, "B").End(xlUp)        'last row with value in column B
    
        For r = 1 To lastR
            Driver = wsA.Cells(r, "B")                      'attribute value
        Next r
    End Sub
    Last edited by Yongle; Jul 14th, 2019 at 01:55 AM.

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

    Default Re: VBA Copy Paste

    Can you place this in what I have coded so I know where to put it? Thanks.

  5. #5
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,908
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA Copy Paste

    try this

    Code:
    Sub tgmvba()
        Dim cel As Range
        Set cel = Sheets("Well Info").Range("N4")
        Do
            Sheets("Rev_Summary").Range("API_Driver") = cel
            Set cel = cel.Offset(1)
        Loop Until cel = ""
    End Sub

  6. #6
    Board Regular
    Join Date
    Mar 2015
    Posts
    3,908
    Post Thanks / Like
    Mentioned
    72 Post(s)
    Tagged
    7 Thread(s)

    Default Re: VBA Copy Paste

    Reading your first post again , revise this line in post #5

    Code:
    Set cel = Sheets("Well Info").Range("N5")

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

    Default Re: VBA Copy Paste

    Thanks Iíll try and be back in touch later this week

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

    Default Re: VBA Copy Paste

    That worked! Thanks.

    Follow up question if you do not mind. How do I copy from say sheet1 range a2:o15 and paste values into the first open range below that range and loop that. It will end the loop when the previous loop ends. Iím assuming itís very similar to what you already said Iím just not sure how to dynamically offset the paste to allow it to move down to the next available row.

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

    Default Re: VBA Copy Paste

    Just kidding it is not working it will only loop 6 rows before the loops ends. Even though the cell is not blank

Some videos you may like

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
  •