VBA Copy Paste

tgmvba

New Member
Joined
Jul 13, 2019
Messages
6
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
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Welcome to the forum @tgmvba :)

Here is one way
- using a Do Loop

Code:
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
 
Upvote 0
Another way using a For Loop

Code:
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
 
Last edited:
Upvote 0
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
 
Upvote 0
Reading your first post again , revise this line in post #5

Code:
Set cel = Sheets("Well Info").Range("[COLOR=#ff0000]N5[/COLOR]")
 
Upvote 0
That worked! Thanks.

Follow up question if you do not mind. How do I copy from say sheet1 range a2:eek:15 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.
 
Upvote 0
Just kidding it is not working it will only loop 6 rows before the loops ends. Even though the cell is not blank
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,194
Members
448,554
Latest member
Gleisner2

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