Macro will only work in the file where I created it

[LouiseZ]

Hi,

I have created a macro (my first ever), and it works nicely in the workbook where it was created. However, I get the same kind of data several times a week and that data is imported into a new workbook, and I'd like to use the macro I created to calculate some things in the new workbooks. Whenever I try, it says "Runtime error 9" and when I define this it says "Identifier under cursor is not recognized". I have watched several tutorial videos etc. but I just can't figure out what the problem is...
Can someone please help? Below is a snapshot of a part of the code, I assume the yellow part is where the problem is, but I don't know where the problem lies. I don't want the macro tied to a particular workbook because then I'd have to make a new one for each workbook, and that removes the whole idea of it - so I understand that there must be a solution to this?

Very grateful for any help!

 

[jkpieterse]

The problem is with the ListObjects("xxx") bit, the name "xxx" apparently isn't always the same. If there is only ever one ListObject (Table) on the worksheet, simply replace that with ListObjects(1) everywhere in your code.

 

[LouiseZ]

The problem is with the ListObjects("xxx") bit, the name "xxx" apparently isn't always the same. If there is only ever one ListObject (Table) on the worksheet, simply replace that with ListObjects(1) everywhere in your code.

Hi,

Thank you for your reply, I really appreciate the help.
However, I am not sure I understand what you mean. I created the macro from one workbook containing a big table with many columns, and I want the macro to select some columns and calculate things (MFI) for me, as you can probably understand from the part of the code you can see. The file/workbook I created the macro in is called "AE825_AE826" - should I change every part where it says that to "1"?

Thanks in advance!

 

[jkpieterse]

Hi Louise,

You could try replacing every ActiveSheet.ListObjects("AE825_AE826") with ActiveSheet.ListObjects(1)

 

[LouiseZ]

Hi Louise,

You could try replacing every ActiveSheet.ListObjects("AE825_AE826") with ActiveSheet.ListObjects(1)

Hi Jan,

Thank you. I tried replacing this everywhere in the code, but then I get this message when I try to run:

It then says when I define it "Identifier under cursor is not recognized" (same as it said before but for a different location). What am I doing wrong?

Thanks for your help!

 

[jkpieterse]

Hard to say without the workbook I'm afraid.

 

[LouiseZ]

Hard to say without the workbook I'm afraid.

Hi again,

As I understand it after further research, my problem is that I made the macro in one specific data set, and I can't get it to work in another data set since the names change with every new data set. I am fine with having to use the same workbook/file every time and just copy the new data into that so that the macro can run, but I can't get around that the names will differ since I don't have the same names/numbers/tags in every experiment. For example, in one data set the data can be called "AE822" and in another "AE826", but the data is put into the same column every time (since I extract it from the same program).
From what I understand I could get around this problem if I created a macro where I use the "Find"-function instead of as I am doing now, filtering different columns. The word I am looking for in column C is for example "analysis", then in column G I want the word "CD163" (for example), and then I want to copy the data from a column M and paste down below. Then instead of the word "analysis" in column C, I want the word "isotype", and I want to copy the values from column M down below and divide those with the data from "analysis" that I just pasted.
However, since I have filtered the column itself, with for example the word "analysis", it becomes very specific in the code and thus won't work if for example AE822 changes to AE826 etc.
Is there a way around this?

A small example of how the file looks:

Grateful for any help!

 

[jkpieterse]

What type of file does your experiment produce exactly? Is it a csv or txt file perhaps?

 

[LouiseZ]

What type of file does your experiment produce exactly? Is it a csv or txt file perhaps?

Hi,

It comes out of the software Kaluza as a CVS file, but I have created another (functional) macro that converts that into an Excel workbook and transforms the data into columns. Is that part of the problem, you think?

 

[jkpieterse]

Can you perhaps edit two dataset files in Notepad, replacing sensitive information with nonsense and deleting all but -say- five rows of data? Please upload that to somewhere and share a link here and also show how you want the end result to look like.