Applying a Macro with Keeping Formulas
I want to keep my existing formula after I copy a different value to the related cell.
I am beginner of Excel VBA and there is a problem that I need to solve. I have value of previous period cumulative total at cell A1. Cell B1 is the value of this month. Lastly C1, it sums the previous period and this month and gives the cumulative total value.
So I've already written a macro for this but next month I need to copy the value at C1 and paste it to A1 so it will automatically sum a1+b1 again and write to c1. However when I apply the macro for copying the value it destroys the sum formula.
Sub sumfunc()
Range("C1").Formula = "=A1+B1"
Range("C1:C3").FillDown
End Sub
Sub copyfunc()
Worksheets("Sheet1").Range("C1:C3").Copy _
Destination:=Worksheets("Sheet1").Range("A1")
End Sub
excel vba
edited Jan 7 at 8:07
Pᴇʜ
23.1k62950
asked Jan 1 at 10:55
AlphaCentaAlphaCenta
153
add a comment |
I want to keep my existing formula after I copy a different value to the related cell.
I am beginner of Excel VBA and there is a problem that I need to solve. I have value of previous period cumulative total at cell A1. Cell B1 is the value of this month. Lastly C1, it sums the previous period and this month and gives the cumulative total value.
So I've already written a macro for this but next month I need to copy the value at C1 and paste it to A1 so it will automatically sum a1+b1 again and write to c1. However when I apply the macro for copying the value it destroys the sum formula.
Sub sumfunc()
Range("C1").Formula = "=A1+B1"
Range("C1:C3").FillDown
End Sub
Sub copyfunc()
Worksheets("Sheet1").Range("C1:C3").Copy _
Destination:=Worksheets("Sheet1").Range("A1")
End Sub
excel vba
edited Jan 7 at 8:07
Pᴇʜ
23.1k62950
asked Jan 1 at 10:55
AlphaCentaAlphaCenta
153
If you copy that formula without absolute cell references, you end up with #REF! error because you are trying to reference a column that is left of column A. If you use absolute references then you get a circular reference because the formula contains a reference to the cell it is in. What are you actually trying to do?
– user10852207
Jan 1 at 11:02
Exactly that's what i've got. #REF! error. So basically i want it to keep summing when i copy C1 value to A1. Again and again. Next month i will be copying the previous month's value(C1) to A1 and write another value manually to B1 so that it will sum again to C1. At the end i will be applying for a range.
– AlphaCenta
Jan 1 at 11:19
add a comment |
I want to keep my existing formula after I copy a different value to the related cell.
I am beginner of Excel VBA and there is a problem that I need to solve. I have value of previous period cumulative total at cell A1. Cell B1 is the value of this month. Lastly C1, it sums the previous period and this month and gives the cumulative total value.
So I've already written a macro for this but next month I need to copy the value at C1 and paste it to A1 so it will automatically sum a1+b1 again and write to c1. However when I apply the macro for copying the value it destroys the sum formula.
Sub sumfunc()
Range("C1").Formula = "=A1+B1"
Range("C1:C3").FillDown
End Sub
Sub copyfunc()
Worksheets("Sheet1").Range("C1:C3").Copy _
Destination:=Worksheets("Sheet1").Range("A1")
End Sub
excel vba
edited Jan 7 at 8:07
Pᴇʜ
23.1k62950
asked Jan 1 at 10:55
AlphaCentaAlphaCenta
153
I want to keep my existing formula after I copy a different value to the related cell.
I am beginner of Excel VBA and there is a problem that I need to solve. I have value of previous period cumulative total at cell A1. Cell B1 is the value of this month. Lastly C1, it sums the previous period and this month and gives the cumulative total value.
So I've already written a macro for this but next month I need to copy the value at C1 and paste it to A1 so it will automatically sum a1+b1 again and write to c1. However when I apply the macro for copying the value it destroys the sum formula.
Sub sumfunc()
Range("C1").Formula = "=A1+B1"
Range("C1:C3").FillDown
End Sub
Sub copyfunc()
Worksheets("Sheet1").Range("C1:C3").Copy _
Destination:=Worksheets("Sheet1").Range("A1")
End Sub
excel vba
excel vba
edited Jan 7 at 8:07
Pᴇʜ
23.1k62950
asked Jan 1 at 10:55
AlphaCentaAlphaCenta
153
edited Jan 7 at 8:07
Pᴇʜ
23.1k62950
asked Jan 1 at 10:55
AlphaCentaAlphaCenta
153
edited Jan 7 at 8:07
Pᴇʜ
23.1k62950
edited Jan 7 at 8:07
Pᴇʜ
23.1k62950
edited Jan 7 at 8:07
Pᴇʜ
23.1k62950
23.1k62950
asked Jan 1 at 10:55
AlphaCentaAlphaCenta
153
asked Jan 1 at 10:55
AlphaCentaAlphaCenta
153
asked Jan 1 at 10:55
AlphaCentaAlphaCenta
153
153
If you copy that formula without absolute cell references, you end up with #REF! error because you are trying to reference a column that is left of column A. If you use absolute references then you get a circular reference because the formula contains a reference to the cell it is in. What are you actually trying to do?
– user10852207
Jan 1 at 11:02
Exactly that's what i've got. #REF! error. So basically i want it to keep summing when i copy C1 value to A1. Again and again. Next month i will be copying the previous month's value(C1) to A1 and write another value manually to B1 so that it will sum again to C1. At the end i will be applying for a range.
– AlphaCenta
Jan 1 at 11:19
add a comment |
If you copy that formula without absolute cell references, you end up with #REF! error because you are trying to reference a column that is left of column A. If you use absolute references then you get a circular reference because the formula contains a reference to the cell it is in. What are you actually trying to do?
– user10852207
Jan 1 at 11:02
Exactly that's what i've got. #REF! error. So basically i want it to keep summing when i copy C1 value to A1. Again and again. Next month i will be copying the previous month's value(C1) to A1 and write another value manually to B1 so that it will sum again to C1. At the end i will be applying for a range.
– AlphaCenta
Jan 1 at 11:19
If you copy that formula without absolute cell references, you end up with #REF! error because you are trying to reference a column that is left of column A. If you use absolute references then you get a circular reference because the formula contains a reference to the cell it is in. What are you actually trying to do?
– user10852207
Jan 1 at 11:02
If you copy that formula without absolute cell references, you end up with #REF! error because you are trying to reference a column that is left of column A. If you use absolute references then you get a circular reference because the formula contains a reference to the cell it is in. What are you actually trying to do?
– user10852207
Jan 1 at 11:02
Exactly that's what i've got. #REF! error. So basically i want it to keep summing when i copy C1 value to A1. Again and again. Next month i will be copying the previous month's value(C1) to A1 and write another value manually to B1 so that it will sum again to C1. At the end i will be applying for a range.
– AlphaCenta
Jan 1 at 11:19
Exactly that's what i've got. #REF! error. So basically i want it to keep summing when i copy C1 value to A1. Again and again. Next month i will be copying the previous month's value(C1) to A1 and write another value manually to B1 so that it will sum again to C1. At the end i will be applying for a range.
– AlphaCenta
Jan 1 at 11:19
add a comment |
1 Answer
1
active
oldest
votes
If you copy that formula without absolute cell references, you end up with #REF! error because you are trying to reference a column that is left of column A. If you use absolute references then you get a circular reference because the formula contains a reference to the cell it is in.
You need to copy the value returned from the formula and add it to the target, not the formula itself. In this way, the value from the formula in column C will reflect a growing sum.
Sub sumfunc()
Worksheets("Sheet1").Range("C1:C3").Formula = "=A1+B1"
End Sub
Sub copyfunc()
with Worksheets("Sheet1")
.Range("C1:C3").Copy
.Range("A1").pastespecial paste:=xlpastevalues, operation:=xladd
end with
End Sub
answered Jan 1 at 11:32
user10852207user10852207
2263
That's what i need. Thank you so much !
– AlphaCenta
Jan 1 at 11:43
@AlphaCenta If this answered your question please mark it as solved: Accepting Answers: How does it work?
– Pᴇʜ
Jan 7 at 8:08
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%2f53994868%2fapplying-a-macro-with-keeping-formulas%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
If you copy that formula without absolute cell references, you end up with #REF! error because you are trying to reference a column that is left of column A. If you use absolute references then you get a circular reference because the formula contains a reference to the cell it is in.
You need to copy the value returned from the formula and add it to the target, not the formula itself. In this way, the value from the formula in column C will reflect a growing sum.
Sub sumfunc()
Worksheets("Sheet1").Range("C1:C3").Formula = "=A1+B1"
End Sub
Sub copyfunc()
with Worksheets("Sheet1")
.Range("C1:C3").Copy
.Range("A1").pastespecial paste:=xlpastevalues, operation:=xladd
end with
End Sub
answered Jan 1 at 11:32
user10852207user10852207
2263
That's what i need. Thank you so much !
– AlphaCenta
Jan 1 at 11:43
@AlphaCenta If this answered your question please mark it as solved: Accepting Answers: How does it work?
– Pᴇʜ
Jan 7 at 8:08
add a comment |
If you copy that formula without absolute cell references, you end up with #REF! error because you are trying to reference a column that is left of column A. If you use absolute references then you get a circular reference because the formula contains a reference to the cell it is in.
You need to copy the value returned from the formula and add it to the target, not the formula itself. In this way, the value from the formula in column C will reflect a growing sum.
Sub sumfunc()
Worksheets("Sheet1").Range("C1:C3").Formula = "=A1+B1"
End Sub
Sub copyfunc()
with Worksheets("Sheet1")
.Range("C1:C3").Copy
.Range("A1").pastespecial paste:=xlpastevalues, operation:=xladd
end with
End Sub
answered Jan 1 at 11:32
user10852207user10852207
2263
That's what i need. Thank you so much !
– AlphaCenta
Jan 1 at 11:43
@AlphaCenta If this answered your question please mark it as solved: Accepting Answers: How does it work?
– Pᴇʜ
Jan 7 at 8:08
add a comment |
If you copy that formula without absolute cell references, you end up with #REF! error because you are trying to reference a column that is left of column A. If you use absolute references then you get a circular reference because the formula contains a reference to the cell it is in.
You need to copy the value returned from the formula and add it to the target, not the formula itself. In this way, the value from the formula in column C will reflect a growing sum.
Sub sumfunc()
Worksheets("Sheet1").Range("C1:C3").Formula = "=A1+B1"
End Sub
Sub copyfunc()
with Worksheets("Sheet1")
.Range("C1:C3").Copy
.Range("A1").pastespecial paste:=xlpastevalues, operation:=xladd
end with
End Sub
answered Jan 1 at 11:32
user10852207user10852207
2263
If you copy that formula without absolute cell references, you end up with #REF! error because you are trying to reference a column that is left of column A. If you use absolute references then you get a circular reference because the formula contains a reference to the cell it is in.
You need to copy the value returned from the formula and add it to the target, not the formula itself. In this way, the value from the formula in column C will reflect a growing sum.
Sub sumfunc()
Worksheets("Sheet1").Range("C1:C3").Formula = "=A1+B1"
End Sub
Sub copyfunc()
with Worksheets("Sheet1")
.Range("C1:C3").Copy
.Range("A1").pastespecial paste:=xlpastevalues, operation:=xladd
end with
End Sub
answered Jan 1 at 11:32
user10852207user10852207
2263
edited Jan 1 at 11:45
answered Jan 1 at 11:32
user10852207user10852207
2263
answered Jan 1 at 11:32
user10852207user10852207
2263
answered Jan 1 at 11:32
user10852207user10852207
2263
2263
That's what i need. Thank you so much !
– AlphaCenta
Jan 1 at 11:43
@AlphaCenta If this answered your question please mark it as solved: Accepting Answers: How does it work?
– Pᴇʜ
Jan 7 at 8:08
add a comment |
That's what i need. Thank you so much !
– AlphaCenta
Jan 1 at 11:43
@AlphaCenta If this answered your question please mark it as solved: Accepting Answers: How does it work?
– Pᴇʜ
Jan 7 at 8:08
That's what i need. Thank you so much !
– AlphaCenta
Jan 1 at 11:43
That's what i need. Thank you so much !
– AlphaCenta
Jan 1 at 11:43
@AlphaCenta If this answered your question please mark it as solved: Accepting Answers: How does it work?
– Pᴇʜ
Jan 7 at 8:08
@AlphaCenta If this answered your question please mark it as solved: Accepting Answers: How does it work?
– Pᴇʜ
Jan 7 at 8:08
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%2f53994868%2fapplying-a-macro-with-keeping-formulas%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
If you copy that formula without absolute cell references, you end up with #REF! error because you are trying to reference a column that is left of column A. If you use absolute references then you get a circular reference because the formula contains a reference to the cell it is in. What are you actually trying to do?
– user10852207
Jan 1 at 11:02
Exactly that's what i've got. #REF! error. So basically i want it to keep summing when i copy C1 value to A1. Again and again. Next month i will be copying the previous month's value(C1) to A1 and write another value manually to B1 so that it will sum again to C1. At the end i will be applying for a range.
– AlphaCenta
Jan 1 at 11:19