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

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.

Hi and thanks for quick response...
to answer your question:(in red)

Quote:

---

Originally Posted by macropod

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)

Try the following macro:

Code:

---

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.