Copy only hardcoded values to the correct row and column of a another spreadsheet

ok let me summarise my understanding of what you want.

You do some work on sheet Forcast of the file Original-Forcast-Sheet.xls like you change values etc...

and

When you download at the beg of the next month a new file you want to be able to do what exactly on that new file that is the part that is not clear to me. (I guess we are only talking of the sheet Forcast correct ???)

Do you want:
1) To copy all the info that you have in the original in sheet forcast to sheet forcast of the new file ?? I doubt !!!

or

2) You want to copy ONLY the items that have values (meaning these are items that you input manually) as the rest are formulas ? Like you want to take Say:
Item 43533 in Aug 30 store count

and in file Updated you have for
Item 43533 for Aug we have a formula that shows 5

So the question what do you want ?
30 + 5 = 35 in K6
or
just 30 in cell K6
or ... ???

I need from you a clear example like this.
gowflow

My actual excel forecast template is pre-populated each month from our reporting tool. There are approximately 5 data tabs that contain different types of information that are needed for the forecast tab.  The real spreadsheet goes over to columns CZ, but the only parts of the spreadsheet that I can change is the Store Counts and the Unit Movement sections so that is what I included in the mocked up examples.  The Original_Forecast_Sheet represent January’s Forecast file and the Updated_Forecast_Sheet represents that February Forecast file.

Each month when the data is populated it doesn't know future store counts, so if we are in January (which we are) February Thru December will have the same store counts as January.  However, I may know that in Aug the retailer is planning on adding my items to an additional 25 Stores.  What I would do is hardcode 30 in the Original_Forecast_Sheet for  Item 43533 for Aug (5 current stores + the 25 new stores).  This way when I'm forecasting I can be as accurate as possible.  August represents the month when the retailer sets the aisle for the upcoming season so I could potentially have to hardcode new store counts for all my items.  The same goes with the Unit Movement, if my item is added to a bunch of new stores I will have to make an assumption whether it will have better unit movement, worse unit movement or stay the same.  If I think it will change I would hard code that value.

When February rolls around I generate the Updated_Forecast_Sheet that has updated data on the 5 data tabs.  I need to get any hardcoded values from the Store Count and Unit Movement section of the January - Forecast tab over to the February – Forecast tab so I do not have to manually type them again.  The February Forecast file is identical to January's but the tricky part is that I could have had new items populate into February and shift the row where the item was listed from the prior month so the solution not only has to find the hardcoded Store Count and Unit Movements but be smart enough to put them into the correct cell on the new months template.

Thank you for your help.

thank you for all this but please lets cut it to the nity gridy

What do I put in the updated for that store ?? I am still not clear !!! Shall I simply copy all the hardcoded data for a specific store and plug them as is in their corresponding month in the Updated file

OR

I should add them to existing

OR

...??

this is not clear to me.
gowflow

Copy only the hardcoded values for each item and overwrite the formula that is in the updated file for that item.

GOOD !!!! that's all I need !!!
Am going to pick up my son from foot and will get to this upon my return !!! :)

1) Only one last thing always both Original and Update have the same format ? like same columns and position

2) We are only concerned about Store and Unit Movement
and these are always in 1 sheet that is called Forecast ????

gowflow

Both Original and Update will have the same format and columns as well as position.  Also, the tabs on both files will be called Forecast.

Your question prompted me to think of something else.  Would it be possible not to force the two files to be named specific file names in order for it to work?  I may need to name my file January Business A Forecast and someone else may need to name there's January Business B Forecast.  If you had the Original file open and when you ran the code it would prompt you to browse for the Updated forecast file you want to use, that would seem like a favorable solution over forcing the two files to be named something specific.

I've been racking my brain on this for over a week so I really appreciate your help.

No problem don't worry I usually provide 'flexible' 'user set' solutions and that are 'long term' not quick fix !!!

Let me work on something and then you will give me your comments.
gowflow

One last issue is store number unique ? like may I run into a same code that is in 2 different rows ? My assumption would be a strict NO but who knows !!!
Let me know
gowflow

A strict no is correct.

Thank you

ok it is ready. however before posting I am only concerned with one comment you made which is:

quote
The real spreadsheet goes over to columns CZ, but the only parts of the spreadsheet that I can change is the Store Counts and the Unit Movement sections so that is what I included in the mocked up examples.
Unquote

Is it possible for you to post the exact Forecast sheet same as you have ? Post only this file it is enough.
gowflow

Is there any other specific questions I can answer without posting my exact work file, I don't want to violate the confidentiality terms I signed at my work?

The forecast data starts at row 40 of the forecast tabs.  Columns B thru G are item characteristic fields with the item number being in column D, the Store Counts for Jan thru Dec go from column H-S and Unit Movement go from Column U to AF.  Column AG to AR are override fields that I was hoping I could copy to the new spreadsheet if I can figure out how to extend your code.  Columns AS all the way to CZ are formulas that won't be overwritten, I may consider locking them down so they can't be changed.

