I need an excel macro to copy only cell value from other pages?

Question

I'm trying to copy the e46 cell value from all pages except my current page, and paste them in column e of my current page. Everything I've tried copies the e46 cell formula and a I get REF errors. Please help with a macro to accomplish this.

Answer 1

Try this macro. If the sheet you wish to list the values of all E46 values on is not 'Sheet1', then change the "Sheet1" reference to your actual sheet name, i.e. "Data", "List", "Summary", etc.



Then copy the macro to the clipboard:



Sub List_E46()

Dim ws As Worksheet

For Each ws In Worksheets

If ws.Name <> "Sheet1" Then

Sheets("Sheet1").Range("E" & Rows.Count).End(xlUp).Offset(1, 0).Value = _

ws.Range("E46").Value

End If

Next

End Sub



Press ALT + F11



In the menus at the top of the VBE, select INSERT > MODULE



Paste the macro into the module editing area to the right.



Close the VBE and return to Excel.



Press ALT + F8



When the Macros window opens, highlight the macro and click 'Options..'



Enter a letter to be used as a keyboard shortcut and click 'OK'.



Close the Macros window.



Press CTRL + your shortcut letter to run the macro.



Note: If you just want to run the macro, highlight the macro and click 'RUN' when the Macros window opens.

Answer 2

Sheets("Sheet1").Select

Range("E46").Select

Selection.Copy

Sheets("Sheet3").Select

Range("E1").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False

Sheets("Sheet2").Select

Range("E46").Select

Application.CutCopyMode = False

Selection.Copy

Sheets("Sheet3").Select

Range("E2").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _

:=False, Transpose:=False



-------------------------------------------



I don't think that's a good way to go about, though. You'll have to run the macro every time you update any values. A better idea is to use the formula:

=Sheet1!E46

Answer 3

indexed right here are 3 differences on 'replica and paste'. the 1st is a macro that, whilst brought about from any worksheet will replica the fee of A1 on that sheet to cellular A1 on Sheet2. the 2d is an adventure handler that triggers on double clicking cellular A1 on the sheet containing the form handler code. The third is an adventure handler that triggers on a worksheet replace adventure in cellular A1 interior the sheet containing the code. replace all "A1" references to the cellular address you desire to repeat to, i.e. "C24", "F12", and so on. replace all "$A$a million" references to the cellular address you desire to repeat to, i.e. "$C$24", "$F$12", and so on. replace all "Sheet2" references to the actual sheet you desire to repeat to, i.e. "records", "enter", and so on. Macro: replica right here macro to the clipboard: Sub CopyData() selection("A1").replica holiday spot:=Sheets("Sheet2"). _ selection("A1") end Sub Press ALT + F11 interior the menus on the superb of the VBE, choose INSERT > MODULE Paste the macro into the enhancing section to the superb. close the VBE and return to the worksheet. To create a keyboard shortcut, press ALT + F8 whilst the Macros window opens, spotlight the macro and click 'concepts...'. enter a letter for use as a keyboard shortcut and click 'ok'. close the Macros window. To run the macro press CTRL + your shortcut letter. ====== Double click adventure Handler: replica right here code to the clipboard: inner maximum Sub Worksheet_BeforeDoubleClick(ByVal objective As selection, Cancel As Boolean) If objective.address = "$A$a million" Then Sheets("Sheet2").selection("A1").fee = objective.fee end If end Sub choose the suited worksheet and real click the sheet tab. choose 'View Code' Paste the code into the sheet module enhancing section to the superb. close the VBE and return to the worksheet. Double click cellular A1 and the fee of A1 (not the formulation) would be copied to Sheet2 cellular A1. ==== Worksheet_Change adventure Handler: replica this code to the clipboard and use the 'double click' technique above. inner maximum Sub Worksheet_Change(ByVal objective As selection) If objective.address = "$A$a million" Then Sheets("Sheet2").selection("A1").fee = objective.fee end If end Sub

Answer 4

Without Macros, you can accomplish similar:

1. Instead of pasting normally, you can always use Paste Special...

2. Use INDIRECT.



For example, let's assume that the sheets you want to add from are named "January" to "December".

COL A COL B

January =INDIRECT(A1&"!E46")

February =INDIRECT(A2&"!E46")

March =INDIRECT(A3&"!E46")

April =INDIRECT(A4&"!E46")

May =INDIRECT(A5&"!E46")

June =INDIRECT(A6&"!E46")

July =INDIRECT(A7&"!E46")

August =INDIRECT(A8&"!E46")

September =INDIRECT(A9&"!E46")

October =INDIRECT(A10&"!E46")

November =INDIRECT(A11&"!E46")

December =INDIRECT(A12&"!E46")



To leave permanent record of those values you would then copy From B1:B12 to, say C1, Paste Special, Values