Copying multiple rows of data and create a different spreadsheet per row

[AndyJM87]

I will try and explain as best as I can..

Basically, the company I work for needs a way to copy the data from a worksheet and paste it into a job card...

At the moment, I have only managed to be able to write the VB so it creates one job card at a time (see below)

Sub CreateJobCard()
'Generates job card
    wsJob.Range("C2").Value = wsData.Cells(frmMain.lstViewInc.ListIndex + 5, 3)
    wsJob.Range("K2").Value = wsData.Cells(frmMain.lstViewInc.ListIndex + 5, 4)
    wsJob.Range("A6").Value = wsData.Cells(frmMain.lstViewInc.ListIndex + 5, 2)
    wsJob.Range("B6").Value = wsData.Cells(frmMain.lstViewInc.ListIndex + 5, 10)
    wsJob.Range("B10").Value = wsData.Cells(frmMain.lstViewInc.ListIndex + 5, 11)
    
    wsJob.Copy
    
        With Application.FileDialog(msoFileDialogSaveAs)
            If .show = -1 Then
                .Execute
            Else
                Exit Sub:
            End If
        End With
    
    ActiveWorkbook.Close
End Sub

[/URL][/IMG]


Using the below userform, the listbox pulls the rows of data from a worksheet.

Is there some sort of Loop where Excel will loop through all rows, copy the relevant data into the job card, and then add a new sheet into a workbook with each individual job card copied.

Hope that makes sense.

Any help appreciated.

 

[DanteAmor]

Hi @AndyJM87, I have somo questions.


Do you want to go through all the lines of the sheet?
What is the userform for?
Or do you want to go through the selected lines in the userform, then look for that record in the sheet and copy the data?


If the base is the userform then explain in detail what you have in the userform. It is a listbox or a listview, how you loaded the data in the form, loaded all the necessary data to put that data on the sheet "job card", if all the data is in the form, it is no longer necessary to go to the sheet. Etc.