Pulling specific data from a specific sheet, loading to a temp sheet, for forwarding.

Hi Fawkes_

Is the sub "Expedite" the code you are working with for this? Does what you have work so far?

Also... if you are inserting the new sheet through the code, then it did not exist before, for sure? If it is guaranteed not to exist then I'm not sure you need to first check for the columns on the temp sheet?
If the above is true, then we also don't really need to set any ranges for the temp sheet (because we know exactly which column number has what heading. I know the main sheet can have varying columns so we used ranges, but it makes little sense if you know for sure which column number has what heading)

Making progress, but there is no "Sales Order" heading in the main sheet... so the code does error out right now. Did you mean "SO" or will the main sheet be updated before you start using the code?
What is the path and filename on the shared drive? What is the name of the worksheet the data needs to be added to?

Try this for starters, the code checks for an existing temp and renames it if it exists to avoid errors. It adds the button and assigns it the second macro in the post below. It opens the network file, pastes, and saves, then closes it. Note that you have to update the filename as well as the worksheet name for the share.

Please Login or Register  to view this content.

In both macros you want to make sure if we use tempWS.Range("A" & Rows.Count).End(xlUp).Row that column A will be one that has data EVERY time, otherwise change the A or we need to adapt to check them all.

So this line

Please Login or Register  to view this content.

Changed to

Please Login or Register  to view this content.

and you still had an issue? it worked fine when I tried that...
My copy thinks it is a date column though?

Yes... ooops! I forgot a line... in the loop below, I needed to add the red line:

Please Login or Register  to view this content.

without that it just kept writing over the first row.

Hey Arkadi,

Im running into an error on this line, says the "subscript is out of range"? It was working fine on my test sheet, but when I try to use it on any other workbook I get this error.

Please Login or Register  to view this content.

It was working fine on the test WB but when I brought it over to a live book I ran the original macro fine, just hits this wall now.

Thank you again for all the time you have devoted to helping me with this

Yes you could definitely try activeworkbook. Even not specifying the workbook at all would put it into the active book since that is the default context.

As a small upgrade, when we do the .Find, any match could be set to the range, unless we tell it to match the whole content exaxtly. What I mean is that Find "PO" would for example be a match if the header it finds first is "PO Item" since it contains PO. It is better to specify that the whole cell has to match. Most of the headers we look for are pretty unique, but "SO" in particular has the potential for matches in other columns if the headings ever change.

So for example:

Please Login or Register  to view this content.

would change to:

Please Login or Register  to view this content.

Good morning Arkadi,

how are you doing today? I went through a changed SO to Sales order for the add and the searches to remove the chances of it grabbing the wrong column.

I was able to change the workbook issue to get it to work without issues.

I get an overflow on the sales order copy section if the "Sales Order" column on the first sheet is not formatted is there a way for me to format that during the add of the column?

What format should it have?

Ok, so after if soNum is nothing....... End If (in the original code) put this:

Please Login or Register  to view this content.

That will make it a number format, no decimals, and yes text will still be ok to enter

give .numberformat = "@" ? that makes it text

If the error happens on the test book, can you send me it again? Maybe I can reproduce the problem and try to solve it...

Truth is you probably didn't need to limit the input amount, "#" or "####" might even have done it just to indicate no decimals or commas. But I'm glad you found a solution, well done
Glad I was of some use to you in this project
Mmmmmm steak (drool)... lol. Maybe hit the rep button if you want to give something, that and as you have so kindly done, mark the thread as solved, are all the thanks required

Fawkes,
If I understand correctly then what you want is to check for a blank in the cell.... I'm not sure why it isn't working the way you have it unless the code below what you have pasted gives more clues...
As far as just a tip on tidying code, you could replace:

Please Login or Register  to view this content.

with:

Please Login or Register  to view this content.

But that is just form, and will not change the functionality.

On second thought, the column is set before the "for i" loop? It doesn't make sense to check if it is nothing every time... maybe :

Please Login or Register  to view this content.

I thought it would work ... idk there has to be something stupid im missing.


Here is the full code, but really its something in there I am missing.

Please Login or Register  to view this content.

ignore the nonEcompass part I have it commented out for now, I will tackle that mess soon enough. at the moment im just working to get it to pull the cell and use it to enter into a search that will save a value from inside the mainframe (not coded in yet) but at the moment the code runs through and just does nothing. as if I did something wrong with the loop (the mainframe comms part is incomplete due to me not being able to grab the variable to put in place) I have even put debug.print (custacct) and it wont should the value in the immediate window. so I feel like im missing something smh

Have you stepped through the code? Is the problem it runs even when len(custacct) should be 0?

Have you made sure it is finding the column? I guess so, since it would give you a message if it is nothing...
Have you checked what custAcctCol.Column is, and checked it is the right column number? also, have you checked what the value of WS.Cells(i, custAcctCol.Column).Value is when i=2 (or any other number)... I just wonder if custacct is getting the right value.

Additionally, why are there quotes in this? The variables are double, and that is numerical

Please Login or Register  to view this content.

Probably won't matter much since it will take the numerical value, and not the string, but it is technically a contradiction.

Valid point, It was just a habit I can remove them and it will work as well lol.

I was trying to check the value with the debud.print but I could get it to spit anything out.

Currently im working with the vlook and im getting false in everycell. Im pretty sure that is my fault because I used something pretty dumb to do it, I used the same method I would use from a record macro to link the file. so I think I need to use a much different method since im doing it as a loop now.

Please Login or Register  to view this content.

the column to left of the vlook is cell for the search so I think I need to use something similar to this

Please Login or Register  to view this content.

I decided to give this a shot, but It just displays the entire formula in the cell if I remove formatting, because it defaults to text, if I do numbers it does nothing, how would I properly format the column to take the formula?

Please Login or Register  to view this content.

Good morning Fawkes
Sorry I hadn't replied sooner, just came back on... but I'm glad you have sorted out the problem yourself, great job!