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:
This code selects all cells on a worksheet. ALL CELLS![]()
Cells.Select
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.
Bookmarks