Is there a way to move cells to the left when a sheet is copied?
I am creating a 5 week look ahead labor planner. I currently have a function that displays weeks 2-5 from the previous sheet to weeks 1-4 on the copied sheet. The issue is when a task is finished or another task is inserted, the formula changes.
For example, the formula in C8 would display =Prev(I8). If a task is inserted and row 8 moves to row 9, the formula changes to =Prev(I9) causing the former row 8 to display row 9 of the previous sheet.
What I am looking for is a trigger event when a certain sheet is copied or a way to permanently and automatically copying the previous cells instead of using a formula.
Example of the sheets:
sheet 1: https://imgur.com/w2Sk8f9
copied sheet: https://imgur.com/TwWkfzZ
excel vba excel-vba
asked Nov 15 '18 at 17:22
phillgarcphillgarc
1
add a comment |
I am creating a 5 week look ahead labor planner. I currently have a function that displays weeks 2-5 from the previous sheet to weeks 1-4 on the copied sheet. The issue is when a task is finished or another task is inserted, the formula changes.
For example, the formula in C8 would display =Prev(I8). If a task is inserted and row 8 moves to row 9, the formula changes to =Prev(I9) causing the former row 8 to display row 9 of the previous sheet.
What I am looking for is a trigger event when a certain sheet is copied or a way to permanently and automatically copying the previous cells instead of using a formula.
Example of the sheets:
sheet 1: https://imgur.com/w2Sk8f9
copied sheet: https://imgur.com/TwWkfzZ
excel vba excel-vba
asked Nov 15 '18 at 17:22
phillgarcphillgarc
1
add a comment |
I am creating a 5 week look ahead labor planner. I currently have a function that displays weeks 2-5 from the previous sheet to weeks 1-4 on the copied sheet. The issue is when a task is finished or another task is inserted, the formula changes.
For example, the formula in C8 would display =Prev(I8). If a task is inserted and row 8 moves to row 9, the formula changes to =Prev(I9) causing the former row 8 to display row 9 of the previous sheet.
What I am looking for is a trigger event when a certain sheet is copied or a way to permanently and automatically copying the previous cells instead of using a formula.
Example of the sheets:
sheet 1: https://imgur.com/w2Sk8f9
copied sheet: https://imgur.com/TwWkfzZ
excel vba excel-vba
asked Nov 15 '18 at 17:22
phillgarcphillgarc
1
I am creating a 5 week look ahead labor planner. I currently have a function that displays weeks 2-5 from the previous sheet to weeks 1-4 on the copied sheet. The issue is when a task is finished or another task is inserted, the formula changes.
For example, the formula in C8 would display =Prev(I8). If a task is inserted and row 8 moves to row 9, the formula changes to =Prev(I9) causing the former row 8 to display row 9 of the previous sheet.
What I am looking for is a trigger event when a certain sheet is copied or a way to permanently and automatically copying the previous cells instead of using a formula.
Example of the sheets:
sheet 1: https://imgur.com/w2Sk8f9
copied sheet: https://imgur.com/TwWkfzZ
excel vba excel-vba
excel vba excel-vba
asked Nov 15 '18 at 17:22
phillgarcphillgarc
1
asked Nov 15 '18 at 17:22
phillgarcphillgarc
1
asked Nov 15 '18 at 17:22
phillgarcphillgarc
1
asked Nov 15 '18 at 17:22
phillgarcphillgarc
1
asked Nov 15 '18 at 17:22
phillgarcphillgarc
1
1
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
For example, you can build a click button that deletes the value in week 1 and pastes the others to the left
Sub example()
Sheets("Sheet1").Range("A1:F150").Clear
Sheets("Sheet1").Range("G1:Z150").Copy
Sheets("Sheet1").Range("A1").PasteSpecial xlValues
End Sub
Change the range, and get your result. Hope it is helpful.
answered Nov 15 '18 at 17:53
AnNgAnNg
261
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%2f53324834%2fis-there-a-way-to-move-cells-to-the-left-when-a-sheet-is-copied%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
For example, you can build a click button that deletes the value in week 1 and pastes the others to the left
Sub example()
Sheets("Sheet1").Range("A1:F150").Clear
Sheets("Sheet1").Range("G1:Z150").Copy
Sheets("Sheet1").Range("A1").PasteSpecial xlValues
End Sub
Change the range, and get your result. Hope it is helpful.
answered Nov 15 '18 at 17:53
AnNgAnNg
261
add a comment |
For example, you can build a click button that deletes the value in week 1 and pastes the others to the left
Sub example()
Sheets("Sheet1").Range("A1:F150").Clear
Sheets("Sheet1").Range("G1:Z150").Copy
Sheets("Sheet1").Range("A1").PasteSpecial xlValues
End Sub
Change the range, and get your result. Hope it is helpful.
answered Nov 15 '18 at 17:53
AnNgAnNg
261
add a comment |
For example, you can build a click button that deletes the value in week 1 and pastes the others to the left
Sub example()
Sheets("Sheet1").Range("A1:F150").Clear
Sheets("Sheet1").Range("G1:Z150").Copy
Sheets("Sheet1").Range("A1").PasteSpecial xlValues
End Sub
Change the range, and get your result. Hope it is helpful.
answered Nov 15 '18 at 17:53
AnNgAnNg
261
For example, you can build a click button that deletes the value in week 1 and pastes the others to the left
Sub example()
Sheets("Sheet1").Range("A1:F150").Clear
Sheets("Sheet1").Range("G1:Z150").Copy
Sheets("Sheet1").Range("A1").PasteSpecial xlValues
End Sub
Change the range, and get your result. Hope it is helpful.
answered Nov 15 '18 at 17:53
AnNgAnNg
261
answered Nov 15 '18 at 17:53
AnNgAnNg
261
answered Nov 15 '18 at 17:53
AnNgAnNg
261
answered Nov 15 '18 at 17:53
AnNgAnNg
261
261
add a comment |
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%2f53324834%2fis-there-a-way-to-move-cells-to-the-left-when-a-sheet-is-copied%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