WELLL this is exactly why I wanted your file !!!
I do not need the data as much as I need the field position !!! all this is

NOT AT ALL WHAT YOU POSTED !!! it is good I asked for it. do not post any valuable data. only the format and so I know where exactly they fit. I would not venture changing the code as it will be a total headache if your not super VBA inclined.

All this being said (DO NOT VIOALATE CONFIDENTIALITY FOR SURE NO ONE WANTS THIS)

So work on this Forcast worksheet so you can post it in format and not data then will take it from there.

pls confirm
gowflow

I read again your last post in details

YOU ARE JOCKING !!!!

Are you VBA knowlegable ??
gowflow

Here is the forecast file.  All of the fields in yellow are the fields that a user could change and fields that I wanted to try to get over to the new spreadsheet.  My original example mentioned store count and unit movement because I wanted to try to keep it simple because my spreadsheet is pretty detailed.  

I can't write VBA, I did find the VBA code to click a button and hide certain columns and I was able to make that work in this spreadsheet and I was proud of that if that tells you anything.  I was hoping that if your code applied to columns H thru S for example that I could figure out how to make it work for other columns in the spreadsheet but that is probably not realistic.  With my forecast file attached, it would be even better if it would work on the front end because I'm not a coder and I would struggle.

Thank you.
Forecast.xlsm

ok fine we are talking about a different issue and for next time please be specific when you post !!!

Yes some experts do work on getting you something off the ground roughly and then its up to you to find the rest.

I usually provide complete solutions.

All this being said I recommend following way of work.

I have designed for you an Independent solution that will achieve what you want. Now in the scoope of this question, I will modify the code so we stay in the spirit of this question which is to get the 'hardcoded' values that were formerly in Col B to Y in sheet Forecast from row 5 downward with items in A

to

Items in Col D starting from row 40 downward from Col H to S and U to AR

If when you try this solution it is acceptable for you then you may close this question and then post a related one with this file as attachment and ask for the next step which to fill all the other items.

I do not have a problem if you ask as many questions as you want I will keep tracking till I get you the whole project covered. But it is important to break them into lots.

Now all this being said I need to know (which is very important and cruitial here) all the items that are in pink are Data (like values not formulas and the rest is formulas ???) or they could also be formulas ????

After your answer I will adapt and revert.
gowflow

I appreciate all your help and the plan you laid out sounds great.

For Columns B thru AR, all columns will have formulas except for the override columns T and AG thru AR.  The override fields will be blank and the user can enter information in there that will be used for other calculations to the far right of the spreadsheet.

I do not understand you lost me here. How do I know what to copy? I sure will not copy a formula !!!
gowflow

For columns B they AR You would copy anything that is hardcoded, if a formula has been overwritten with a hardcoded value you would copy it over or if one if the override fields that starts out blank has a hardcoded value typed in you would copy it over.

I have changed the code to fit your requirement HOEVER I have not tested this as I do not have 2 worksheets that are similar with formulas and data so I make sure it function properly. So my recommendation is the following:

1) You make a copy of both your original and updated file and test this macro on copies not the real files so if something goes working you still have your data intact.

2) Download this file and make sure macros are enabled.

3) you will see 2 buttons green and blue (large one) they are self explicit and will not say more will see if it is user friendly and intuitive. Just click on these buttons one at a time and read the heading the green is for your last month file or the one you have worked on manually and the blue is the new file or this month file.

4) once both files are loaded and correct at this stage I recommend you simply close the workbook. It will tell you saving to registry and it will close all open files.

5) Run it again and it should open the files you had previously selected automatically you will see that the red button is accessible. Hit on the red button and you will receive a messagebox at the end telling you the results.

6) Anytime you can click on View Last Month File or View this month File button it will bring both files up so you can view the results before saving the 'This month File'

7) I disabled the automatic saving until all is ok and the macro is fine then we can have saving automatically.

Let me know
gowflow
UpdatePrevMonthForcast.xlsm

Awesome.   I'm in kid duty now and I will be able to fully test in about 45 minutes.

Wow, that worked brilliantly. I even moved an item to a different location in the updated spreadsheet and it knew where to put the hardcoded values.  This more than blew away my initial expectations for this solution.  Let me ask you this, since you are familiar with the code, if I posted a new question to add the same functionality to the information at the top of the spreadsheet prior to row 40, would you be willing to help?

for sure pls only put the forcast workbook that you posted here Forcast/xlsm and put a link to this question and specify what you want.

Let me know by putting a link in here. I am glad it worked for you as frankly I was not confident as we changed the references. I usually check thouroughly the solution before posting. Also pls provide a before and after file for the new question it will help make sure we don't waste too much time.
gowfflow