Auto create info sheets from master spreadsheet [Solved]

Posts
2
Registration date
Thursday April 12, 2018
Last seen
April 16, 2018
-
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 

Your reply

1 reply

Best answer
Posts
10930
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
December 11, 2018
2
Thank you
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




Say "Thank you" 2

Glad we were able to help! Love us? Write us a review! Rate CCM

CCM has helped 2713 users this month

wbarn17
Posts
2
Registration date
Thursday April 12, 2018
Last seen
April 16, 2018
-
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
Posts
10930
Registration date
Monday June 3, 2013
Status
Moderator
Last seen
December 11, 2018
-
You are welcome. I was in a good mood that day!
Respond to ac3mark