VBA Code, copy same data multiple times based on user input

[dixielou]

I am building a workbook to track training sessions. One worksheet will have fields to be completed for each training session. Some of the data would need to be copied into a log multiple times based on a couple of different factors: how many different documents were used in the training and how many attendees there were. I need to be able to report who is trained on which documents as well as pull up a list of all the training sessions an individual has attended.

For example: One training session used 2 documents and had 4 attendees - this would generate 8 lines in the log, two for each attendee and listing each document.

Is there a way to have VBA code state:
Insert a row for each attendee and paste their name in a specific column
copy a cell range and paste into each row (contains info that is specific to training session: date, trainer, etc)
copy a cell containing a document number and paste into each row

Repeat for each unique document number

My many attempts to create and insert an HTML screen shot failed so hopefully this makes sense to one of the smart cookies in this Forum.

Thank you,
DixieLou

 

[My Aswer Is This]

Glad to see you just joined the Forum.

When asking for help we always need specific details.

Like you said:

One worksheet will have fields to be completed for each training session

But you did not provide the name of the sheet and did not say what field.

When providing specific details you need to say something like this:

Sheet name "Master" has a field which is column B

See their we know the sheet name and that the field is Column B​

And if you go back and look at your question you will see a whole lot of other cases where you spelled out what you wanted but provided no specific details.

Do not say things like:

The other sheet and column Data

Look forward to seeing you write your question again here in this same post but with specific details.

 

[dixielou]

Every time I tried to type out more detail, I would end up getting kicked out of the website. So I tried writing a simpler version of my question to see if it was even possible.

Will try again:

Rows would need to be inserted into the worksheet

"Training Log" starting at row 13. ​

The number of rows would be determined by the number of documents listed in cells E2:E8 on the "Data Entry Screen" multiplied by the number of attendees entered in cells J2:J16 on the same worksheet.

On the worksheet "Data Entry Screen", fields B2:B9 would need to be copy/pasted into worksheet "Training Log" cells C13:J?? (ending row number determined by how many rows were added)
"Data Entry Screen" cells J2:J?? (ending row determined by how many attendees were entered) would be copied into "Training Log" starting at K13 - one copy of each name for each document listed
"Data Entry Screen" cells E2:G?? (ending row determined by how many documents were entered) would be copied into "Training Log" L13:N?? for each attendee listed

Hopefully this is the level of detail you're asking for.​

 

[My Aswer Is This]

This may be more complicated then I can deal with.

I like it more when users say here is what I'm doing and here are the results I want to see after the script is run.

Your explanation had a lot of details but it seemed more like you were telling me how the script should run.

You subject title says:

VBA Code, copy same data multiple times based on user input.

That normally means when I see it that you want a Input box to pop up you enter 20 and we copy a row of data 20 times and paste it someplace else.
​

 

[Peter_SSs]

Every time I tried to type out more detail, I would end up getting kicked out of the website.

When you are Logging In, click the 'Remember Me?' checkbox and you won't get timed out.

Try this in a copy of your workbook.

Sub TrainingLog()
  Dim wsEntry As Worksheet, wsLog As Worksheet
  Dim rAttendees As Range, rDocs As Range, rCell As Range
  Dim NumAttendees As Long, NewRows As Long, oSet As Long
  
  Set wsEntry = Sheets("Data Entry Screen")
  Set wsLog = Sheets("Training Log")
  With wsEntry
    Set rAttendees = .Range("J2", .Range("J" & .Rows.Count).End(xlUp))
    Set rDocs = .Range("E2", .Range("E" & .Rows.Count).End(xlUp))
    NumAttendees = rAttendees.Rows.Count
    NewRows = NumAttendees * rDocs.Rows.Count
    wsLog.Rows(13).Resize(NewRows).Insert
    .Range("B2:B9").Copy
  End With
  With wsLog
    .Range("C13").Resize(NewRows).PasteSpecial Transpose:=True
    rAttendees.Copy Destination:=.Range("K13").Resize(NewRows)
    For Each rCell In rDocs
      rCell.Copy Destination:=.Range("L13").Offset(oSet).Resize(NumAttendees)
      oSet = oSet + NumAttendees
    Next rCell
  End With
End Sub

 

[dixielou]

Brilliant! If a couple tweeks can be made it will be absolutely perfect:
1) When inserting the rows into the Training Log - can they be formatted to be the same as the one below the insert point.
2) I missed a detail in my description - when copying the document line/s can it be set copy cells E2:G2 plus any additional lines used up to E8? The cells would still be pasted into the Training Log starting in column L.

Thank you

 

[Peter_SSs]

If a couple tweeks can be made it will be absolutely perfect:

Let's try for that.

Sub TrainingLog_v2()
  Dim wsEntry As Worksheet, wsLog As Worksheet
  Dim rAttendees As Range, rDocs As Range, rCell As Range
  Dim NumAttendees As Long, NewRows As Long, oSet As Long
  
  Set wsEntry = Sheets("Data Entry Screen")
  Set wsLog = Sheets("Training Log")
  With wsEntry
    Set rAttendees = .Range("J2", .Range("J" & .Rows.Count).End(xlUp))
    Set rDocs = .Range("E2", .Range("E" & .Rows.Count).End(xlUp)).Resize(, 3)
    NumAttendees = rAttendees.Rows.Count
    NewRows = NumAttendees * rDocs.Rows.Count
    wsLog.Rows(13).Resize(NewRows).Insert
    .Range("B2:B9").Copy
  End With
  With wsLog
    .Range("C13").Resize(NewRows).PasteSpecial Transpose:=True
    rAttendees.Copy Destination:=.Range("K13").Resize(NewRows)
    For Each rCell In rDocs.Rows
      rCell.Copy Destination:=.Range("L13").Offset(oSet).Resize(NumAttendees)
      oSet = oSet + NumAttendees
    Next rCell
    .Rows(13 + NewRows).Copy
    .Rows(13).Resize(NewRows).PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
  End With
End Sub

 

[dixielou]

Wonderful! Works like a dream!
Thank you so much

 

[Peter_SSs]

Wonderful! Works like a dream!
Thank you so much

You are welcome. Your descriptions in posts 3 & 6 were detailed and nice and clear which helped a lot.

BTW, did you try my suggestion in your thread in the 'About This Board' forum about trying to get the HTML Maker working?

 

[dixielou]

I have not been able to get the HTML Maker working. It would have made this whole thread much easier to explain!