Insert entire sheet into another or template

[jackson1990]

Hello everyone,

First let me explain what I am doing because there may be a better way of doing it. I am creating an excel for a set of users. They will enter in a SKU, and based off that sku I want it to pull due dates for that project, as well as populate a template for the category that sku is based in. By template I want a list of things they must do in order to be complete with their task. There is about 18 categories these skus fall under. So ideally, the user inputs the sku, I've already used Vlookup to pull the data of the category and due dates, but have no idea how to get a certain template to populate in the fields below based off all of that.

So essentially it works like this: User enters sku data>Via vlookup it gathers the data of category and due dates for that sku>populates one of 18 "templates" that the user then fills out.

Any ideas on this?

Thanks everyone!

 

[cerfani]

Well probably a few ways to do it... you can populate the sheet with functions that reference the SKU and query whatever you need based on the sku value, you can handle errors by returning blank values or whatever... you can get creative on excel with functions alone or you can do it through vba code. And have a script that rus and creates a template. VBA code is more versatile and if you know the language much easier to work with over excel functions.

VBA code requires a trigger like a button though or possibly an event.

 

[jackson1990]

I wouldn't say I'm familiar with VBA.

I was thinking I could make 18 sheets each with their unique "template" or formatting (then hide them so the user couldn't see them) I want with the check list I want them to fill out, but I don't know how I would pull that information from the other sheets onto the "home/user" sheet after the sku is open? Is there such a function that pulls over an entire sheet and populates from that cell onward?

 

[cerfani]

If the 18 sheets all have a unique structure (different fields in different positions) then yeah you could have them hidden and pre formatted. No reason for a script to create the template each time, the code should just populate the fields.

Regarding what VBA can do, you can copy and paste or cut and paste, delete sheets, create new sheets, move sheets, etc... there isnt a function to just pull data, which has no technical meaning in regards to excel. if you mean you can copy/paste, cut/paste, copy entire sheets and then edit the copy, etc... but yeah whatever you mean by pull, excel can do it... you could do it with excel functions with query type functions (lookups, etc). If you are new to VBA writing a simple script is a slow process, you will constantly have to google how to do something :/

 

[jackson1990]

Thanks again for you replies by the way. I'm putting up some images just to see if it helps with what I am doing. It's the most basic version of it but it should help with the idea.

https://ibb.co/jFSgxx
https://ibb.co/fCc3AH
https://ibb.co/nvA1xx

Basically, its like I want it to function that IF category 4 is showing then paste the coordinating sheet into this user sheet underneath the sku and category. I'm starting to think this is only achievable through VBA?

Thanks again for your responses.

 

[cerfani]

Yeah ok, so sheets 2 through whatever are already populated, nothing to do but grab the values from the appropriate sheet depending on the category cell. Yeah You could do it with Excel, it wouldnt be impossible but it really isn't the best way. VBA would be best.

I would use a worksheet_change event. https://msdn.microsoft.com/en-us/vba/excel-vba/articles/worksheet-change-event-excel

This code is inserted onto the sheet code page in the vba editor (find your project in project pane and double click sheet1 and it will bring up a blank white notepad, paste this there)... you can use the intersect method to check which cell is being edited (i think it is easy enough to just check string though msdn examples will show with Intersect)

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
    If Target.Address = "$D$1" Then 'category updated so clear the area and pull data
        Dim category As String
        category = Target.Value
        If category = "1" Then
            [COLOR=#008000]'populate from sheet2[/COLOR]
        Else
            If category = "2" Then
                [COLOR=#008000]'populate from sheet3[/COLOR]
            Else
                If category = "3" Then
                    [COLOR=#008000]'populate from sheet4[/COLOR]
                Else
                    [COLOR=#008000]'populate from sheet5[/COLOR]
                End If
            End If
        End If
     End If
End Sub

so that is like the skeleton of the code, you can see how it would flow you can interact with checkboxes as well (googled for you but didnt read ... http://p2p.wrox.com/vb-how/45800-how-access-checkbox-excel-sheet-vbulletin.html)

 

[jackson1990]

PM
Cerfani Thanks this is exactly what I was looking for! And links to resources as well. You're a saint.

 

[cerfani]

No prob, I might look good on stain glass lol but yeah good luck!

 

[jackson1990]

Unfortunately, I guess I overestimated my VBA skills...as usual.

I'm struggling on how to get this to do the actual copy based off the cell contents of $D$1. They way I'm understand the code you wrote is that should 1 be present in $D$1 then it will populate Sheet2 into Sheet1. Sorry I haven't really dabbled in VBA or coding for that matter, so I'm not sure where to head with this. I tried looking around at resources to avail. If you could help out a bit more than you already have I'd be eternally grateful.

Thanks

 

[jackson1990]

Cerfani any help on this?