Excel VBA Code adapt paste location based on variable in reference cell

Hi, I previously posted this question in another thread. At the suggestion of the user attempting to answer the question I have reviewed the way the way I am asking the question (as there was some confusion) and started fresh here.

I have a workbook, in which the first sheet will be raw data, and then a sheet which will perform a series of calculations on data entered into it. There might be several dozen copies of this second sheet (varying from project to project), always performing the same calculations, but on different parts of the raw data. I need to copy in the sections of raw data to each sheet to calculate my various results. At the moment I have to search through my data, which could be hundreds or even thousands of rows long, highlight and copy the section I need to the relevant area of the relent sheet. Iam trying to write a VBA to speed this process up.

I have attached an example workbook. This is simplified in terms of the amount of data, the calculations which the successive sheets are performing and the exact locations of the elements but the principle is the same.

I would like to be able to enter two variables on a sheet. One to select the file number of the raw data, and one to set the location to paste. Hopefully the following images show this idea
Enter variable sheet2 (the active sheet in this example):
Variable.JPG

Source data to be copied based on variable in cell A1 sheet1 (the raw data)
Source.JPG

Pasted data in sheet2 (the active sheet in this example)
Outcome.JPG

To populate each location in a given calculation sheet I would re-enter the variable and re-run the macro (assigned to a keyboard shortcut). It does not have to copy to different locations or different sheets at the same time.

At the moment I have the following code:

Please Login or Register  to view this content.

This selects a range of values from the raw data page sheet1 (a 4x4 grid), based on the file number in column A being matched to the value of cell A1 in the activesheet (the first two columns are ignored using the offset), and copies it to a fixed grid of cells in whatever the active sheet is.

What I need is for the location in which the values are pasted to to be chosen by the variable entered in cell B1 of the active sheet matching to the corresponding location name in columnC of the active sheet (with the values then pasted starting in the adjacent cell). So if I enter 10 in A1, and B in B1 in the active sheet the code will search for file 10 in column A sheet1, skip the first two columns, copy a 4x4 grid of values, then paste these into the location b box, starting in the cell to the right of 'B'.

Does anyone know how to adapt the code above to achieve this?

Also, it would be useful to know if there is a more efficient way of picking the range to be copied instead of having to type out the individual offset.copy for each cell.

Please let me know if I can clarify anything further.

I think this code does what you ask based on the example file
Filenumber is in B1 (not A1 as you describe)
Location Code is C1

Please Login or Register  to view this content.

Thank you so much that is exactly what I'm looking for - it works perfectly! Apologies for the inaccuracies in the description regarding the cell references.

One small modification; would it be possible to adapt the .Find UCase part (for the location variable) to be able to find a letter and a number. For example B3, OR C5?

Thanks again.

Yes you're right that works fine. I have another issue. The file number in my raw data actually comes to me as a string of test and numbers, I have column A set up to extract the integer file number using =value(Mid etc).

This seems to be confusing the VBA code; it is almost working but the point at which the copy starts is out by 5 rows. So when I enter file 1 (which is in row 6) it is returning the value in row 10.

Is there a way around this, or of incorporating a further offset to mitigate this?

Thanks again for your assistance.

Hi, following up on my previous post I think I can clarify what the issue is.

My file number in its raw format looks like this: XYZ444_5556068_191213_0001.ABC

The number highlighted red is the file number, in this case 1; however it could be XYZ444_5556068_191213_0112.ABC etc. or anything up to four digits. So zeros are added before the integer to make the file number up to four digits (I have no control over this due to the source).

I am using the function =VALUE(MID(B6,24,4)) to pick out the file number. Although excel is not displaying the zeros as a result of this function, they must still be there somehow and this must be throwing the code off. When I change the function to =VALUE(MID(B6,27,1)) so it only picks the last value in the four digit file number, the code works fine and starts the copy in the correct place.

So, can the code either be adapted to allow for this, or can the =value function be changed so that over those four digits it only returns values greater than zero?

Thanks again.

Leading zero's are not the problem the VALUE function will handle those correctly
based on your example the problem is with inaccurate MID criteria (based on what you posted)
If I paste the filename in a cel and do only the mid part I get 001.
so test for yourself with 23 instead of 24 as start.

a more failsafe function would be
=VALUE(MID(D2;FIND(".",D2)-4,4))

this will find the period in the string and then step back 4 positions for the number this will tackle any issue where the filename varies in changes in length..

You were correct, my MID criteria was wrong, however I am still having the same issue. I have tried both functions you suggested. The copy is still starting 5 rows out. If I change the =Value to only pick the last digit of the file number, or just type the values in, the copy starts in the correct place.

I have adapted the code to fit my actual spreadsheet but all I've done is change the cell references and adjust the ranges, unless there's something I'm missing. The adapted code is below:

Please Login or Register  to view this content.

Unfortunately I can't post the actual spreadsheet but the images below show the layout.

Data (Sheet4)
Attachment 654574

Target worksheet (ActiveSheet)
Attachment 654575

Any ideas what else might be causing this, or if I've messed something up in the code when adapting it?

I'm going to mark this as solved now as you answered my original question, and I'll post the new one to a new thread. Thanks again.