Paste values as text and add headings to new sheets

[drewship]

Hello all. I have a couple of small problems I hope someone can help me with.
I have attached a test workbook. The ExtractSN macro searches column B for serial numbers and copies each one to its own row while copying all associated data to the individual rows to the TEMP sheet.

I need this macro to copy the 3 header rows from each source sheet to new destination sheets, then copy the numerical values as text to the respective sheet to preserve leading zeros and any letters. If the new sheets could be named the same as the source sheets and add _RPT to the end of the name, that would make things easier.

Any help would be appreciated.

Thanks,
Andrew

[drewship]

I have done a lot of trial and error but have not been able to get the following piece of code to copy the leading zeros even when I previously set the column to 'TEXT' in the code.

Please Login or Register  to view this content.

What this does is copy one set of numbers from the source field and pastes it to a destination field. For example, the source field contains

00256, 04598, 22597, 2U/56

The main code parses through each set of numbers and in this example would paste the values to four separate rows in column B.

256
4598
22597
2U/56

I need to show the leading zeros as follows

00256
04598
22597
2U/56

Can someone help with this?

Thanks,
Andrew

[drewship]

I have attached an updated spreadsheet which partially does what I need.

I still need help with pasting the exact text which may include letters and leading zeros. The current paste code does not paste leading zeros and only pastes the numbers before the first letter in the number group. Everything after the letter is lost.

for example:

00256, 04598, 22597, 2U/56

currently pastes:

256
4598
22597
2

I need the following to be pasted:

00256
04598
22597
2U56

Additionally, I need:

Please Login or Register  to view this content.

to ensure that each source row is copied but have been unsuccessful in changing the LastRow portion to something that starts at the first row of data and duplicates the entire row for each separate serial number. You can see in the rpt sheets that each serial number is separated correctly on its own row, but only the source data from the last row is duplicated in the rest of the columns.

Any help would be greatly appreciated!!!

Thanks,
Andrew

[drewship]

Can anyone help with this?

[StephenR]

Andrew - please could you clarify what you are pasting - from where and to where? Does your worksheet illustrate your desired results?

EDIT: for the formatting, what about if you do this?

Please Login or Register  to view this content.

[drewship]

StephenR, using your code, I get "PasteSpecial method of Range class failed"

I changed the code back and have added a 'EAST apples rpt DESIRED' sheet to show what that specific sheet should look like after the macro is run. The other sheets should follow suit.

Basically, I want to copy the contents of each blue tab to its respective green tab but with each serial number and associated data on its own row.

Thanks,
Andrew

[StephenR]

Andrew, I have to confess I'm stuck. I'm sure there must be a simple solution, but I'm unfamiliar with number formatting and I haven't managed to solve this yet!

[drewship]

I don't believe I have seen any previous posts in the forum where you have been stumped, so this must be more difficult than I thought.

If you step through the code, hold your curser over the

Please Login or Register  to view this content.

portion in the line

Please Login or Register  to view this content.

and you will see the correct value I need pasted to the green tab, but as soon as it is actually pasted, it loses leading zeros and everything after a letter, even if i previously set the column to "Text".

Andrew

[StephenR]

Andrew - try this:

Please Login or Register  to view this content.

I don't believe I have seen any previous posts in the forum where you have been stumped, so this must be more difficult than I thought.

Ha, thanks, but there are plenty which stump me but I cunningly conceal my ignorance by not posting in them.

[drewship]

StephenR, Thanks!! That change worked to paste the actual value as needed. I thought Val was supposed to paste the actual value but I guess I misunderstood what it would do.

Do you have any recommendations to copy the associated row data for each serial number instead of the data from the last row?

Thanks,
Andrew

[StephenR]

Andrew - I think just replacing the LastRow reference in this line does the trick:

Please Login or Register  to view this content.

[drewship]

Thanks!! That seems to have done the trick. I am going to run some tests on a copy of the live workbook to see if anything else pops up as an issue.

Andrew

[drewship]

Everything seems to be working...thanks again!!!

Andrew