Auto create info sheets from master spreadsheet [Solved]

wbarn17 2 Posts Thursday April 12, 2018Registration date April 16, 2018 Last seen - Apr 13, 2018 at 06:38 AM - Latest reply: ac3mark 9176 Posts Monday June 3, 2013Registration dateModeratorStatus April 20, 2018 Last seen
- Apr 16, 2018 at 04:59 PM
UPDATE: I am able to automatically generate the excel sheets and then automatically convert those sheets into separate files using a macro. My remaining issue is how to populate the desired cells with their corresponding data from the master sheet. For example: Sheet 2 fills in with all of the data from row 2 of Sheet 1, Sheet 3 fills in with all of the data from row 3 of Sheet 1, Sheet 4 fills in with all of the data from row 4 of Sheet 1, etc... Once again... thank you for any help!




I have tried to find a solution to this problem for awhile, but I still am not making any progress. I have a master spreadsheet that contains data (about 25 columns) for 200+ part numbers (each part number has its own row). I would like to find a way to automatically make individual excel files for each part number using a template and fill the corresponding data into specified cells. Would something like this even be possible? I am trying to avoid having to make 200+ excel files and manually filling in the 25 data points into each file.

If this is not possible, would it be easier to do it all in one file? For example have the master on sheet 1, then the 200+ on sheet 2, sheet 3, sheet 4, .... etc. Then I could convert the sheets into separate files.

Here is a dropbox link to the master file file and another for the individual template file. I removed all the data for company privacy purposes.

https://www.dropbox.com/s/sz9jwp2f908pqyq/ccm%20ex.xlsx?dl=0

https://www.dropbox.com/s/rxsyhhec87j8j94/ccm%20temp.xls?dl=0

Any help would be appreciated!

Thanks
See more 

3 replies

Reply to this topic
ac3mark 9176 Posts Monday June 3, 2013Registration dateModeratorStatus April 20, 2018 Last seen - Updated by ac3mark on 13/04/18 at 05:04 PM
+2
Helpful
2
OK, when you say "ALL of the data", why cant you just cut line 2, create sheet2, paste line 2. Is there some fancy formatting you wish to have when "filling in" in the sheet?

Try this, use the below code as an example, and make do what you are asking:

Function CountRows(WhatSheet)
Dim cellcount
cellcount = 0
ThisWorkbook.Worksheets(WhatSheet).Select
cellcount = Cells(ThisWorkbook.Worksheets(WhatSheet).Rows.Count, 1).End(xlUp).Row
CountRows = cellcount

End Function

Sub PasteToSheet(WhatSheet)
Dim tmpRow
tmpRow = CountRows(WhatSheet)

ThisWorkbook.Worksheets(WhatSheet).Select
Cells(tmpRow, 1).EntireRow.Select
Selection.Insert
Application.CutCopyMode = False
End Sub

Function SheetToSheet(FromWhatSheet, FromWhatRow, ToWhatSheet)
Dim tmpCellValue
tmpCellValue = ""

ThisWorkbook.Worksheets(FromWhatSheet).Select
ActiveSheet.Range("A" & FromWhatRow).EntireRow.Select
Selection.Copy
PasteToSheet (ToWhatSheet)

End Function

Function CheckIfIsSheet(SheetName) As Boolean
CheckIfIsSheet = False
For Each ws In Worksheets
If SheetName = ws.Name Then
CheckIfIsSheet = True
Exit Function
End If
Next ws
End Function


Private Sub CreateTheSheet(NamedWhat)
Dim Works As Worksheet
With ThisWorkbook
Set Works = .Sheets.Add(After:=.Sheets(.Sheets.Count))
Works.Name = NamedWhat
End With
End Sub


Sub CreateBook(WhatSheet)
Dim Mainsheet
Dim RowCount
Dim CurrentRow
Dim StartingRow
Dim WhatRow
Dim CellValue
Dim CellToCheck
Dim sheetTAB As Worksheet
StartingRow = 2 'Change this value to whatever row your master sheet starts on
Mainsheet = WhatSheet
ThisWorkbook.Worksheets(Mainsheet).Select
RowCount = CountRows(Mainsheet)
' MsgBox (RowCount) 'unREM for Feedback
'Now need to check if there is any info on the master sheet
If CurrentRow > RowCount Then
MsgBox ("No Entries on Worksheet " & Mainsheet & ". Now closing!")
'Upon finding no info, it closes
Exit Sub
End If
'Now we need ot loop through each line and see if we have a worksheet
For CurrentRow = StartingRow To RowCount
CellToCheck = "B" & CurrentRow
CellValue = ThisWorkbook.Worksheets(Mainsheet).Range(CellToCheck).Value
MsgBox (CellValue) 'unREM for feedback
'need to see if the tab exist, if not create it
doesSheetExist = CheckIfIsSheet(CellValue)
MsgBox (doesSheetExist)
If doesSheetExist = False Then
CreateTheSheet (CellValue)
End If
nret = SheetToSheet(Mainsheet, CurrentRow, CellValue)
ThisWorkbook.Worksheets(Mainsheet).Select
Next
End Sub




wbarn17 2 Posts Thursday April 12, 2018Registration date April 16, 2018 Last seen - Apr 16, 2018 at 12:24 PM
Thank you so much. With this information I was able to solve my problem. I appreciate the time you took to do this for me.
ac3mark 9176 Posts Monday June 3, 2013Registration dateModeratorStatus April 20, 2018 Last seen - Apr 16, 2018 at 04:59 PM
You are welcome. I was in a good mood that day!
Respond to ac3mark