conditional new workbook creation

[cwebster]

I have a very large worksheet for which I need to create separate workbooks when the value of a particular cell changes. Each workbook would contain the range of cells prior to the cell break. I would like for the workbooks to be named the same as the trigger cell contents (5-digit number). I have searched but am not able to find any code that would perform this function for me. Can you help?

 

[Ceduljko]

Is your worksheet one big data file and you have to split it into several Excel documents?

Can you explain the problem once again?

 

[lozzablake]

you can use the SheetChange Event to trap the change cell event (look it up in Help).

you can then create a new workbook and copy the required cells in and save it with the name=to the contents of the cell you are checking.

 

[cwebster]

Is your worksheet one big data file and you have to split it into several Excel documents?

Can you explain the problem once again?

Yes, it is one very large data file that I need to split into separate workbooks based on a particular cell changing. What code exactly would do this?

 

[lozzablake]

can you clarify. Does the trigger cell refer to the row number where you want to stop the export?

If so when you type the second number will that be exporting the previous number+1 up to the current trigger number?

Is the trigger cell on a different sheet?

 

[cwebster]

can you clarify. Does the trigger cell refer to the row number where you want to stop the export?

If so when you type the second number will that be exporting the previous number+1 up to the current trigger number?

Is the trigger cell on a different sheet?

The trigger cell is when the cell being watched changes. At that point, the changed row (cell-1) up to the previous change point needs to be copied to another workbook. No numbers will be typed -- this is a 50k line data file.

 

[lozzablake]

This is the type of thing you could do. It's not clear whether the triggers are constant or how many there are, or how they get updated (user input?), but you could have a series of if statements or maybe for each statement based on named ranges. trhis needs to be in the module associated with the sheet where the data is stored.

Private Sub Worksheet_Change(ByVal Target As range)

Const MyDir As String = "U:\"
Const NumColumns As Integer = 10

Dim rngTrigger1 As range
Dim rngTrigger2 As range

Set rngTrigger1 = range("triggercell1")
Set rngTrigger2 = range("triggercell2")

If Target = rngTrigger1 Then
Workbooks.Add
ThisWorkbook.Sheets(1).range(Cells(1, 1), Cells(rngTrigger1.Row - 1, NumColumns)).Copy Destination:=ActiveWorkbook.Sheets(1).range("a1")

ActiveWorkbook.Close savechanges:=True, Filename:=MyDir & Target.Value

End If


End Sub

 

[Ceduljko]

All, we are not talking here about dynamically changed cell. There's a huge list where, let's asume, in column "A" a primary key exists. At each change of primary key (maybe each 100 rows, or so), the previous block has to be written to another file.

This problem is almost repeated ScatteredXY charting question:

http://www.mrexcel.com/board2/viewtopic.php?t=184942

If you tweak the code given there, you only need to replace charting part with file save (or, opening a new blank workbook, copy/pasting the range discovered and saving the workbook).

HTH

 

[lozzablake]

OK, tha's quite simple. We simply need to loop through the prime kt checking to see if it has changed and storing the end of the copied range each time. Something like this:

Sub CreateWorkBooks()

Dim intNumRows As Integer
Dim shtData As Worksheet
Dim i As Integer
Dim intStartRow As Integer

Const MyDir As String = "U:\"
Const NumColumns As Integer = 10


Set shtData = ThisWorkbook.Sheets(1)

intStartRow = 1

With shtData
intNumRows = .range("a1").CurrentRegion.Rows.Count

'loop through each row checking to see if row ahead has same prime key
For i = 1 To intNumRows

If .Cells(i + 1, 1) <> .Cells(i, 1) Then
.range(Cells(intStartRow, 1), Cells(i, NumColumns)).Copy
Workbooks.Add
ActiveSheet.Paste
ActiveWorkbook.Close savechanges:=True, Filename:=MyDir & .Cells(i, 1).Value
intStartRow = i + 1
End If
Next i
End With
End Sub