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

emmett518

New Member
Joined
Feb 18, 2016
Messages
9
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
 
Last edited:

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
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.
 

skywriter

Well-known Member
Joined
Feb 15, 2014
Messages
1,642
This should work. :cool:

Code:
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
 
Last edited:

Watch MrExcel Video

Forum statistics

Threads
1,123,398
Messages
5,601,442
Members
414,450
Latest member
Cassy_sn

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
Top