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.

 

[Joe4]

How exactly is the sheet being exported? It does not sound like it is exporting in Excel format.
Can you post the code behind the button?

 

[rlv01]

Whether or not that is possible depends mainly on two things:
1. How you are exporting your sheet data. For advice on that you will have to post your code.
2. Your data validation rules.

Update: I see @Joe4 has beat me to it

 

[Nikhil2803]

Sure, please find the attached code while clicking on export button.

Function testExport()
Dim wbI As Workbook, wbO As Workbook
Dim wsI As Worksheet, wsO As Worksheet

Set gr_name = Worksheets("Template1").Range("dens_group")

strUserName = fOSUserName

' Source/Input Workbook
Set wbI = ThisWorkbook
' Set the relevant sheet from where you want to copy
Set wsI = wbI.Sheets("Template1")

' Destination/Output Workbook
Set wbO = Workbooks.Add

With wbO
' Set the relevant sheet to where you want to paste
Set wsO = wbO.Sheets("Sheet1")

' Save the file
.SaveAs Filename:="C:\Users\" & strUserName & "\Desktop\" & gr_name & "_Dent.xlsx", FileFormat:=xlOpenXMLWorkbook

' Copy the range
wsI.Range("dent_export").Copy

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

' Save file with pasted data
.Save

' Close newly created file
.Close

End With

MsgBox ("Your data has been saved to your desktop.")

End Function

 

[Nikhil2803]

How exactly is the sheet being exported? It does not sound like it is exporting in Excel format.
Can you post the code behind the button?

Function testExport()
Dim wbI As Workbook, wbO As Workbook
Dim wsI As Worksheet, wsO As Worksheet

Set gr_name = Worksheets("Template1").Range("dens_group")

strUserName = fOSUserName

' Source/Input Workbook
Set wbI = ThisWorkbook
' Set the relevant sheet from where you want to copy
Set wsI = wbI.Sheets("Template1")

' Destination/Output Workbook
Set wbO = Workbooks.Add

With wbO
' Set the relevant sheet to where you want to paste
Set wsO = wbO.Sheets("Sheet1")

' Save the file
.SaveAs Filename:="C:\Users\" & strUserName & "\Desktop\" & gr_name & "_Dent.xlsx", FileFormat:=xlOpenXMLWorkbook

' Copy the range
wsI.Range("dent_export").Copy

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

' Save file with pasted data
.Save

' Close newly created file
.Close

End With

MsgBox ("Your data has been saved to your desktop.")

End Function

 

[Nikhil2803]

Whether or not that is possible depends mainly on two things:
1. How you are exporting your sheet data. For advice on that you will have to post your code.
2. Your data validation rules.

Update: I see @Joe4 has beat me to it

Function testExport()
Dim wbI As Workbook, wbO As Workbook
Dim wsI As Worksheet, wsO As Worksheet

Set gr_name = Worksheets("Template1").Range("dens_group")

strUserName = fOSUserName

' Source/Input Workbook
Set wbI = ThisWorkbook
' Set the relevant sheet from where you want to copy
Set wsI = wbI.Sheets("Template1")

' Destination/Output Workbook
Set wbO = Workbooks.Add

With wbO
' Set the relevant sheet to where you want to paste
Set wsO = wbO.Sheets("Sheet1")

' Save the file
.SaveAs Filename:="C:\Users\" & strUserName & "\Desktop\" & gr_name & "_Dent.xlsx", FileFormat:=xlOpenXMLWorkbook

' Copy the range
wsI.Range("dent_export").Copy

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

' Save file with pasted data
.Save

' Close newly created file
.Close

End With

MsgBox ("Your data has been saved to your desktop.")

End Function

 

[Joe4]

Instead of trying to do a copy/paste, why not just do a "SaveAs" on the original file?
Then it should retain all the formatting from the original.

If you do not want/need all the sheets from the original file, just delete the ones you don't need before doing the "SaveAs".
As long as you don't re-save the original file with the original location and file name while doing this, you won't affect/change the original file.

 

[Nikhil2803]

Is it possible for you to provide the code snippet. I just need to get the original data in the exported sheet without loosing the original format. It should take data as it is.

 

[Joe4]

Is it possible for you to provide the code snippet. I just need to get the original data in the exported sheet without loosing the original format. It should take data as it is.

You should be able to get the code to do that just by turning on your Macro Recorder, and record yourself performing those steps manually.
The Macro Recorder is a great little tool to get code snippets like that!

 

[rlv01]

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.