String and Value workbook reference

This probably makes perfect sense to you, as you see it and read it.

However, it seems a bit murky (to me at least) absent looking at it on my screen.

Can you take another stab at explaining it, or upload a sample workbook please?

Sorry for the unclear request, it's probably because I am not a programmer and over several months have created a very convoluted and inefficient macro, by recording sequences of key strokes, pasting them together and adding code from kind helpers on this site. The equations are all hard coded with numerical values and names that only work for the current dataset, e.g. subtract 2.5 from each cell of column "ABC". When I get another data set I go in and edit the macro's hard coded values and strings. Although there are only 5 or 6 values to change they are in 5 different macros in 15 or 20 different places and I inevitably miss one. Instead of a hardcoded value in the macro equation I want a pointer that will go to another sheet in the workbook and read the values to use. The proper approach would be to write the macros with variables. My poorboy approach is to retrieve a string or value from a specific place in another sheet of the workbook. In effect the macro says "In Sheet 2, Subtract the value that is in cell A1 of Sheet1, form every cell in the column of Sheet2 that has the name as given in cell B1 of Sheet1"

Would it be easier to explain to me how to substitute a variable for the hardcoded strings and numbers, and then read a file that updates the variables????

arguably, the easiest way to do it is with a fixed reference.

For example

Please Login or Register  to view this content.

The D1 reference is fixed in position so you can put this formula in B1 and fill down and column B will be filled with the results.
In the D1 reference, the column D is fixed static by use of the $ sign in fron of the D and the row is likewise fixed in position by the $ sign in fron of the 1
Technically, for the above example, the only $ sign technically needed is the one in front of the number, but I added both for demonstration purposes.
Every time you change the value in D1, the calculated result would change in column B.

Here is te fixed file

I'm at work at the moment ( I work as an Office Integration Developer). I'll take a closer look at this when I get home at about 1:30 PM Central time.

in the mean time, I believe some, if not all of your questions, are or can be answered by looking at the VBA code in the sample workbook I uploaded last night.
Take a look at that and see what you think of it and I'll take another look at this thread when I get home.

Thanks Nori,

That's exectly waht I needed!

Thanks Bilbo,

I didn't look at you sample workbook in detail earlier, from other threads I thought I was not explaining my problem correctly. I like the use of the button update, it will be very helpful in other macros I use.

ok, I've had some time to re-examine your posts and code and have a few first comments and questions.

first of all, in your code posted today, there is a line:

Please Login or Register  to view this content.

This code selects all cells on a worksheet. ALL CELLS
For the XLS format, this means 16,777,216 cells
For the XLSX, XLSB, and XLSM format, this means 17,179,869,184 cells
Some quick math will tell you that the newer format supports 1,024 times MORE CELLS.

Others may disagree, but I can share with you first hand, this can be VERY DANGEROUS to successful completion of your code.
Naturally, this does depend on how much actual content lives in the sheet, it's format(s), its functions and calculation state and many other factors.
I can personally attest that I have seen hundreds of developers use this step in XLS format and then attempt to run the code in the newer applications ans watch Excel crash at worst, or error out at best.

If you, using this small example as an example, get the mistaken impression you can use it in any scenario, you may find out the hard way you can't.
Please don't misunderstand, I fully expect, respect, and accept, you're using it because it may well be that is what someone showed you, or it was contained in some other code you copied.
None the less, in a future post, I'll provide an example of how you can gather the necessary information to avoid using it.

Moving on:
The For/Next Loop is ok, but it does nothing to delete any unnecessary columns that may have the misfortune to be placed to the right of the last listed column in the For/Next Loop.
Here too, I'll work into an example workbook the ability to correct this situation.

The code seems to make the assumption that the far left column will always receive the "Cut Off" heading (hard coded) but the code also seems to imply the data pasted from LAS Load may actually have a different heading.
Are these correct assumptions?

Repetitive Programmatic Copy/Pasting can cause problems with the Office Clipboard.
There is no way to programmatically empty the Office Clipboard, and it only empties itself when the last current instance of an Office application is closed.
While I certainly understand the use of copy/paste when performing steps manually, things can get carried away when code is used to do the same thing.
Frequently, I've seen people record a macro to make repetitive tasks easier, but when that repetitive task is copy/paste, I've also seen Excel crash and even adversely impact Windows and the kernel.
Granted, that can be an extreme result, but it does happen.
As an alternative, one can use some database functionality to create a "connection" to data and then append the data to another location in the workbook without ever using copy/paste.
As extra benefits:
You can have the option to select only those columns you really want from the original records,
Rename any of the columns as you specify
Avoid additional steps to delete unneeded columns
Can re-specify new or different columns on subsequent runs of data.
If desired, you can also filter data based on criteria you can specify.
All of these parameters can be externally set by you very simply by placing values in cells in a parameters sheet.
while this may seem like fairly advanced programming, it really is not that difficult.
If that opportunity seems appealing to you, I could also post a sample of that.

oh, one other question I forgot.

You mention the desire to change columns you want to keep.
Is this because the columns actually change from run to run, or because sometimes you may decide different columns are needed even though original columns may still be included in the new data?

I appreciate your comments, and I am very aware of how inefficient and how sensitive my code is. In addition to hard coded names and values that often need to be changed, by recording macros the code often points to specific column or cell locations rather than to a name or type of data. If anything is in the wrong place failure is certain.

Your assumptions are correct, the Cutoff channel is the result of an IF statement, operating on other columns, that populates the column with 0/1 values. Then another macro deletes all rows with a 1. Like I said, if the cuttof channel is not in col 1, the Cutoof macro won't find it.

Selecting the columns is a simple matter and a very small part of the operation. I'm sure you'd have a good laugh at how ugly the total code is, but it works. I can use it because I built it and know what breaks it, after I complete the functionality I need to make it useable for others.