command button execute copy and paste of worksheets into a new workbook and then automatically runs a macro...
I am working from an existing workbook in a worksheet(xlsb). I am looking to use a activeX control command button to copy and paste (values only and protect cell values or a value lock) two sheets of existing workbook into a new workbook. I then need a macro to run in the new workbook that deletes column/rows. I am very new to VBA and having no success.
The code is working, but we are looking to make the column/row deletion code of 'Sub Macro_RUN_IN_New_Workbook' execute after the sheets are copy and pasted into the new workbook. Presently the code executes in both the existing workbook and the new workbook. We need the code to run only in the new workbook.
Here is a truncated piece of the code:
Private Sub CommandButton1_Click()
Dim Acmebook As Workbook
Dim Acmesheet As Worksheet
Set Acmebook = ActiveWorkbook
Set Acmesheet = Acmebook.Worksheets("Sheet To Be Copied")
Sub Macro_RUN_IN_New_Workbook()
'To remove N/A Sections'
If Range("L106:L104").Value = "N/A - N/A" Then
Rows("99:107").Delete
End If
End Sub
Worksheets(Array("Array1", "Arrays2")).Copy
With ActiveWorkbook
End With
Sheets("Array1").Cells.Copy
Sheets("Array1").Cells.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Call Worksheets("Array1").Protect(UserInterfaceOnly:=True)
End Sub
vba
asked Dec 31 '18 at 19:42
Kelso_ExcelsKelso_Excels
11
add a comment |
I am working from an existing workbook in a worksheet(xlsb). I am looking to use a activeX control command button to copy and paste (values only and protect cell values or a value lock) two sheets of existing workbook into a new workbook. I then need a macro to run in the new workbook that deletes column/rows. I am very new to VBA and having no success.
The code is working, but we are looking to make the column/row deletion code of 'Sub Macro_RUN_IN_New_Workbook' execute after the sheets are copy and pasted into the new workbook. Presently the code executes in both the existing workbook and the new workbook. We need the code to run only in the new workbook.
Here is a truncated piece of the code:
Private Sub CommandButton1_Click()
Dim Acmebook As Workbook
Dim Acmesheet As Worksheet
Set Acmebook = ActiveWorkbook
Set Acmesheet = Acmebook.Worksheets("Sheet To Be Copied")
Sub Macro_RUN_IN_New_Workbook()
'To remove N/A Sections'
If Range("L106:L104").Value = "N/A - N/A" Then
Rows("99:107").Delete
End If
End Sub
Worksheets(Array("Array1", "Arrays2")).Copy
With ActiveWorkbook
End With
Sheets("Array1").Cells.Copy
Sheets("Array1").Cells.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Call Worksheets("Array1").Protect(UserInterfaceOnly:=True)
End Sub
vba
asked Dec 31 '18 at 19:42
Kelso_ExcelsKelso_Excels
11
1
So what doesn't work? Please edit the question, be specific, and describe what is wrong, where it is failing and any error messages.
– DavidPostill♦
Dec 31 '18 at 20:28
No error messages. The code is working, but we are looking to make the column/row deletion code of 'Sub Macro_RUN_IN_New_Workbook' execute after the sheets are copy and pasted into the new workbook. Presently the code executes in both the existing workbook and the new workbook. We need the code to run only in the new workbook.
– Kelso_Excels
Dec 31 '18 at 20:42
1
Please do not add information in comments, edit your question instead and let us know what you've tried so far
– cybernetic.nomad
Dec 31 '18 at 20:51
Apologies I have edited the original question. Thank you for helping.
– Kelso_Excels
Dec 31 '18 at 23:45
We were able to troubleshoot a solution. Utilized This.Workbook and Active.Workbook in the IF Statements. Thank you greatly for looking into my post and commenting. I aspire to be a better contributor as I grow in experience.
– Kelso_Excels
Jan 2 at 21:00
add a comment |
I am working from an existing workbook in a worksheet(xlsb). I am looking to use a activeX control command button to copy and paste (values only and protect cell values or a value lock) two sheets of existing workbook into a new workbook. I then need a macro to run in the new workbook that deletes column/rows. I am very new to VBA and having no success.
The code is working, but we are looking to make the column/row deletion code of 'Sub Macro_RUN_IN_New_Workbook' execute after the sheets are copy and pasted into the new workbook. Presently the code executes in both the existing workbook and the new workbook. We need the code to run only in the new workbook.
Here is a truncated piece of the code:
Private Sub CommandButton1_Click()
Dim Acmebook As Workbook
Dim Acmesheet As Worksheet
Set Acmebook = ActiveWorkbook
Set Acmesheet = Acmebook.Worksheets("Sheet To Be Copied")
Sub Macro_RUN_IN_New_Workbook()
'To remove N/A Sections'
If Range("L106:L104").Value = "N/A - N/A" Then
Rows("99:107").Delete
End If
End Sub
Worksheets(Array("Array1", "Arrays2")).Copy
With ActiveWorkbook
End With
Sheets("Array1").Cells.Copy
Sheets("Array1").Cells.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Call Worksheets("Array1").Protect(UserInterfaceOnly:=True)
End Sub
vba
asked Dec 31 '18 at 19:42
Kelso_ExcelsKelso_Excels
11
I am working from an existing workbook in a worksheet(xlsb). I am looking to use a activeX control command button to copy and paste (values only and protect cell values or a value lock) two sheets of existing workbook into a new workbook. I then need a macro to run in the new workbook that deletes column/rows. I am very new to VBA and having no success.
The code is working, but we are looking to make the column/row deletion code of 'Sub Macro_RUN_IN_New_Workbook' execute after the sheets are copy and pasted into the new workbook. Presently the code executes in both the existing workbook and the new workbook. We need the code to run only in the new workbook.
Here is a truncated piece of the code:
Private Sub CommandButton1_Click()
Dim Acmebook As Workbook
Dim Acmesheet As Worksheet
Set Acmebook = ActiveWorkbook
Set Acmesheet = Acmebook.Worksheets("Sheet To Be Copied")
Sub Macro_RUN_IN_New_Workbook()
'To remove N/A Sections'
If Range("L106:L104").Value = "N/A - N/A" Then
Rows("99:107").Delete
End If
End Sub
Worksheets(Array("Array1", "Arrays2")).Copy
With ActiveWorkbook
End With
Sheets("Array1").Cells.Copy
Sheets("Array1").Cells.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Call Worksheets("Array1").Protect(UserInterfaceOnly:=True)
End Sub
vba
vba
asked Dec 31 '18 at 19:42
Kelso_ExcelsKelso_Excels
11
asked Dec 31 '18 at 19:42
Kelso_ExcelsKelso_Excels
11
edited Dec 31 '18 at 23:44
Kelso_Excels
asked Dec 31 '18 at 19:42
Kelso_ExcelsKelso_Excels
11
asked Dec 31 '18 at 19:42
Kelso_ExcelsKelso_Excels
11
asked Dec 31 '18 at 19:42
Kelso_ExcelsKelso_Excels
11
11
1
So what doesn't work? Please edit the question, be specific, and describe what is wrong, where it is failing and any error messages.
– DavidPostill♦
Dec 31 '18 at 20:28
No error messages. The code is working, but we are looking to make the column/row deletion code of 'Sub Macro_RUN_IN_New_Workbook' execute after the sheets are copy and pasted into the new workbook. Presently the code executes in both the existing workbook and the new workbook. We need the code to run only in the new workbook.
– Kelso_Excels
Dec 31 '18 at 20:42
1
Please do not add information in comments, edit your question instead and let us know what you've tried so far
– cybernetic.nomad
Dec 31 '18 at 20:51
Apologies I have edited the original question. Thank you for helping.
– Kelso_Excels
Dec 31 '18 at 23:45
We were able to troubleshoot a solution. Utilized This.Workbook and Active.Workbook in the IF Statements. Thank you greatly for looking into my post and commenting. I aspire to be a better contributor as I grow in experience.
– Kelso_Excels
Jan 2 at 21:00
add a comment |
1
So what doesn't work? Please edit the question, be specific, and describe what is wrong, where it is failing and any error messages.
– DavidPostill♦
Dec 31 '18 at 20:28
No error messages. The code is working, but we are looking to make the column/row deletion code of 'Sub Macro_RUN_IN_New_Workbook' execute after the sheets are copy and pasted into the new workbook. Presently the code executes in both the existing workbook and the new workbook. We need the code to run only in the new workbook.
– Kelso_Excels
Dec 31 '18 at 20:42
1
Please do not add information in comments, edit your question instead and let us know what you've tried so far
– cybernetic.nomad
Dec 31 '18 at 20:51
Apologies I have edited the original question. Thank you for helping.
– Kelso_Excels
Dec 31 '18 at 23:45
We were able to troubleshoot a solution. Utilized This.Workbook and Active.Workbook in the IF Statements. Thank you greatly for looking into my post and commenting. I aspire to be a better contributor as I grow in experience.
– Kelso_Excels
Jan 2 at 21:00
1
1
So what doesn't work? Please edit the question, be specific, and describe what is wrong, where it is failing and any error messages.
– DavidPostill♦
Dec 31 '18 at 20:28
So what doesn't work? Please edit the question, be specific, and describe what is wrong, where it is failing and any error messages.
– DavidPostill♦
Dec 31 '18 at 20:28
No error messages. The code is working, but we are looking to make the column/row deletion code of 'Sub Macro_RUN_IN_New_Workbook' execute after the sheets are copy and pasted into the new workbook. Presently the code executes in both the existing workbook and the new workbook. We need the code to run only in the new workbook.
– Kelso_Excels
Dec 31 '18 at 20:42
No error messages. The code is working, but we are looking to make the column/row deletion code of 'Sub Macro_RUN_IN_New_Workbook' execute after the sheets are copy and pasted into the new workbook. Presently the code executes in both the existing workbook and the new workbook. We need the code to run only in the new workbook.
– Kelso_Excels
Dec 31 '18 at 20:42
1
1
Please do not add information in comments, edit your question instead and let us know what you've tried so far
– cybernetic.nomad
Dec 31 '18 at 20:51
Please do not add information in comments, edit your question instead and let us know what you've tried so far
– cybernetic.nomad
Dec 31 '18 at 20:51
Apologies I have edited the original question. Thank you for helping.
– Kelso_Excels
Dec 31 '18 at 23:45
Apologies I have edited the original question. Thank you for helping.
– Kelso_Excels
Dec 31 '18 at 23:45
We were able to troubleshoot a solution. Utilized This.Workbook and Active.Workbook in the IF Statements. Thank you greatly for looking into my post and commenting. I aspire to be a better contributor as I grow in experience.
– Kelso_Excels
Jan 2 at 21:00
We were able to troubleshoot a solution. Utilized This.Workbook and Active.Workbook in the IF Statements. Thank you greatly for looking into my post and commenting. I aspire to be a better contributor as I grow in experience.
– Kelso_Excels
Jan 2 at 21:00
add a comment |
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "3"
};
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%2fsuperuser.com%2fquestions%2f1389365%2fcommand-button-execute-copy-and-paste-of-worksheets-into-a-new-workbook-and-then%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Thanks for contributing an answer to Super User!
- 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%2fsuperuser.com%2fquestions%2f1389365%2fcommand-button-execute-copy-and-paste-of-worksheets-into-a-new-workbook-and-then%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
1
So what doesn't work? Please edit the question, be specific, and describe what is wrong, where it is failing and any error messages.
– DavidPostill♦
Dec 31 '18 at 20:28
No error messages. The code is working, but we are looking to make the column/row deletion code of 'Sub Macro_RUN_IN_New_Workbook' execute after the sheets are copy and pasted into the new workbook. Presently the code executes in both the existing workbook and the new workbook. We need the code to run only in the new workbook.
– Kelso_Excels
Dec 31 '18 at 20:42
1
Please do not add information in comments, edit your question instead and let us know what you've tried so far
– cybernetic.nomad
Dec 31 '18 at 20:51
Apologies I have edited the original question. Thank you for helping.
– Kelso_Excels
Dec 31 '18 at 23:45
We were able to troubleshoot a solution. Utilized This.Workbook and Active.Workbook in the IF Statements. Thank you greatly for looking into my post and commenting. I aspire to be a better contributor as I grow in experience.
– Kelso_Excels
Jan 2 at 21:00