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

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

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,592
Messages
5,487,745
Members
407,609
Latest member
Chrissie1970

This Week's Hot Topics

  • Timer in VBA - Stop, Start, Pause and Reset
    [CODE=vba][/CODE] Option Explicit Dim CmdStop As Boolean Dim Paused As Boolean Dim Start Dim TimerValue As Date Dim pausedTime As Date Sub...
  • how to updates multiple rows in muliselect listbox
    Hello everyone. I need help with below code. code is only chaning 1st row in mulitiselect list box. i know issue with code...
  • Delete Row from Table
    I am trying to delete a row from a table using VBA using a named range to find what I need to delete. My Range is finding the right cell. In the...
  • Assigning to a variable
    I have a for each block where I want to assign the value in column 5 of the found row to the variable Serv. [CODE=vba] For Each ws In...
  • Way to verify information
    Hi All, I don't know what to call this formula, and therefore can't search. I have a spreadsheet with information I want to reference...
  • Active Cell Address – Inactive Sheet
    How to use VBA to get the cell address of the active cell in an inactive worksheet and then place that cell address in a location on the current...
Top