Master tab data list parsed out to applicable cells in applicable worksheets

kiwimtnbkr · #1 10-27-2017, 02:51 AM

Master tab column A is a list of 50 serial numbers (rows 5-55), column B is the device type the serial number belongs to (rows 5-55) and column C is the user of the device (rows 5-55).
Each of the serial numbers is hyperlinked from the Master tab to its own tab which is named with its applicable serial number.

All of the serial number tabs have the same title row consisting of cells A1:C1 being merged with the word 'Type:' in the cell A1 , cells D1:F1 being merged with the words 'Ser no:' in the cell D1 and cells 'G1:H1 being merged with the word 'User:' in the cell G1.
What is the macro I need to copy the Type/Ser no/User into their relevant cells on their applicable tabs based on the info contained in columns A/B/C and rows 5-55 on the Master tab.

Thanks
Mike

NoSparks · #2 10-27-2017, 07:29 AM

How about

Code:

Sub Create_Name_WorkSheets()
Dim i As Integer
Dim ws As Worksheet
Dim sh As Worksheet
Set ws = Sheets("Template")
Set sh = Sheets("CFD Basic Info")
Application.ScreenUpdating = 0

    For i = 5 To Range("A" & Rows.Count).End(xlUp).Row
        Sheets("Template").Copy After:=sh

        With ActiveSheet
            .Name = sh.Range("A" & i).Value
            .Range("A2").Value = sh.Range("B" & i).Value
            .Range("D2").Value = sh.Range("A" & i).Value
            .Range("G2").Value = sh.Range("C" & i).Value
        End With

    Next i
    
Create_Hyperlinks
End Sub

PS: as a new user of these forums have a read of this to understand why there is an appropriate method of posting the same question to more than one forum.
https://www.excelguru.ca/content.php?184

kiwimtnbkr · #3 10-27-2017, 12:06 PM

your p.s. noted - I am suitably admonished and have marked my crossposting as solved.

and that code pretty much does exactly what I need it to do so MASSIVE thank you!

question - is there a way that the info can be copied into merged cells A1, D1 and G1 without overwriting the 'Type:', 'Ser no:' and 'User:' text?

NoSparks · #4 10-27-2017, 01:07 PM

Quote:

question - is there a way that the info can be copied into merged cells A1, D1 and G1 without overwriting the 'Type:', 'Ser no:' and 'User:' text?

I assume you mean like this

Code:

Sub Create_Name_WorkSheets()
Dim i As Integer
Dim ws As Worksheet
Dim sh As Worksheet
Set ws = Sheets("Template")
Set sh = Sheets("CFD Basic Info")
Application.ScreenUpdating = 0

    For i = 5 To Range("A" & Rows.Count).End(xlUp).Row
        Sheets("Template").Copy After:=sh

        With ActiveSheet
            .Name = sh.Range("A" & i).Value
            .Range("A1").Value = .Range("A1").Value & "  " & sh.Range("B" & i).Value
            .Range("D1").Value = .Range("D1").Value & "  " & sh.Range("A" & i).Value
            .Range("G1").Value = .Range("G1").Value & "  " & sh.Range("C" & i).Value
        End With

    Next i
    
Create_Hyperlinks
End Sub

kiwimtnbkr · #5 10-27-2017, 01:40 PM

that is EXACTLY what I was after!!

Can't thank you enough for your assistance! Absolute legend

kiwimtnbkr · #6 10-27-2017, 04:47 PM

couple more queries

Should I need to insert a new row for a new device with its Ser no., Type and User, how would you re-run the macro to just create the new worksheet, it's hyperlink and have it inserted in the correct place?
Is that even possible?

And if the User of a particular device changes, how would you re-run the macro to update just the affected worksheet?

I realise I am asking a lot but I just can't find the right topics to answer my queries.

cheers
Mike

NoSparks · #7 10-28-2017, 12:24 AM

See if this works for you

kiwimtnbkr · #8 10-28-2017, 12:59 AM

you've absolutely nailed the first part - I would never have come up with that code in a million years!

The only thing that I couldn't see working was if the User of a particular device changes is updated on the Master sheet. Is it possible to rerun the macro on demand to get it to update the changed user(s) on their applicable worksheet(s)?

NoSparks · #9 10-28-2017, 06:47 AM

Quote:

The only thing that I couldn't see working was if the User of a particular device changes is updated on the Master sheet. Is it possible to rerun the macro on demand to get it to update the changed user(s) on their applicable worksheet(s)?

Are you sure about that ?

Right click the "CFD Basic Info" tab and select View Code.
Insert the word STOP as the first line in the Worksheet_Change sub.
Go back to the "CFD Basic Info" sheet and click a hyperlink to view that sheet.
Note the User number.
Go back to the "CFD Basic Info" sheet and change the User for that hyperlink.
The code will stop and display itself with the STOP line highlighted.
Use the F8 key to step through the code one line at a time and see what happens.
When the sub finishes, check that hyperlinked sheet again for the User.

Before removing the STOP instruction, try inserting a line for another Ser No.
(do this near the top of the Ser No. list so you're not looping too often while doing things manually)
and use F8 again to see what happens and where the code goes.

Hope this helps to explain and show what's going on.

kiwimtnbkr · #10 10-28-2017, 01:07 PM

that will teach me to try and work on spreadsheets after a long day!

Your code works absolutely brilliantly!

The error was all mine when I copied the code over to the actual working spreadsheet and completely missed the code you has written for the CFD Basic Info master sheet.

Many many thanks for your assistance, I just wish there was a way for me to be able to buy you a cold beverage or three in repayment for your time.... you don't happen to live in New Zealand?

kiwimtnbkr · #11 10-28-2017, 01:17 PM

consider this request as now SOLVED.

NoSparks · #12 10-28-2017, 01:38 PM

I'm on Vancouver Island, west coast of Canada, so I'll have to pass on the beverage.

Glad I could be of assistance.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
Column B Cell 25 of all worksheets copies data found in the lower cells ...	rpcalo	Excel	3	03-10-2016 03:38 AM
Move Data from Master List to Sheets with City Name	meggenm	Excel	3	01-28-2016 10:18 AM
Creating Word document that automatically inputs text into applicable fields,	jjfromnj	Word	3	12-04-2015 10:00 AM
seprate process data from main master list	Santhosh AMASL	Excel	1	01-12-2015 09:33 AM
Linking Workbooks/Entire Worksheets to Identical in Master	taxacct	Excel	2	10-01-2014 11:22 AM