VBA Copy Paste

[tgmvba]

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

 

[Yongle]

Welcome to the forum @tgmvba

Here is one way
- using a Do Loop

Sub tgmvba()
    Dim cel As Range                                  [COLOR=#006400][I]  'declare variable[/I][/COLOR]
    Set cel = Sheets("A").Range("B1")               [COLOR=#006400][I]    'first cell to copy[/I][/COLOR]

    Do
        Sheets("B").Range("C4") = cel                  [I][COLOR=#006400] 'attribute value (faster than copy & paste)[/COLOR][/I]
        Set cel = cel.Offset(1)                        [I][COLOR=#006400] 'move down one cell[/COLOR][/I]
    Loop Until cel = ""                                 [I][COLOR=#006400]'when to stop looping[/COLOR][/I]

End Sub

 

[Yongle]

Another way using a For Loop

Sub tgmvba_ForLoop()
    Dim r As Long, lastR As Long                       [COLOR=#006400][I] 'declare variables[/I][/COLOR]
    Dim wsA As Worksheet, Driver As Range
    Set wsA = Sheets("A")                              [COLOR=#006400][I] 'set sheet variable[/I][/COLOR]
    Set Driver = Sheets("B").Range("C4")              [COLOR=#006400][I]  'set range variable[/I][/COLOR]
    lastR = wsA.Cells(Rows.Count, "B").End(xlUp)       [COLOR=#006400][I] 'last row with value in column B[/I][/COLOR]

    For r = 1 To lastR
        Driver = wsA.Cells(r, "B")                     [COLOR=#006400][I] 'attribute value[/I][/COLOR]
    Next r
End Sub

 

[tgmvba]

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

 

[Yongle]

try this

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

 

[Yongle]

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

Set cel = Sheets("Well Info").Range("[COLOR=#ff0000]N5[/COLOR]")

 

[tgmvba]

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

 

[tgmvba]

That worked! Thanks.

Follow up question if you do not mind. How do I copy from say sheet1 range a215 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.

 

[tgmvba]

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