CODEWITHSUNDEEP
excelvba
Prince M
Prince M

Reputation: 441

Copy and pasting rows of a table in VBA

I have a table, lets say table1 which is AF6:AH100. I want to write a macro that, for each row in this table, it pastes the three cells into W4, X4, Y4, then when the formulas update, it pastes AC3 and AD3 into AI6 and AJ6.

So it would go like this:

FOR N IN 6:100
COPY(AFN,AGN,AHN)
PASTE -> W4, X4, Y4
formulas update
COPY(AC3, AD3)
PASTE -> AIN, AJN

Here is what I have:

Sub RefreshFactors()

Dim rng As Range
cell As Range

Set rng = Range("AF6:AH101")

For Each Row In rng.Rows

{not sure how to copy paste multiple cells at a time}
    
End Sub

Can someone help me write this please?

Upvotes: 0

Views: 556

Answers (1)

Raymond Wu
Raymond Wu

Reputation: 3387

Give this a try:

Option Explicit

Sub RefreshFactors()
        
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("Sheet1") 'Change to the correct sheet name
            
    Const startRow As Long = 6  'First row to process
    Const endRow As Long = 101 'Last row to process
            
    Const srcRng As String = "AF?:AH?" 'Source range, ? is the token for the row number during loop
    Const finalRng As String = "AI?:AJ?" 'Output range, ? is the token for the row number during loop
    
    Dim valueRng As Range
    Set valueRng = ws.Range("W4:Y4")
    
    Dim formulaRng As Range
    Set formulaRng = ws.Range("AC3:AD3")
            
    Application.ScreenUpdating = False
            
    Dim i As Long
    For i = startRow To endRow
        valueRng.Value2 = ws.Range(Replace(srcRng, "?", i)).Value2
        'Application.Calculate 'should be unnecessary but uncomment this if the formula is not updating itself after above line for some reason
        ws.Range(Replace(finalRng, "?", i)).Value2 = formulaRng.Value2
    Next i
    
    Application.ScreenUpdating = True
    
End Sub

Upvotes: 2

Related Questions