0

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

  • 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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.