Retaining cell formatting when transferring data to another workbook


New Member
Hi everyone, first time poster here and beginner VBA user.

I'm having a hard time with a data cell transfer operation from one workbook to a new workbook. The problem is when I move the data over, I'm losing the leading 0s on my 5 digit ID codes, so instead of copying over 00034 I'm only getting 34. I've tried using the .text instead of .value, and I get a mismatch error.

Here's the code. The command which does the actual data transfer is highlighted in BOLD. I know the code is sloppy but I have no formal VBA training so most of my learning is by looking at other people's examples. Any help is appreciated.

Private Sub ExportSubmit_Click()

' Set sheets to copy from
Dim wsSupp As Worksheet
Dim wsPQE As Worksheet
Dim wsControls As Worksheet
Set wsSupp = Worksheets("Supplier_DL")
Set wsPQE = Worksheets("PQE_DL")
Set wsControls = Worksheets("Help & Controls")

' Open new workbook from template
Sheets("Help & Controls").Activate
Sheets("Help & Controls").Range("F3").Select
Dim strTemplate As String: strTemplate = "QA_Tracker_Template.xltm"
Dim templatePath As String: templatePath = ActiveCell.Value
Dim templateFile As String: templateFile = templatePath & strTemplate
Dim wb As Workbook
Set wb = Workbooks.Add(templateFile)

' Name and Save new workbook
Dim wbname As String
wbname = "QAL-" & Me.QALNumber.Value & " Distribution & Tracker.xlsm"
wb.SaveAs (wbname), 52


Dim NumRows As Integer, NumCols As Integer

With wsSupp
NumRows = Sheets("Supplier_DL").UsedRange.Rows.Count
NumCols = Sheets("Supplier_DL").UsedRange.Columns.Count
End With

Workbooks(wbname).Worksheets("Distribution List Check").Range("B2:G" & NumRows).Value = _
ThisWorkbook.Worksheets("Supplier_DL").Range("A2:F" & NumRows).Value

'Clear input controls.
Me.QALNumber.Value = ""
Me.QALTitle.Value = ""
Me.monthDue.Value = ""
Me.dayDue.Value = ""
Me.yearDue.Value = ""
Unload Me

End Sub


New Member
I should mentions this routine is using data from both a userform and pulling data from worksheets.


Well-known Member
Copy paste instead, e.g.:

ThisWorkbook.Worksheets("Supplier_DL").Range("A2:F" & NumRows).copy destination:= _
Workbooks(wbname).Worksheets("Distribution List Check").Range("B2:G" & NumRows)
Last edited:

Some videos you may like

This Week's Hot Topics