conditional new workbook creation

cwebster

New Member
Joined
Dec 15, 2005
Messages
3
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?
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Ceduljko

Board Regular
Joined
Dec 28, 2004
Messages
58
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

Well-known Member
Joined
Dec 15, 2005
Messages
818
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

New Member
Joined
Dec 15, 2005
Messages
3
Ceduljko said:
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

Well-known Member
Joined
Dec 15, 2005
Messages
818

ADVERTISEMENT

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

New Member
Joined
Dec 15, 2005
Messages
3
lozzablake said:
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

Well-known Member
Joined
Dec 15, 2005
Messages
818

ADVERTISEMENT

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

Board Regular
Joined
Dec 28, 2004
Messages
58
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

Well-known Member
Joined
Dec 15, 2005
Messages
818
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
 

Watch MrExcel Video

Forum statistics

Threads
1,118,081
Messages
5,570,089
Members
412,310
Latest member
mark884
Top