Linking data from 1 sheet to several sheets in another book?

Re: Linking data from 1 sheet to several sheets in another b

Sandy - there are 2 approaches that come to mind...

1. Use the INDIRECT function in the workbook that contains the multiple sheets using the sheet name as the variable in a formula to extract only the data that matches the sheet name

eg in A1 of sheet 2002-08 - enter 2002-08

then B1

=IF(INDIRECT("'[CONSOLIDATED.XlS]"&A1&"'!A2")=A1,INDIRECT("'[CONSOLIDATED.XlS]"&A1&"'!A2"),"")

basically tests cell A2 in your consolidated file to see if it equals 2002-08 if it does it returns A2 from the consolidated file...obviously you would need to amend this but the main jist is there for you to work out.

The other approach is to use VB to go through the data in your consolidated data and copy and paste to the appropriate sheet in your other workbook (say 1.xls) - if the sheet doesn't already exist then create it etc... this works off the assumption that the cells containing the 2002-08 info etc are not date formatted and that the sheet names in the other file match these names

Here's some (not too elegant) code that goes through the cells in Column A of your consolidated range to determine the copy range and then pastes the resulting range into your other workbook - before moving on to the next date type thing in Column A - ie from 2002-08 to 2002-09 and so on until there's nothing left in Column A

Again you will have to tinker with this or someone else may well provide a better code...

Sub SEPARATE_DATA()

Dim n As Integer
Dim inc As Integer

Workbooks.Open ("C:\Luke Work\Mr_Excel\1.xls")
Windows("CONSOLIDATED_TEST.xls").Activate

n = 1
inc = 0

Do Until Cells(n, 1) = ""
'Determine Sheet Name
SNAME = Cells(n, 1)
Do Until Cells(n, 1) <> Cells(n + inc + 1, 1)
inc = inc + 1
Loop
Range(Cells(n, 1), Cells(n + inc, 1)).Copy
inc = 0

Windows("1.xls").Activate
On Error GoTo ErrorHandler
100
Sheets(SNAME).Select
Cells(2, 1).PasteSpecial
Windows("CONSOLIDATED_TEST.xls").Activate

If inc = 0 Then
n = n + 1
Else
n = n + inc
End If

Loop

Windows("1.xls").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close

Exit Sub

ErrorHandler:

Sheets.Add
ActiveSheet.Name = SNAME
Resume Next

End Sub

Re: Linking data from 1 sheet to several sheets in another b

Thank you very much for your reply to my question.

I am not quite sure how to impliment your suggestions. I am unable to locate the Indirect function in my Step by Step Excel book and I know nothing about VB (Not even where to put the code you so generously provided)

What I have is a worksheet that looks something like this:

What I need to do is export the 2003-01 line items to an excel book that has a sheet for each month. Also, I don't want all the columns to go... I only need conlumns A, B, F, G, I, & J-N. (In that order) Each Effective month would go to it's corresponding sheet in the new workbook.

Will the indirect function do this for me?

Thanks for all your help,
Sandy

Re: Linking data from 1 sheet to several sheets in another b

Sandy if I tried to explain the whole thing via Mr E we could be here for some time to-ing and fro-ing...

Easier if you send me by email

a) A sample file as per your example with say up to 3 months data (I don't know what it is so it means little to me in case you're worried about security!)
b) send me the file you want to use that houses all of the month sheets in it.

I will then lop some vba in and it will be working.

Hooray.