Retaining cell formatting when transferring data to another workbook

sleuth

New Member
Joined
Jan 12, 2018
Messages
27
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

ThisWorkbook.Activate

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
 

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

sleuth

New Member
Joined
Jan 12, 2018
Messages
27
I should mentions this routine is using data from both a userform and pulling data from worksheets.
 

sheetspread

Well-known Member
Joined
Sep 19, 2005
Messages
5,117
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:

sleuth

New Member
Joined
Jan 12, 2018
Messages
27
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)
That did it! Thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,102,363
Messages
5,486,406
Members
407,547
Latest member
Sankarasrinivas

This Week's Hot Topics

Top