VBA Command to copy cells a1, b1, d1 and J1 from worksheet 1 to worksheet 2

[emmett518]

1. Can someone post the command in VBA to copy the results of the formulas (not the actual formulas) in cells A1, B1, D1 and J1 from worksheet 1 to worksheet 2 in the same spreadsheet?

2. How would that change if you wanted to transfer the formulas, not the output from the formulas?

3. Can you use variables for the cells to cycle through various cells to copy - say A1, B1, D1, and then A2, C2, E2, etc?

Thanks

 

[skywriter]

worksheet 2 in the same spreadsheet?

Which cells?

2. How would that change if you wanted to transfer the formulas, not the output from the formulas?

You would use paste special formulas just like if you wanted to do the same thing manually without code.

3. Can you use variables for the cells to cycle through various cells to copy - say A1, B1, D1, and then A2, C2, E2, etc?

Yes.

 

[emmett518]

Which cells?


You would use paste special formulas just like if you wanted to do the same thing manually without code.


Yes.

The same cells in worksheet 2.

 

[skywriter]

This should work.

Sub emmett518V1()
'Values only, no formulas.
Dim arrAddress, j
arrAddress = Array("A1", "B1", "D1", "J1")
    For Each j In arrAddress
        Worksheets("Sheet2").Range(j).Value = Worksheets("Sheet1").Range(j).Value
     Next j
End Sub

Sub emmett518V2()
'Formulas only, no values.
Dim arrAddress, j
arrAddress = Array("A1", "B1", "D1", "J1")
    For Each j In arrAddress
        Worksheets("Sheet1").Range(j).Copy
        Worksheets("Sheet2").Range(j).PasteSpecial xlPasteFormulas
        Application.CutCopyMode = False
     Next j
End Sub

Sub emmett518V3()
'Cycle through certain cells.
Dim arrAddress, j
arrAddress = Array("A1", "B1", "D1", "A2", "C2", "E2")
    For Each j In arrAddress
            Range(j).Select
            MsgBox "Cell " & j & " has been selected."
    Next j
End Sub

 

[emmett518]

Thanks. I'm going to work through your code to try to understand it.

Emmett