Problems with macros when I copy a worksheet

Hi,
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!
Delete-Excel-macros.PNG
BrejinskiAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Harry LeeCommented:
Brejinski,

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?
0
BrejinskiAuthor Commented:
Thanks, HarryHYLee,
here's the file
12-COMPRA-N-O-COMPRA-11-set.xlsm
0
BrejinskiAuthor Commented:
I just am not very sure how to use ActiveSheet and ActiveWorkbook. Any hint?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

SteveCommented:
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
'or
Set ws = Sheets("Sheet1")

Open in new window


Then work with the sheet in code like:
ws.range("A1")
ws.Rows.Count

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:
Sub COLAR()
    Dim ws as worksheet
    Set ws = ActiveSheet  'this line for activesheet
    'set ws = Sheets("Plan1") 'or this line for sheet by name
    ws.Range("A14").Select
    
    For X = 15 To 4000 Step 13
    ws.Range("A2:I13").Copy
    ws.Cells(X, 1).Select
    Selection.Insert Shift:=xlDown
    Next X
    
    ws.Range("A1").Select
    
    Application.CutCopyMode = False 'Tecla Esc
    
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Harry LeeCommented:
Brejinski,

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

Macro Name                              Sheet to run on
APAGA_14_em_diante()                  
ESC()                                    
COLAR()                                    

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.
0
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.
0
Harry LeeCommented:
Brejinski,

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.
12-COMPRA-N-O-COMPRA-13-set--.xlsm
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Applications

From novice to tech pro — start learning today.