Macro to reference workbook based on activesheet

[GeorgeF211]

Hi all,


I'm at a fairly basic understanding of macros, but I figured this would be a good little project to start getting my head around it.


Basically, what I'm looking to do is the following:


1) Extract the name of the active worksheet;


2) Open a workbook with the same name as this sheet, which will be in the same directory as the master document;


3) Search the newly opened workbook for the values in column B of the master document;


4) Return TRUE or FALSE depending on if the search was successful or not.


I'm not asking you guys to write this for me, but it would be great to get a few pointers on which commands you think I'll need to use.


Thanks,


George

 

[Luke M]

For 1 and 2, you'll want to use some of the following objects and methods

[pre]

ActiveSheet.Name
ThisWorkbook.Path 'What is the current folder
Application.Open(....) 'Fill in with path and file name. Don't forget the extension!
For number 3, I'm not exactly sure what values you are looking for. If it's a single value, you'll want to use either the [code].Find

method (record yourself doing a Find to get code), or you could use a worksheet function COUNTIF to just detect if a value is there.

Psuedo structure

xCount = WorksheetFunction.CountIf(SomeRange,SomeValue)
if xCount > 0 Then
msgbox "true"
else
msgbox "false"
end if[/code][/pre]
Purposely trying not to give the whole thing away, as it's best to learn by doing. And as always, if you get stuck, try recording a macro of you doing what you want to see if it will show you the code you need. Let us know if you need more help.

 

[GeorgeF211]

Thanks Luke, exactly the kind of thing I was looking for (I agree, playing about is the best way to learn these things).