VBA Macro Lookup sheet not there

**FlatFish** · 10-07-2021, 10:03 AM

Hi everyone,

I have attached four files. Report example is a report I receive monthly and don't do anything with besides changing the name. The script help word doc is actually a VBScript, but I pasted the code for it in a word doc as VBScript is an invalid file type. Template Help is the excel file the script runs and inside it are two macros. The final output is the Example file.

If you look at the Template Help file you will see a Lookup Sheet that Column Y in the Example file references. The issue I am having is in the Example File the Lookup Sheet is no longer there, therefore all of column Y will return "misc" because that's the default false value.

What I want is that lookup sheet to be in the Example file so that issue does not occur anymore.

I am not sure what I added or what I am missing in the Macro(s) that causes the sheet to not appear.

Any help would be greatly appreciated.

Thank you

**6StringJazzer** · 10-07-2021, 11:08 AM

The naming convention you are using for variables makes it difficult to read your code (wbBook1, wbBook2, wsSheet1, wsSheet2). Best practice is to make variable names meaningful, like wbTemplate and wbReport. Once I figured that out I was able to make this suggestion. Add the line of code shown in red. Your code is very specific to your environment (Outlook folders, file directory) and it would have taken too much time for me to set up a test, but you should be able to test this pretty quickly.

Please Login or Register  to view this content.

Also you declare and set but never use the variable misc. It is not needed.

You seem to declare variables but you should also use Option Explicit and require declarations. Doing so prevents a lot of bugs and runtime errors.

**6StringJazzer** · 10-07-2021, 11:10 AM

One other thing: I don't see why you are using a standalone VBScript file to kick this off. It would be simpler to just open the template file, and add a Workbook_Open sub to automatically run the macro when the workbook is opened.

**FlatFish** · 10-07-2021, 11:13 AM

Thank you very much for the response. I agree with all that you said about best practice. I recently started working at this company and this is old code from the previous employee. I planned to fix some things up and all, but I am unfamiliar with VBA and it's important to get these reports running ASAP.

Again, thank you for taking the time to help me. I am going to give it a try

VBA Macro Lookup sheet not there

LinkBack

Thread Tools

Rate This Thread

Display

VBA Macro Lookup sheet not there

Re: VBA Macro Lookup sheet not there

Re: VBA Macro Lookup sheet not there

Re: VBA Macro Lookup sheet not there

Thread Information

Users Browsing this Thread

Similar Threads

[SOLVED] Macro to lookup in other sheets based on cell as a sheet name

[SOLVED] Macro to lookup a value in a Sheet and find that value in another sheet and Copy paste

Macro to lookup data monthwise and aggregate the value in a new sheet

[SOLVED] Excel Macro to lookup sheet name with a range of cells and paste in the reference sheet

Macro to move to Sheet/cell based on lookup

macro to update sheet, lookup value and delete ranges

Macro to Lookup and Output to new sheet

Tags for this Thread

Bookmarks

Bookmarks

Posting Permissions