Copying a sheet from closed workbook into active workbook

I have a worksheet 'Lepingu lisa' in file Book1 that I'm trying to modify with a macro to look like the same sheet on Book2 file, but I'm having trouble already with the AV column.
I have an extra file that I'm trying to use, where the macro should take the value "1". But I get an error with this process.

So basically I'm trying to use macro for column AV and for table BI:BP

Never used a macro before, so not that sure what I'm doing so wrong here.

Or how should I debug this error?

EDIT: Using this code, but it doesn't seem to work... Only opens the source file and does nothing else. Tho it should copy the sheet and then close it as well.

Please Login or Register  to view this content.

Just as a suggestion (I'm not sure where you've got to in your task):

For the "big IF" statement used for cells in column AV, I'd recommend using a lookup table of 2 columns (this table could be on another sheet to keep it out of sight if required). The first column would hold the various text strings that might be included in column D cells [e.g. Aatrium/Fuajee, Kabinet/Büroo etc] and the second column has the value you want to associate with each such text string. Then a short IFERROR + VLOOKUP in the AV cell would generate the required result e.g. IFERROR(VLOOKUP(D3,address of the 2 column lookup table,2,false),"") for cell AV3 would generate the 1 or blank as required. It would make it a lot easier to add additional text strings that require a number 1 to be returned.

Thank You for the suggestion

I actually do have a seperate file for the list of names. I just don't know how to use it in my macro yet. Open to all suggestions

Also, if I saved the macro file as PERSONAL.XLSB and someone else is going to use it as well, does he/she has to have the macro file as well?


EDIT: The code I'm using at the moment is this. Tho I'd like to use the added file. I tried the Sub OpenWorkbook() and Sub CloseWorkbook() but could't get it to work.

Please Login or Register  to view this content.

You should be able to programmatically open the second workbook (I think it can be kept hidden if you don't want the user to see it) whenever the main workbook is opened. You would also close it when the main workbook is closed.
Alternatively, if the second workbook is on a SharePoint or OneDrive location, you can use the http reference to the second workbook in your IF or VLOOKUP formulae without requiring macros. So instead of something like

Please Login or Register  to view this content.

you'd use something along the lines of

Please Login or Register  to view this content.

The way I get the web address (not sure if there's a better way) is to open both workbooks in Excel online (browser) and make the formulas. This will automatically enter the web addresses.

But that's the formula that goes in AV column, right?

My goal, at the moment, is to get the main file take the sheet from 'Tüüpruumide nimestik büroopindadega', add it before sheet 'Lepingu lisa' in the main file and then I could use the given formulas. Right?
But all I'm able to do with macro code so far is open the 'Tüüpruumide nimestik büroopindadega' file and nothing else happens.

Any ideas what should I use to perform this task? Open other file -> copy the sheet from there -> close the opened file

EDIT: I found this code from another forum, but this seems to work with only one specific file I assume: Copy Before:=Workbooks("test.xlsm").Sheets(1). I have many with different names.

Please Login or Register  to view this content.

EDIT2: I'm surely doing something wrong here if even this code just opens the other file, doesn't copy the sheet nor close it.

Please Login or Register  to view this content.

Oh, okay, it does copy the sheet from source file into the active workbook, but it takes the PERSONAL.XLSB, which opens on its own, as the active workbook. And I think it copys the sheet to the source file as well, if it's the active workbook.
How could I change this?

The macro code that you have is contained in the PERSONAL.xlsb workbook. There's a line in that code that says

Please Login or Register  to view this content.

, so that is why the worksheet is being copied into the PERSONAL workbook.

If you want it to be copied into the workbook from which you run the macro, copy the macro into that other workbook and run it from there.

However, as noted in an earlier post above, if you have the source workbook stored on OneDrive, you wouldn't need to copy the relevant worksheet from one workbook to another workbook for the LOOKUP formulae to work. It is DEFINITELY possible for those formulae in column AV to refer to the OneDrive workbook (you need th ehttp address of the OneDrive workbook for the formulae). I have successfully done this myself- I have a 'tax database' workbook stored on OneDrive. Various solutions to tax questions link back to that workbook, without a need to copy anything into an individual solution workbook. This sounds like what you are trying to do!

If that takes care of your original question, please choose Thread Tools from the menu link above and mark this thread as SOLVED. You can also access the SOLVED tag by editing the opening post and choosing SOLVED from the drop-down to the left of the title box.

Also, if you have not already done so, remember that you can reward anyone who offered you help towards a solution for your issue by clicking the small star icon (* Add Reputation) located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of each of those who offered help.