VBA macro with ActiveWorkbook not working from Personal.xlsb

[Zand]

Hello,

I know what causes it but cannot get it to work. Probably simple but my VBA knowledge is not enough.

I build a macro that formats a data dump from a system so it is usable. Remove colums, move colums, insert colums, make data readable with concatenate, format cells, set filter etc.
At one point I open a Template xlsx (same directory), Copy a Worksheet to ThisWorkbook, close that Template again and use data from that new inserted Worksheet to sort further.

The macro works perfectly, but only when I paste into the Workbook itself. After that I pasted it into Personal.xlsb to have it available for reuse and then it goes wrong.

I found out that it has to do with ActiveWorkbook or ThisWorkbook that acts as if Personal is the sheet/workbook to work from. ThisWorkbook is the one with the code... so Personal.
How to get around this, it should work on the open Workbook, not the Personal.

I tried all sorts of things with ActivesWorkbook and ThisWorkbook, declare dim for ActiveWorkbook.Name and use that to get onto the Open Workbook but all runs into VAB errors.

ActiveWorkbook.Save
Workbooks.Open ("H:\...\Macro\Kostenoverzicht template.xlsx")
Windows("Kostenoverzicht template.xlsx").Activate
Sheets("06 nummer en naam").Select
Sheets("06 nummer en naam").Copy After:=ThisWorkbook.Sheets(1)
Windows("Kostenoverzicht template.xlsx").Activate
ActiveWorkbook.Close SaveChanges:=True

Has anyone a neat trick to get this code to also work from Personal.xlsb?

Thx for now already...

Greetings, Zand

 

[MARK858]

Sheets("06 nummer en naam").Copy After:=ThisWorkbook.Sheets(1)

Are you really trying to paste the sheet into the personal workbook (which is ThisWorkbook)? Kostenoverzicht template.xlsx is the ActiveWorkbook (although personally I would refer to it by name as it is known)

 

[Zand]

Hello Mark,

Thx for your reply...
No, that is the issue. VBA sees Personal with has the code as the "ThisWorkbook".
When I use the code in the Workbook itself (tested) than that is "ThisWorkbook" and it works fine.

For re-use I want the code in Personal Workbook of course.

So now with code in Personal it tries to copy and paste in the wrong (=Personal) Workbook and that is not as it is supposed to do.

I was thinking of hardcoding the filename to open (works) and filename to copy to but cannot get that to work.
I can set a variable containing filename ThisWorkbook.Name but then run into errors when want to use the variable (read: do not know the correct way of using variable "nameWB").

Something like:
Open Template Workbook (can do, fixed path and filename)
Copy Worksheet (fixed name)
Does not work: Paste Worksheet in Workbook with name variable "nameWB" (filename changes! that is the issue, therefor I used ThisWorkbook)
Close Template Workbook
Activate Workbook "nameWB"


Greetings, Pim

 

[MARK858]

ThisWorkbook is ALWAYS the workbook the code resides in and in your case that is the Personal Workbook.

Set nameWB = Workbooks.Open ("H:\...\Macro\Kostenoverzicht template.xlsx")
Sheets("06 nummer en naam").Copy After:=nameWB.Sheets(1)

Assuming it is the worbook you have opened you want it to paste in although that is the active workbook (which would be the same as Sheets("06 nummer en naam"))

 

[Zand]

Hello Mark,

Looks like it... I will test it tomorrow!
Thx

Greetings, Pim

 

[Zand]

Hello Mark,

I wanted it the other way around, so copy from Template and paste into nameWB.

After some hours of fiddling, I got I working (cannot really get into the logic of Objects, Methods, Properties (-; ) .

I made it like this:

Dim nameWB As Workbook (Workbook I am working on, not by a fixed filename, changes by date)
Dim nameWBT As Workbook (Template Workbook with fixed name)

Set nameWB = ActiveWorkbook (The open Workbook workfile is set to nameWB)

Workbooks.Open ("M:\Afdeling\Linda\communicatie\KPN facturen\Kostenoverzicht KPN 2024\Kostenoverzicht template.xlsx") (Template Workbook is openend)
Set nameWBT = ActiveWorkbook (Template Workbook is set to nameWBT)

nameWBT.Activate (Template Workbook is activated)
Sheets("06 nummer en naam").Select (Worksheet to copy is selected)
Sheets("06 nummer en naam").Copy After:=nameWB.Sheets(1) (Worksheet is pasted at the end of nameWB Workbook)
nameWBT.Activate (Template Workbook is activated again)
ActiveWorkbook.Close SaveChanges:=True (Template Workbook is closed)
nameWB.Activate (nameWB main Workbook is activated again)
Template closes and "Active" (not This) Workbook finishes the macro nicely.

Thankx for the kickstart... good practice!

Greetings, Pim

 

[MARK858]

Happy you have it sorted.

You'll probably find that you don't need the line below

Sheets("06 nummer en naam").Select (Worksheet to copy is selected)

 

[Zand]

Ehh yes I do now (((-; Is a leftover from earlier tries... did not notice it, Thanx