Retaining cell formatting when transferring data to another workbook
Results 1 to 4 of 4

Thread: Retaining cell formatting when transferring data to another workbook
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Jan 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Retaining cell formatting when transferring data to another workbook

    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

  2. #2
    New Member
    Join Date
    Jan 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Retaining cell formatting when transferring data to another workbook

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

  3. #3
    Board Regular
    Join Date
    Sep 2005
    Posts
    5,097
    Post Thanks / Like
    Mentioned
    4 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Retaining cell formatting when transferring data to another workbook

    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 by sheetspread; Jan 12th, 2018 at 06:57 PM. Reason: had the references backwards

  4. #4
    New Member
    Join Date
    Jan 2018
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Retaining cell formatting when transferring data to another workbook

    Quote Originally Posted by sheetspread View Post
    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •