How to copy a formula hidden in a row at the bottom of a table to the active cell located in the same column using code assigned to a button?Copying multiple cells in same row based on multiple criteriaExcel Macro to Locate Duplicate Values of a Column and Copy Adjacent CellsCopy the last value above in same column as active cellVBA - Enter a formula into all active rows of the current column (when you don't know the column letter, or if the column changes)Excel Duty ListIgnore hidden rows above active cells to compare active cells with the first corresponding visible cell abovecoloring a row including cells in hidden columnsMacro to copy formula down to next cell in column and then paste above cell as valueHow to copy and paste values in a row based on table input and paste them into a dynamic Worksheet nameHow to hide row based on cell value in last column of worksheet?
What makes Ada the language of choice for the ISS's safety-critical systems?
How would a aircraft visually signal "in distress"?
Is open-sourcing the code of a webapp not recommended?
What is wrong with this proof that symmetric matrices commute?
What language is the software written in on the ISS?
Is it a problem if <h4>, <h5> and <h6> are smaller than regular text?
What is the actual quality of machine translations?
Can a black dragonborn's acid breath weapon destroy objects?
How is water heavier than petrol, even though its molecular weight is less than petrol?
What does the term "railed" mean in signal processing?
How to retract an idea already pitched to an employer?
BGP convergence issue
Are there downsides to using std::string as a buffer?
If you had a giant cutting disc 60 miles diameter and rotated it 1000 rps, would the edge be traveling faster than light?
Soft question: Examples where lack of mathematical rigour cause security breaches?
Preventing Employees from either switching to Competitors or Opening Their Own Business
Confusion about off peak timings of London trains
What are the peak hours for public transportation in Paris?
How to project 3d image in the planes xy, xz, yz?
The eyes have it
Frame failure sudden death?
An average heaven where everyone has sexless golden bodies and is bored
Should I compare a std::string to "string" or "string"s?
Movie about a boy who was born old and grew young
How to copy a formula hidden in a row at the bottom of a table to the active cell located in the same column using code assigned to a button?
Copying multiple cells in same row based on multiple criteriaExcel Macro to Locate Duplicate Values of a Column and Copy Adjacent CellsCopy the last value above in same column as active cellVBA - Enter a formula into all active rows of the current column (when you don't know the column letter, or if the column changes)Excel Duty ListIgnore hidden rows above active cells to compare active cells with the first corresponding visible cell abovecoloring a row including cells in hidden columnsMacro to copy formula down to next cell in column and then paste above cell as valueHow to copy and paste values in a row based on table input and paste them into a dynamic Worksheet nameHow to hide row based on cell value in last column of worksheet?
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty height:90px;width:728px;box-sizing:border-box;
I have a table in a worksheet that tracks who and when a person is assigned to perform a duty. The table has a header row with dates. The leftmost 2 columns have the title and name of the individuals (roster). Only one person per date is assigned to the specific duty. The bottom row of the table contains the formula that I want to copy to the active cell in the same column in the table when the button is clicked. I have made several attempts to accomplish this task, but all have been riddled with errors. Any help would be appreciated.
Sub PasteFormula()
'
' PasteFormula Macro
' Pastes a formula from the table's bottom row, that is hidden, into the active cell of the same column.
'
'
ActiveCell.PasteSpecial xlPasteAllExceptBorders
ActiveSheet.Paste
End Sub
excel vba
edited Mar 24 at 16:30
BigBen
7,7253820
asked Mar 24 at 16:25
Brian MasonerBrian Masoner
31
|
show 7 more comments
I have a table in a worksheet that tracks who and when a person is assigned to perform a duty. The table has a header row with dates. The leftmost 2 columns have the title and name of the individuals (roster). Only one person per date is assigned to the specific duty. The bottom row of the table contains the formula that I want to copy to the active cell in the same column in the table when the button is clicked. I have made several attempts to accomplish this task, but all have been riddled with errors. Any help would be appreciated.
Sub PasteFormula()
'
' PasteFormula Macro
' Pastes a formula from the table's bottom row, that is hidden, into the active cell of the same column.
'
'
ActiveCell.PasteSpecial xlPasteAllExceptBorders
ActiveSheet.Paste
End Sub
excel vba
edited Mar 24 at 16:30
BigBen
7,7253820
asked Mar 24 at 16:25
Brian MasonerBrian Masoner
31
Sub PasteFormula() ' ' PasteFormula Macro ' Pastes a formula from the table's bottom row, that is hidden, into the active cell of the same column. ' ' ActiveCell.PasteSpecial xlPasteAllExceptBorders ActiveSheet.Paste End Sub
– Brian Masoner
Mar 24 at 16:30
I am new to posting into forums. Please excuse my clunky attempts to share.
– Brian Masoner
Mar 24 at 16:31
gotcha. I edited the code into your question. Please make any changes to the code if the edit is not correct.
– BigBen
Mar 24 at 16:31
I have researched this thoroughly and can only find examples where the cell to be copied is a predefined cell in the worksheet. For my solution to work correctly, the cell to be copied will change with respect to the column where the active cell is located.
– Brian Masoner
Mar 24 at 16:32
Is the table formatted as an Excel table (using Ctrl+T)?
– BigBen
Mar 24 at 16:33
|
show 7 more comments
I have a table in a worksheet that tracks who and when a person is assigned to perform a duty. The table has a header row with dates. The leftmost 2 columns have the title and name of the individuals (roster). Only one person per date is assigned to the specific duty. The bottom row of the table contains the formula that I want to copy to the active cell in the same column in the table when the button is clicked. I have made several attempts to accomplish this task, but all have been riddled with errors. Any help would be appreciated.
Sub PasteFormula()
'
' PasteFormula Macro
' Pastes a formula from the table's bottom row, that is hidden, into the active cell of the same column.
'
'
ActiveCell.PasteSpecial xlPasteAllExceptBorders
ActiveSheet.Paste
End Sub
excel vba
edited Mar 24 at 16:30
BigBen
7,7253820
asked Mar 24 at 16:25
Brian MasonerBrian Masoner
31
I have a table in a worksheet that tracks who and when a person is assigned to perform a duty. The table has a header row with dates. The leftmost 2 columns have the title and name of the individuals (roster). Only one person per date is assigned to the specific duty. The bottom row of the table contains the formula that I want to copy to the active cell in the same column in the table when the button is clicked. I have made several attempts to accomplish this task, but all have been riddled with errors. Any help would be appreciated.
Sub PasteFormula()
'
' PasteFormula Macro
' Pastes a formula from the table's bottom row, that is hidden, into the active cell of the same column.
'
'
ActiveCell.PasteSpecial xlPasteAllExceptBorders
ActiveSheet.Paste
End Sub
excel vba
excel vba
edited Mar 24 at 16:30
BigBen
7,7253820
asked Mar 24 at 16:25
Brian MasonerBrian Masoner
31
edited Mar 24 at 16:30
BigBen
7,7253820
asked Mar 24 at 16:25
Brian MasonerBrian Masoner
31
edited Mar 24 at 16:30
BigBen
7,7253820
edited Mar 24 at 16:30
BigBen
7,7253820
edited Mar 24 at 16:30
BigBen
7,7253820
7,7253820
asked Mar 24 at 16:25
Brian MasonerBrian Masoner
31
asked Mar 24 at 16:25
Brian MasonerBrian Masoner
31
asked Mar 24 at 16:25
Brian MasonerBrian Masoner
31
31
Sub PasteFormula() ' ' PasteFormula Macro ' Pastes a formula from the table's bottom row, that is hidden, into the active cell of the same column. ' ' ActiveCell.PasteSpecial xlPasteAllExceptBorders ActiveSheet.Paste End Sub
– Brian Masoner
Mar 24 at 16:30
I am new to posting into forums. Please excuse my clunky attempts to share.
– Brian Masoner
Mar 24 at 16:31
gotcha. I edited the code into your question. Please make any changes to the code if the edit is not correct.
– BigBen
Mar 24 at 16:31
I have researched this thoroughly and can only find examples where the cell to be copied is a predefined cell in the worksheet. For my solution to work correctly, the cell to be copied will change with respect to the column where the active cell is located.
– Brian Masoner
Mar 24 at 16:32
Is the table formatted as an Excel table (using Ctrl+T)?
– BigBen
Mar 24 at 16:33
|
show 7 more comments
Sub PasteFormula() ' ' PasteFormula Macro ' Pastes a formula from the table's bottom row, that is hidden, into the active cell of the same column. ' ' ActiveCell.PasteSpecial xlPasteAllExceptBorders ActiveSheet.Paste End Sub
– Brian Masoner
Mar 24 at 16:30
I am new to posting into forums. Please excuse my clunky attempts to share.
– Brian Masoner
Mar 24 at 16:31
gotcha. I edited the code into your question. Please make any changes to the code if the edit is not correct.
– BigBen
Mar 24 at 16:31
I have researched this thoroughly and can only find examples where the cell to be copied is a predefined cell in the worksheet. For my solution to work correctly, the cell to be copied will change with respect to the column where the active cell is located.
– Brian Masoner
Mar 24 at 16:32
Is the table formatted as an Excel table (using Ctrl+T)?
– BigBen
Mar 24 at 16:33
Sub PasteFormula() ' ' PasteFormula Macro ' Pastes a formula from the table's bottom row, that is hidden, into the active cell of the same column. ' ' ActiveCell.PasteSpecial xlPasteAllExceptBorders ActiveSheet.Paste End Sub
– Brian Masoner
Mar 24 at 16:30
Sub PasteFormula() ' ' PasteFormula Macro ' Pastes a formula from the table's bottom row, that is hidden, into the active cell of the same column. ' ' ActiveCell.PasteSpecial xlPasteAllExceptBorders ActiveSheet.Paste End Sub
– Brian Masoner
Mar 24 at 16:30
I am new to posting into forums. Please excuse my clunky attempts to share.
– Brian Masoner
Mar 24 at 16:31
I am new to posting into forums. Please excuse my clunky attempts to share.
– Brian Masoner
Mar 24 at 16:31
gotcha. I edited the code into your question. Please make any changes to the code if the edit is not correct.
– BigBen
Mar 24 at 16:31
gotcha. I edited the code into your question. Please make any changes to the code if the edit is not correct.
– BigBen
Mar 24 at 16:31
I have researched this thoroughly and can only find examples where the cell to be copied is a predefined cell in the worksheet. For my solution to work correctly, the cell to be copied will change with respect to the column where the active cell is located.
– Brian Masoner
Mar 24 at 16:32
I have researched this thoroughly and can only find examples where the cell to be copied is a predefined cell in the worksheet. For my solution to work correctly, the cell to be copied will change with respect to the column where the active cell is located.
– Brian Masoner
Mar 24 at 16:32
Is the table formatted as an Excel table (using Ctrl+T)?
– BigBen
Mar 24 at 16:33
Is the table formatted as an Excel table (using Ctrl+T)?
– BigBen
Mar 24 at 16:33
|
show 7 more comments
1 Answer
1
active
oldest
votes
Maybe something like this, using the inbuilt properties of a ListObject. Change the sheet and table names as needed.
Sub PasteMyFormula()
Dim myTbl As ListObject
Set myTbl = ThisWorkbook.Sheets("Sheet1").ListObjects("Table1")
With myTbl
If Not Intersect(ActiveCell, .DataBodyRange) Is Nothing Then
.DataBodyRange(.ListRows.Count, ActiveCell.Column).Copy
ActiveCell.PasteSpecial xlPasteAllExceptBorders
Application.CutCopyMode = False
End If
End With
End Sub
answered Mar 24 at 16:49
BigBenBigBen
7,7253820
BigBen - Sorry that took so long to test, the COL walked in - which is odd for a Sunday. However, your solution worked! Awesome work. I cannot thank you enough.
– Brian Masoner
Mar 24 at 17:22
Great, glad to help.
– BigBen
Mar 24 at 17:25
add a comment |
Your Answer
StackExchange.ifUsing("editor", function ()
StackExchange.using("externalEditor", function ()
StackExchange.using("snippets", function ()
StackExchange.snippets.init();
);
);
, "code-snippets");
StackExchange.ready(function()
var channelOptions =
tags: "".split(" "),
id: "1"
;
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function()
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled)
StackExchange.using("snippets", function()
createEditor();
);
else
createEditor();
);
function createEditor()
StackExchange.prepareEditor(
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader:
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
,
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
);
);
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55325935%2fhow-to-copy-a-formula-hidden-in-a-row-at-the-bottom-of-a-table-to-the-active-cel%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Maybe something like this, using the inbuilt properties of a ListObject. Change the sheet and table names as needed.
Sub PasteMyFormula()
Dim myTbl As ListObject
Set myTbl = ThisWorkbook.Sheets("Sheet1").ListObjects("Table1")
With myTbl
If Not Intersect(ActiveCell, .DataBodyRange) Is Nothing Then
.DataBodyRange(.ListRows.Count, ActiveCell.Column).Copy
ActiveCell.PasteSpecial xlPasteAllExceptBorders
Application.CutCopyMode = False
End If
End With
End Sub
answered Mar 24 at 16:49
BigBenBigBen
7,7253820
BigBen - Sorry that took so long to test, the COL walked in - which is odd for a Sunday. However, your solution worked! Awesome work. I cannot thank you enough.
– Brian Masoner
Mar 24 at 17:22
Great, glad to help.
– BigBen
Mar 24 at 17:25
add a comment |
Maybe something like this, using the inbuilt properties of a ListObject. Change the sheet and table names as needed.
Sub PasteMyFormula()
Dim myTbl As ListObject
Set myTbl = ThisWorkbook.Sheets("Sheet1").ListObjects("Table1")
With myTbl
If Not Intersect(ActiveCell, .DataBodyRange) Is Nothing Then
.DataBodyRange(.ListRows.Count, ActiveCell.Column).Copy
ActiveCell.PasteSpecial xlPasteAllExceptBorders
Application.CutCopyMode = False
End If
End With
End Sub
answered Mar 24 at 16:49
BigBenBigBen
7,7253820
BigBen - Sorry that took so long to test, the COL walked in - which is odd for a Sunday. However, your solution worked! Awesome work. I cannot thank you enough.
– Brian Masoner
Mar 24 at 17:22
Great, glad to help.
– BigBen
Mar 24 at 17:25
add a comment |
Maybe something like this, using the inbuilt properties of a ListObject. Change the sheet and table names as needed.
Sub PasteMyFormula()
Dim myTbl As ListObject
Set myTbl = ThisWorkbook.Sheets("Sheet1").ListObjects("Table1")
With myTbl
If Not Intersect(ActiveCell, .DataBodyRange) Is Nothing Then
.DataBodyRange(.ListRows.Count, ActiveCell.Column).Copy
ActiveCell.PasteSpecial xlPasteAllExceptBorders
Application.CutCopyMode = False
End If
End With
End Sub
answered Mar 24 at 16:49
BigBenBigBen
7,7253820
Maybe something like this, using the inbuilt properties of a ListObject. Change the sheet and table names as needed.
Sub PasteMyFormula()
Dim myTbl As ListObject
Set myTbl = ThisWorkbook.Sheets("Sheet1").ListObjects("Table1")
With myTbl
If Not Intersect(ActiveCell, .DataBodyRange) Is Nothing Then
.DataBodyRange(.ListRows.Count, ActiveCell.Column).Copy
ActiveCell.PasteSpecial xlPasteAllExceptBorders
Application.CutCopyMode = False
End If
End With
End Sub
answered Mar 24 at 16:49
BigBenBigBen
7,7253820
answered Mar 24 at 16:49
BigBenBigBen
7,7253820
answered Mar 24 at 16:49
BigBenBigBen
7,7253820
answered Mar 24 at 16:49
BigBenBigBen
7,7253820
7,7253820
BigBen - Sorry that took so long to test, the COL walked in - which is odd for a Sunday. However, your solution worked! Awesome work. I cannot thank you enough.
– Brian Masoner
Mar 24 at 17:22
Great, glad to help.
– BigBen
Mar 24 at 17:25
add a comment |
BigBen - Sorry that took so long to test, the COL walked in - which is odd for a Sunday. However, your solution worked! Awesome work. I cannot thank you enough.
– Brian Masoner
Mar 24 at 17:22
Great, glad to help.
– BigBen
Mar 24 at 17:25
BigBen - Sorry that took so long to test, the COL walked in - which is odd for a Sunday. However, your solution worked! Awesome work. I cannot thank you enough.
– Brian Masoner
Mar 24 at 17:22
BigBen - Sorry that took so long to test, the COL walked in - which is odd for a Sunday. However, your solution worked! Awesome work. I cannot thank you enough.
– Brian Masoner
Mar 24 at 17:22
Great, glad to help.
– BigBen
Mar 24 at 17:25
Great, glad to help.
– BigBen
Mar 24 at 17:25
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f55325935%2fhow-to-copy-a-formula-hidden-in-a-row-at-the-bottom-of-a-table-to-the-active-cel%23new-answer', 'question_page');
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function ()
StackExchange.helpers.onClickDraftSave('#login-link');
);
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Sub PasteFormula() ' ' PasteFormula Macro ' Pastes a formula from the table's bottom row, that is hidden, into the active cell of the same column. ' ' ActiveCell.PasteSpecial xlPasteAllExceptBorders ActiveSheet.Paste End Sub
– Brian Masoner
Mar 24 at 16:30
I am new to posting into forums. Please excuse my clunky attempts to share.
– Brian Masoner
Mar 24 at 16:31
gotcha. I edited the code into your question. Please make any changes to the code if the edit is not correct.
– BigBen
Mar 24 at 16:31
I have researched this thoroughly and can only find examples where the cell to be copied is a predefined cell in the worksheet. For my solution to work correctly, the cell to be copied will change with respect to the column where the active cell is located.
– Brian Masoner
Mar 24 at 16:32
Is the table formatted as an Excel table (using Ctrl+T)?
– BigBen
Mar 24 at 16:33