\nCOPY(AFN,AGN,AHN)
\nPASTE -> W4, X4, Y4
\nformulas update
\nCOPY(AC3, AD3)
\nPASTE -> AIN, AJN
Here is what I have:
\nSub RefreshFactors()\n\nDim rng As Range\ncell As Range\n\nSet rng = Range("AF6:AH101")\n\nFor Each Row In rng.Rows\n\n{not sure how to copy paste multiple cells at a time}\n \nEnd Sub\n
\nCan someone help me write this please?
\n","author":{"@type":"Person","name":"Prince M"},"upvoteCount":0,"answerCount":1,"acceptedAnswer":{"@type":"Answer","text":"Give this a try:
\nOption Explicit\n\nSub RefreshFactors()\n \n Dim ws As Worksheet\n Set ws = ThisWorkbook.Sheets("Sheet1") 'Change to the correct sheet name\n \n Const startRow As Long = 6 'First row to process\n Const endRow As Long = 101 'Last row to process\n \n Const srcRng As String = "AF?:AH?" 'Source range, ? is the token for the row number during loop\n Const finalRng As String = "AI?:AJ?" 'Output range, ? is the token for the row number during loop\n \n Dim valueRng As Range\n Set valueRng = ws.Range("W4:Y4")\n \n Dim formulaRng As Range\n Set formulaRng = ws.Range("AC3:AD3")\n \n Application.ScreenUpdating = False\n \n Dim i As Long\n For i = startRow To endRow\n valueRng.Value2 = ws.Range(Replace(srcRng, "?", i)).Value2\n 'Application.Calculate 'should be unnecessary but uncomment this if the formula is not updating itself after above line for some reason\n ws.Range(Replace(finalRng, "?", i)).Value2 = formulaRng.Value2\n Next i\n \n Application.ScreenUpdating = True\n \nEnd Sub\n
\n","author":{"@type":"Person","name":"Raymond Wu"},"upvoteCount":2}}}Reputation: 441
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
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