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

Can a formula spear through sheets?
Use =SUM(January:December!E7) to sum E7 on all of the sheets from January through December

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,119
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,122,521
Messages
5,596,653
Members
414,083
Latest member
Mrsash

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top