Issues with Personal Macro Workbook [SOLVED]

[brightyoyo]

Hi


When I use my macros from my personal macro workbook they do not work, but if I use them in a workbook they work fine. Is there something else that has to be done in order to make them work in the personal macro workbook?

 

[Luke M]

Can you elaborate more on what "they do not work" means? Doesn't run, doesn't show up in list, does something unexpected, etc.

 

[brightyoyo]

Hi


They show up in the developers tab, but they do not work properly. For instance, I have a macro that imports data from another workbook. In the personal macro workbook it does not import any data although it appears that it is trying to run the macro without any results. Another macro is suppose to create a new sheet but does not create a new sheet, but once it appears to be attempting to run the macro.


Thanks

 

[brightyoyo]

Hi

The Personal Macro Workbook does not copy data from another workbook. It seems to open the workbook, but does not copy the data to the new workbook. Is there something I need to add to the macro to get it to copy the Data or anything else?

 

[SirJB7]

Hi, brightyoyo!


If you didn't receive further assistance with the information provided, consider uploading a sample file (including manual examples of desired output if applicable), it'd be very useful for those who read this and might be able to help you. Thank you. Give a look at the green sticky posts at this forums main page for uploading guidelines.


In this case indicate the macro involved, how it's triggered or executed and any other indication that may help to aid you.


Then someone who steps in here and could test it with other personal macro file different from his own one, maybe could find and post as solution for your issue. Your last post without any link to uploaded files and without any macro code pasted neither helps nor add significant information to the topic.


Regards!

 

[brightyoyo]

Hi

Here is a workbook with my macros in it and the two separate files needed for importing into the workbook. The main problem I am having is using the macros Import_from_BOM and Import_from_schematic in the personal macro workbook. The macros seem to run, but they do not import any data into my main workbook, but does import in to the personal macro file. I have also included my personal macro workbook.


https://www.dropbox.com/sh/67unlewr92vgm3z/XAGscYaB7G


Thank You

 

[SirJB7]

Hi, brightyoyo!


This is the first procedure in module Module1 on you personal macro workbook:

-----

[pre]

Sub Import_from_BOM()

'Copy from Workbook to Workbook
Dim wb As Workbook
Dim wb2 As Workbook
Set wb = ThisWorkbook
'~~> Get the first File
Ret1 = Application.GetOpenFilename("Excel Files (*.xls*), *.xls*", _
, "Please select first file")
Set wb2 = Workbooks.Open(Ret1)
Columns("A:G").Select
Selection.Copy
wb.Activate
Range("A1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

Application.CutCopyMode = False
wb.Activate
ActiveWorkbook.Close

End Sub[/pre]
-----


It uses 2 workbooks, one (wb2) selected and opened from a dialog and other (wb) as ThisWorkbook. Then it copies the 1st 7 columns of the sheet where wb2 happened to get saved from, to the active sheet of wb.


The main issue here is that wb refers to ThisWorkbook, in this case PERSONAL.XSLB, and that's why the data is copied into it. If you wanted that data got copied in the workbook that you had previously opened and selected as active when you run the macro from your personal macro workbook, you should replace the reference from ThisWorkbook to ActiveWorkbook as follows:

-----

Set wb = ActiveWorkbook

-----


Try checking all the other macros that doesn't work as expected to see if the issue is the same.


Regards!

 

[brightyoyo]

Hi SirJB7


It seems that was the problem. Once I checked all the macros and got rid of all the references to ThisWorkbook the macros worked.


Thank You so much for your constant help.

 

[SirJB7]

Hi, brightyoyo!

Glad you solved it. Thanks for your feedback and for your kind words too. And welcome back whenever needed or wanted.

Regards!