Custom View of worksheet in different workbook

F

FreeDevil

Hi All,

I have a question about excel 2000.

Situation:
Now, i have 3 workbooks, say Q1_Sales, Q2_Sales, Q3_Sales

and in each workbook, there are 3 worksheets, say Product_A,
Product_B, Product_C.

So, my question is: is there any way (apart from copy worksheet one by
one) that i can view all Product_B sheets in one workbook ?
ie, one workbook (it's like a summary workbook) contains all product_b
worksheets only.

any excel function that can do that? or it can only be done by macro
??

any idea will be appreciate.

Thanks for your time.
 
S

Stefan Hägglund [MSFT]

Hi!

You can link to the data in a summary workbook, using formulas like:
='C:\[Book1.xls]Sheet2'!A1
='C:\[Book1.xls]Sheet2'!A2
etc.

Best regards

Stefan Hägglund
Microsoft
 
F

FreeDevil

Stefan Hägglund said:
Hi!

You can link to the data in a summary workbook, using formulas like:
='C:\[Book1.xls]Sheet2'!A1
='C:\[Book1.xls]Sheet2'!A2
etc.

Best regards

Stefan Hägglund
Microsoft
Click to expand...

Thanks.
so you mean i just link the cell one by one ?
but is there any way that the summary workbook can store a link of the
entire worksheet instead of only a cell ?
because if i only link the cell one by one, i need to copy and paste
the format every time if i change the format of the source workshee.

Thanks for your time.
 
D

Dave Peterson

If you right click on a worksheet tab, you'll see an option for "Move Or Copy".

You can choose "(new book)" in the top box (and check create a copy near the
bottom).

This'll create a new single sheet workbook (that sheet only).

Here's what I got when I recorded a macro when I did one sheet (with a slight
edit, too).

Option Explicit
Sub Macro1()
Sheets("Sheet1").Copy
End Sub

So to do all the worksheets:

Option Explicit
Sub testme01()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
wks.Copy
With ActiveSheet
Application.DisplayAlerts = False
.Parent.SaveAs Filename:=ActiveWorkbook.Path & "\" & .Name
Application.DisplayAlerts = True
.Parent.Close savechanges:=False
End With
Next wks
End Sub

This also saves them in the same folder using the worksheet's name. It
overwrites any existing file of the same name.
 
F

FreeDevil

Great !!
Thanks for the macro.

one more question, instead of copying a sheet from current workbook to
another workbook, is there any macro that can copy a sheet from
another workbook to the current workbook ??

so i don't need to open each workbook to move/copy a sheet one by one,
instead, i only need to specific which sheets i want to copy to the
current workbook.

That would be very very helpful if anyoen could give me some light
about that.

Million Thanks.
 
D

Dave Peterson

The workbooks would have to be opened so that the copy could take place.

And if all the files were in the same folder, something like this might work for
you:

Option Explicit
Sub testme01()

Dim myFiles() As String
Dim fCtr As Long
Dim myFile As String
Dim myPath As String
Dim tempWkbk As Workbook
Dim mySheetNames As Variant
Dim wks As Worksheet
Dim wksCtr As Long

'change to point at the folder to check
myPath = "c:\my documents\excel\test"
If Right(myPath, 1) <> "\" Then
myPath = myPath & "\"
End If

myFile = Dir(myPath & "*.xls")
If myFile = "" Then
MsgBox "no files found"
Exit Sub
End If

Application.ScreenUpdating = False

mySheetNames = Array("sheet1", "sheet2", "sheet3")

'get the list of files
fCtr = 0
Do While myFile <> ""
fCtr = fCtr + 1
ReDim Preserve myFiles(1 To fCtr)
myFiles(fCtr) = myFile
myFile = Dir()
Loop

If fCtr > 0 Then
For fCtr = LBound(myFiles) To UBound(myFiles)
Application.StatusBar = "Processing: " & myFiles(fCtr)
Set tempWkbk = Nothing
On Error Resume Next
Set tempWkbk = Workbooks.Open(Filename:=myPath & myFiles(fCtr))
On Error GoTo 0
If tempWkbk Is Nothing Then
MsgBox "Error Opening: " & myFiles(fCtr)
Else
For wksCtr = LBound(mySheetNames) To UBound(mySheetNames)
Set wks = Nothing
On Error Resume Next
Set wks = tempWkbk.Worksheets(mySheetNames(wksCtr))
On Error GoTo 0
If wks Is Nothing Then
MsgBox "Error with: " & mySheetNames(wksCtr) & _
vbLf & " of: " & myFiles(fCtr)
Else
tempWkbk.Worksheets(mySheetNames(wksCtr)).Copy _
before:=ThisWorkbook.Worksheets(1)
End If
Next wksCtr
tempWkbk.Close savechanges:=False
End If
Next fCtr
End If

With Application
.ScreenUpdating = True
.StatusBar = False
End With

End Sub

I guessed that the way you specify which worksheets to copy is by the worksheet
name with this line:

mySheetNames = Array("sheet1", "sheet2", "sheet3")

You could add more names to this array if you wanted.

And I pasted them into the workbook that held the code:
before:=ThisWorkbook.Worksheets(1)

You could change this to any open workbook:
before:=workbooks("thisismyname.xls").Worksheets("sheet99")
 
Top