VB attached to button to pull templates based on values in specific cells

[devildogdad76]

Attached is the workbook I am trying to improve and I have made a macro attached to the “Build Report” button that does an example of what I am wanting to accomplish. Basically what I want to happen is when the “Build Report” button is clicked I want it to check what the 1st “Install Type” is, then pull a template over from the matching worksheet to the “overnight report” worksheet, then move to the 2nd “Install Type” and pull that template over to the “overnight report” worksheet and so on. What I have currently set up is just an example of what I would like the end result to be, but unfortunately it is very crude and if any of the info gets changed in the “Install Type” column, it will not change the outcome of the button. Right now, I'm wanting to make sure that I am on the right track in thinking that I will need to use VBA to accomplish this. I have never used VBA before so this will definitely be a learning experience for me. If I am on the right track, then I will start researching this. Thank you in advance.

[Mumps1]

I have a few questions for clarification:

-Do you simply want to copy the sheets based on the names in the "Install Type" column to the "Overnight Report" sheet one underneath the other?
-One of the install types is "Win 10" but the corresponding sheet name is "Win 10 (ORACLE)" which means that this sheet name is based on the "Install Type" column andthe "Installer" column. Does the dependency on two columns happen only for "Win 10"?
-Can you please explain in more detail what you mean by:

if any of the info gets changed in the “Install Type” column, it will not change the outcome of the button

[devildogdad76]

I have a few questions for clarification:

-Do you simply want to copy the sheets based on the names in the "Install Type" column to the "Overnight Report" sheet one underneath the other?
YES

-One of the install types is "Win 10" but the corresponding sheet name is "Win 10 (ORACLE)" which means that this sheet name is based on the "Install Type" column andthe "Installer" column. Does the dependency on two columns happen only for "Win 10"?

The (ORACLE) part will be removed eventually, it was just there primarily for assisting with the learning curve.


-Can you please explain in more detail what you mean by:

I was just pointing out that the "build report" button on the attached sample spreadsheet is linked to a recorded macro.

[devildogdad76]

I've attached an updated copy of my workbook with a few new buttons to help better simulate what I am trying to accomplish.

[Mumps1]

Start by populating the Overnight Summary sheet data from columns E to L then assign the macro below to the Build Report button:

Please Login or Register  to view this content.

[devildogdad76]

When using this I get a run-time error '429': ActiveX component can't create object.Attachment 657847

[devildogdad76]

I've updated the code and change the CreateObject to GetObject, Now I am getting "Run-time error'13' : type mismatch.

Sub CopyData()
Application.ScreenUpdating = False
Dim Rng As Range, RngList As Object, srcWS As Worksheet, desWS As Worksheet, item As Variant
Set srcWS = ThisWorkbook.Sheets("Overnight Summary")
Set desWS = ThisWorkbook.Sheets("Overnight Report")
Set RngList = GetObject("Scripting.Dictionary")
For Each Rng In srcWS.Range("H11", srcWS.Range("H" & srcWS.Rows.Count).End(xlUp))
If Not RngList.Exists(Rng.Value) Then
RngList.Add Rng.Value, Nothing
End If
Next
For Each item In RngList
Sheets(item).UsedRange.Offset(1).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1)
Next item
Application.ScreenUpdating = True
End Sub

[Mumps1]

It works properly for me. Try the attached file. Click on the "Build Report" button.

[devildogdad76]

It must have been something with how I copied it over. I pulled the data over from the workbook you sent and it works there as well. This is a great start, Thank you very much.

There are some tweaks that I need to try and make, but I will open a new post for that if I can't figure it out. Which is likely, but I still want to try and figure it out myself first.

Thank you again.

[Mumps1]

You are very welcome.