• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 387
  • Last Modified:

Problems with macros when I copy a worksheet

I have created an Excel worksheet with a couple of macros, but whenever I copy it for another week's task, the macros seem to keep the old reference.

I checked ALT F11 and the panel shows another worksheet. How can I delete the old worksheet and have only the latest one?

Also, how can I make the current macros reference only the worksheet I am working on at the moment?

Thanks in advance!
  • 3
  • 3
2 Solutions
Harry LeeCommented:

1) You don't have to worry about which file is listed in VBA Editor. It shows all active files the Excel has opened.

In your screenshot, what it mean is Excel is accessing two separate workbook, and found those modules. Once Excel is closed, and reopen, the list got updated.

2) The problem you are experiencing is due to coding error. There must be a lot of fixed references in your macros. Instead of having fixed references to the workbook and worksheet such as Worksheets("Sheet1"), you will have to update your macros to use ActiveSheet and ActiveWorkbook.

This way, no matter what file you are working on, the macros always work on the current workbook.

Can you upload your file and I can work on changing the macros to point to Current Workbook instead of fixed references?
BrejinskiAuthor Commented:
Thanks, HarryHYLee,
here's the file
BrejinskiAuthor Commented:
I just am not very sure how to use ActiveSheet and ActiveWorkbook. Any hint?
Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Rather than using ActiveSheet or Sheets("Sheet1") format for working with sheets...

Start your code with something like:

Dim ws as worksheet
Set ws = Activesheet
Set ws = Sheets("Sheet1")

Open in new window

Then work with the sheet in code like:

Open in new window

This way you can set the worksheet once at the top of your code to work with any method of Active / Explict names.

I hope this makes sense.

in your code this would be:
    Dim ws as worksheet
    Set ws = ActiveSheet  'this line for activesheet
    'set ws = Sheets("Plan1") 'or this line for sheet by name
    For X = 15 To 4000 Step 13
    ws.Cells(X, 1).Select
    Selection.Insert Shift:=xlDown
    Next X
    Application.CutCopyMode = False 'Tecla Esc
End Sub

Open in new window

Harry LeeCommented:

Can you explain in more detail where is each macros supposed to be ran?

Macro Name                              Sheet to run on

Or even better, explain how you want your workbook to work, and I can get the VBA codes written up for you.

So far as what I see, the Macros don't seem to do much at all.
BrejinskiAuthor Commented:
Hello, HarryHYLee,

The problem seems to be that, whenever I copy a worksheet and rename the new one its macros refence the previous one. That is: I have worksheet WS_14. I copy it with CTRL C and CTRL V and rename the new worksheet WS_15. The macros within WS_15 reference WS_14 and open it.

I want my macros to work independently even when I copy a worksheet. That is: when I copy WS_14 and rename it to WS_15, the last one will run the same macros, but for WS_15.

APAGA_14_em_diante()  - deletes lines from line number 14 until line 4000.

ESC()  - never mind, I don't remember exactly why I created this... :)

COLAR() - copy a part of the worksheet (A2:I13) and pastes this block below the first one.

I hope I have managed to be clear enough now.
Harry LeeCommented:

I think I know what your problem is. Your problem has nothing to do with Activesheet or Activeworkbook.

You problem is when you make a copy of the existing file and rename the new file, you have Excel running. So, as a result, the VBA recognize 2 sets of macros.

And when you run the macro you want, you may end up picking the macro from the existing workbook instead of the newly named workbook.

I know you have closed the existing file before opening up the newly created workbook, but Excel was not close, therefore there is bug going on with the VBA hanging on to the macros in the old workbook.

To solve your problem, you need to exit Excel completely when you do the copy, or more easily, instead of making a copy of the existing file in Windows Explorer, do a Save As inside Excel. That seem to solve the issue.

Also, I have made some improvement on your macros. Please check them out.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now