Exported excel sheet data validation

[Nikhil2803]

Hi Team, I am working on a request where an excel sheet has few columns with columns format like(SSN , ZIP code and YYYYMMDD format) data. Now on the same sheet there is a button. If I click on that button, then all the sheet data will be exported to a new excel sheet and that will be saved into user's desktop. The problem I am facing is, I need to get the same value that is provided in the original sheet. Like if SSN is 000999000, it should be same in the exported excel, but now it is deleting the preceding zeroes(same for ZIP also).
Is there any way, where I can add the same data validations in the newly created excel sheet also. Than you for the help.

 

[Nikhil2803]

You could also adjust your current code to also paste the data validation.

        ' Paste it in say Cell A1. Change as applicable
        wsO.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        wsO.Range("A1").PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, SkipBlanks:=False, Transpose:=False '<-- NEW

This will work unless the validation rule(s) point to a list contained in a range of cells not available in the copied workbook. This goes back to what I said earlier about needing to understand your validation rules.

So, what I am doing is. In my excel sheet, in a worksheet, there are some 15-20 columns. Out of these, there are 4-5 columns that are having Format cells condition. Like, there is a column which can take only SSN format, there is a column which will take only ZIP format, there is a column that is having YYYYMMDD format. suppose I give 07891 in ZIP column. Now in the same sheet, there is a button that will export all the data to a new sheet(create a new excel and save it to desktop). In the new sheet the preceding zero is getting removed. It is making ZIP as 7891. I want the value 07891 in the new sheet as well.

 

[rlv01]

So, what I am doing is. In my excel sheet, in a worksheet, there are some 15-20 columns. Out of these, there are 4-5 columns that are having Format cells condition. Like, there is a column which can take only SSN format, there is a column which will take only ZIP format, there is a column that is having YYYYMMDD format. suppose I give 07891 in ZIP column. Now in the same sheet, there is a button that will export all the data to a new sheet(create a new excel and save it to desktop). In the new sheet the preceding zero is getting removed. It is making ZIP as 7891. I want the value 07891 in the new sheet as well.

Then that is probably a cell format issue not a data validation issue. Right now you are pasting only values (xlPasteValues).
Option 1 is to paste everything:

wsO.Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Option 2 is to paste values, then paste formats:

        ' Paste it in say Cell A1. Change as applicable
        wsO.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        wsO.Range("A1").PasteSpecial Paste:=xlPasteFormats , Operation:=xlNone, SkipBlanks:=False, Transpose:=False

 

[Nikhil2803]

You could also adjust your current code to also paste the data validation.

        ' Paste it in say Cell A1. Change as applicable
        wsO.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        wsO.Range("A1").PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, SkipBlanks:=False, Transpose:=False '<-- NEW

This will work unless the validation rule(s) point to a list contained in a range of cells not available in the copied workbook. This goes back to what I said earlier about needing to understand your validation rules.

You could also adjust your current code to also paste the data validation.

        ' Paste it in say Cell A1. Change as applicable
        wsO.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        wsO.Range("A1").PasteSpecial Paste:=xlPasteValidation, Operation:=xlNone, SkipBlanks:=False, Transpose:=False '<-- NEW

This will work unless the validation rule(s) point to a list contained in a range of cells not available in the copied workbook. This goes back to what I said earlier about needing to understand your validation rules.

Then that is probably a cell format issue not a data validation issue. Right now you are pasting only values (xlPasteValues).
Option 1 is to paste everything:

wsO.Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Option 2 is to paste values, then paste formats:

        ' Paste it in say Cell A1. Change as applicable
        wsO.Range("A1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        wsO.Range("A1").PasteSpecial Paste:=xlPasteFormats , Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Both the steps are working perfectly. Thank you so much for your valuable inputs. I just need one more help. Is there any we can also copy the same column breadth in the new sheet?

 

[Nikhil2803]

Both the steps are working perfectly. Thank you so much for your valuable inputs. I just need one more help. Is there any we can also copy the same column breadth in the new sheet?

I got it. Thank you.

 

[Joe4]

Please do not mark your own posting acknowledging that some other posts by someone else originally contains the solution.
Please mark the original actual post by the person who came up with it.

Thanks