Creating a List specified column as a value - not as formula

[paula.mccall]

Good Evening,

This is a multi-part request for assistance (please forgive, but I am very much a novice to vba, but I am always trying to learn and expand).

I have a worksheet that I am wanting to create a list, into a separate worksheet, from a specified column (column A). However, column A of the original worksheet is based from a formula.
Not only do I need the list to generate, disregarding the formula, I also need it to ensure no duplicates.

Question 1: how can I create this list to paste as a value, and not a formula?
(below is the current code I am using, which is not working correctly):

Please Login or Register  to view this content.

I attached an example file and inserted the current code (hope I did it correctly).


I greatly appreciate your help!

Kindest Regards,

Paula

[jolivanes]

Code will add a Sheet named "SplitCode" if it does not exist.

Please Login or Register  to view this content.

Try on a copy of your original first.

[paula.mccall]

Thank you so much for your help jolivanes - this worked perfect!

So, now for the second part of this question: Instead of creating a new "SplitCode" sheet, is there a way to use this code to create the list in an already existing worksheet in the same workbook?
And, can that same worksheet already have other occupied columns?

Example: In my workbook, I have a worksheet called "Export." In the "Export" worksheet I have data already written in columns F - H. I would like to generate this list from the "CompanyX" worksheet to the "Export" worksheet - specifically in column A, without messing up the data that is already in columns F-H.
Again, ensuring no duplicates are generated.


Thank you so much for time and help!

Kindest Regards,
Paula

[jolivanes]

Maybe like this. Ensure that the references are proper.
It will start adding data from the first empty cell on in Column A in sheet "Export"

Please Login or Register  to view this content.

Again, try on a copy of your workbook first Paula because changes made through code are not reversible.

[paula.mccall]

Hello Jolivanes,

Again, thank you so much for your help and time. I tried the code that you sent for the last post but nothing happened when the macro was run. I have attached another file, with the code included.
So, to try to do a better job of explaining what I need, in full description (instead of in pieces or phases):
The 2nd thing I would like to understand and see (using the attached example) is:

I would like to copy columns A, D, F & H from the "AMS" ws, to columns A, B, C, & D of the "Billing" ws.

-important points to note:
- the list in the "AMS" ws will update daily,
- so its important that when the data is pasted to the "billing" ws, it overwrites any already existing data based on the information matching in column A, and if the well name, from column A, does not already exist, it populates at the last entry

Example: In the "AMS" ws (column A), Well AAA has 50 (column D), 80 (column F), & 130 (column H)
tomorrow, in the "AMS" ws, Well AAA (column A) updates/changes to 60 (column D), 80 (column F), & 140 (column H)

So, in the "billing" ws, i need the the updated numbers to match the Well name from column A

AND: any new data (like adding Well XXX in column A) would automatically populate as the last entry of the pasted list.


Gosh, I hope that all made sense. Please let me know if I need to provide a better example, and again, thank you so much!

Kindest Regards,
Paula

[jolivanes]

You're trying to go from the wrong sheet. The "sh1" and "sh2" are mixed up.
Try so.

Please Login or Register  to view this content.

[jolivanes]

Re: I would like to copy columns A, D, F & H from the "AMS" ws, to columns A, B, C, & D of the "Billing" ws.
Sheet "AMS" Column D Header = "Produced" while Column B in Sheet "Billing" has a Header of "Date"
Sheet "AMS" Column F Header = "Fresh" while Column C in Sheet "Billing" has a Header of "# Incidents"
Sheet "AMS" Column H Header = "Total" while Column D in Sheet "Billing" has a Header of "Produced"
Is that kosher?

[paula.mccall]

1. First, I updated the code you provided, and it works great - thank you! So I can better understand, what is the "c.Value" for?


2. Sorry about that - I meant to change the headers to match on both sheets. The headers that should be in columns A-D of the Billing should match the order of the headers I am wanting from the AMS worksheet. I have reattached the worksheet with the headers updated.


Kind Regards,
Paula

[jolivanes]

Try this (see attached)
You can update(run the code) from either sheet.

Please Login or Register  to view this content.

[paula.mccall]

good morning Jolivanes,

The code works great!!! Thank you so much for all your help - you are awesome!

Kindest Regards,
Paula