Hi,

In my workbook, I must have as many sheets as there are days in a
month. In each sheet I have about 20 tables filled with different
types of data: text, dates, currency, etc. The entire workbook then
contains a month of daily data.

Every time I add a row/column, I split other tables on the same sheet.
The guy before me liked absolute references, so you can imagine it's a
nightmare to deal with.

Can anyone think of a way to optimize this ordeal? Ideally, I want
each table on a separate sheet, but then I'd have to save the whole
workbook as one day, and create a new workbook every day - ****er.
Thanks,



Mia

Option Explicit

Sub SplitAndBuild()

Dim wb As Workbook ' for output
Dim wsOut As Worksheet ' for output
Dim ws As Worksheet 'for daily loop
Dim source As Range
Dim index As Long ' loop counter for tables
' on each sheet set A1:Ann as the ranges of the tables
' eg A1 B5:D20
' eg A2 Table1

Dim tablename As String

' loop for each day -- ie each worksheet
For Each ws In ThisWorkbook.Worksheets

index = 1

'loop through each table
' creating a new worksheet for each
Do While ws.Cells(index, "A") < ""
tablename = ws.Cells(index, "A").Value
If tablename = "" Then Exit Do

' now copy over the ranges

source = ws.Range(tablename) ' a name would be better

'get a new worksheet
Set wsOut = GetSheet
wsOut.Name = tablename
wsOut.Range("B2").Value = tablename

With wsOut.Range("B4").Resize(source.Rows.Count,
source.Columns.Count)
.Value = source.Value
.Interior.ColorIndex = 34
End With

Loop

' ranges all copied, now save the workbook
' use the source worksheet's name?
wb.SaveAs "Month_" & ws.Name

wb.Close False ' its already saved, so just close it

Next ' worksheet - ie do the next day


Next




End Sub
Private Function GetSheet(wb As Workbook) As Worksheet
' create a workbook for the day
If wb Is Nothing Then
Set wb = Workbooks.Add(xlWBATWorksheet) ' with 1 sheet
Set GetSheet = wb.ActiveSheet ' and point at it
Else
With wb
Set GetSheet =
..Worksheets.Add(after:=.Worksheets(.Worksheets.Co unt))
End With
End If

End Function


Notes:
1) on each sheet, put a table starting at A1 with that sheets ranges that
need copying
2) whete the line for saving the output workbook is MONTH, chaneg this to
whatewver you like eg "APRIL_"
3) I assume that you'll process every worksheet. If there are more,
thenyou'll need to have something that can be checked - such as the worksheet
name - before processing, eg lets say each sheet's name is "Day 1" then "Day
2" etc, we could use

IF ws.Name LIKE "Day*" THEN '' and do getsheet & copy bit


HTH
Patrick Molloy
Microsoft Excel MVP



"Mia" wrote:

Hi,

In my workbook, I must have as many sheets as there are days in a
month. In each sheet I have about 20 tables filled with different
types of data: text, dates, currency, etc. The entire workbook then
contains a month of daily data.

Every time I add a row/column, I split other tables on the same sheet.
The guy before me liked absolute references, so you can imagine it's a
nightmare to deal with.

Can anyone think of a way to optimize this ordeal? Ideally, I want
each table on a separate sheet, but then I'd have to save the whole
workbook as one day, and create a new workbook every day - ****er.
Thanks,



Mia