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
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