Macro for Finding Sheet Names, then Copying/Pasting Values

Hello all,

First post here, been having trouble finding what I need. I have very limited VBA skills, however I'm learning quickly. If this information is posted somewhere else, please excuse me.

I have a file with many sheets, with one "Data" sheet that has various dependent data validation lists. In the sheet "Data":
"B2" is a drop-down list of tract names for new homes being built.
"B3" is a manual entry of a specific plan number within that tract name (list of plan numbers are in a separate sheet).
"B4" runs a VLOOKUP to indicate which sheet the selected information from "B2" is in (Sheet names are "C1", "C2", up to "C50"). Below my drop-downs, I have a range of cells that are referenced from another sheet, that need to be copied / pasted into the proper "C" sheet (range that needs to be copied is "B10:B50".

Along the top of each "C" sheet, is the plan numbers for each tract name. I need the Macro to first find the "C" sheet returned from my VLOOKUP (B4), select the sheet that is named that, then run across the top row ("F1:AH1"), find the matching number from "Data!B3", then jump down to row 680 and paste the values from "Data!B10:B50".

Seperately, I can manage to make these processes work, but I'm getting stuck making them all work together, in order, and keep getting errors with my syntax (due to my lack of experience).

It would be easier to just do this manually, however my company has so much information that is coming into this file so often, the Macro will make the data organization much faster.

Thanks in advance.

Post the code you have! Maybe we just make adjustments to what you've already done

Unfortunately while I was gone last week one of my colleagues decided to go in and fiddle with the file, and deleted my Macros for some unknown reason, so I'm back to no code at all currently. I can try to get back to where I was, but obviously the path I was on wasn't working correctly the first time.

If there are some fragments of code for each function I'm looking for, I can piece them together. Or, if someone would be gracious enough to create a template of the code and I can adjust it for my cells and sheet names.

No problem. Does B4 return actual sheet names, or is it an index (like 10 or 50)?

B4 returns values "C1", "C2", and so forth up to "C50", which are exactly the name of my sheets. B4's value is dependent upon which tract name is selected from B2's drop-down list. The macro would search B4 and find that sheet name. For instance, if the tract name selected returned "C5" into cell B4, the macro would find the sheet named "C5", then would search sheet "C5" for the plan number from "Data!B3" within sheet "C5", since the plan numbers are pre-listed across the top in F1:AH1. When it finds a match between "Data!B3" and a number within sheet "C5", the macro will jump down that column to row 680 and paste the values from "Data!B10:B50".

My main issue is getting the macro to search and select the desired sheet, then search and find the desired plan number.

I can attach the file if it is helpful.

Try this, and you could edit the first post and put the file in there of you'd like.

Please Login or Register  to view this content.

That works BEAUTIFULLY! Thank you for the speedy responses!