Consulting

Results 1 to 4 of 4

Thread: macro to fill a form and save one file per row in document

  1. #1

    macro to fill a form and save one file per row in document

    Hi Guys..
    forgive me if the question is not clear straight away.. happy to explain deeper or answer questions...

    I have a report that is being sent daily which with gaps in orders etc...
    we have standard form that needs to be filled with details from that report and then sent forward to another department for upload.

    report has always 20-60 lines (each line is one order) and we have all the details in columns..
    I would like to be able to fill the form for first line using all the details, and then save the file in designated folder.
    and then move to line 2 line 3 etc... so at the end I would like to have 20-60 files saved which I could just send forward.
    is this something that could be done using VBA?
    got to say my VBA knowledge is basic so please be gentle

  2. #2
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    In what file format are you:
    • receiving the reports?
    • sending the form?
    Actually, beyond knowing the file formats, before anyone could help we'd need to know how the data in both are structured. Can you attach bot a report and a form to a post with some representative data (delete anything sensitive)? You do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  3. #3
    Hi and thanks for quick response...
    to answer your questionin red)
    Quote Originally Posted by macropod View Post
    In what file format are you:
    • receiving the reports? .xlsx
    • sending the form? . xlsb
    Actually, beyond knowing the file formats, before anyone could help we'd need to know how the data in both are structured. Can you attach bot a report and a form to a post with some representative data (delete anything sensitive)? You do this via the paperclip symbol on the 'Go Advanced' tab at the bottom of this screen.
    and a trimmed sample..
    of what it looks like... (original report and template have many more fields)
    the new file should only save filled template with info tabs without "report" tab (if possible)
    Attached Files Attached Files

  4. #4
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Try the following macro:
    Sub Demo()
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Dim xlSrc As Worksheet, xlTgt As Worksheet
    Dim c As Long, lCol As Long, r As Long, lRow As Long
    'Check that the "Report" sheet exists
    For Each xlSrc In ActiveWorkbook.Worksheets
      If xlSrc.Name = "Report" Then Exit For
    Next
    If xlSrc.Name <> "Report" Then Exit Sub
    'Establish how many rows & columns are in use
    With xlSrc.UsedRange.Cells.SpecialCells(xlCellTypeLastCell)
      lRow = .Row: lCol = .Column
    End With
    'Create & populate a new sheet for each data row on the "Report" sheet
    For r = 2 To lRow
      Set xlTgt = Sheets.Add(Before:=Sheets("Template"))
      Sheets("Template").Columns.Copy Destination:=xlTgt.Range("A1")
      xlTgt.Name = xlSrc.Range("A" & r)
      For c = 1 To lCol
        xlTgt.Cells(10 + c, 3) = xlSrc.Cells(r, c)
      Next
    Next
    'Delete the "Report" sheet
    'xlSrc.Delete
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
    End Sub
    At present, it leaves the 'Report' sheet intact. When you're happy the code is working as expected, uncomment the xlSrc.Delete line.

    PS: It's not apparent where the 'xlsb' fits in, unless your intention is to save the file in that format.
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

Posting Permissions

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