Sheet Code

[Glenn Robertson]

Hi,

Is this possible, if so can someone help!
What I want is to put code on Sheet 4 so that when sheet 4
is activated, it shows all the information on sheet 1,2
and 3.
All the sheets have the same template, with Row 1 on each
sheet being the coloumn header, if that makes sense.

Thanks

 

[Bob Phillips]

Glenn,

a bit thin on detail as to what info is to be show, but basically you just
trap the activate event on the sheet you are targeting.


Private Sub Worksheet_Activate()
'put your code here
End Sub

'This is worksheet event code, which means that it needs to be
'placed in the appropriate worksheet code module, not a standard
'code module. To do this, right-click on the sheet tab, select
'the View Code option from the menu, and paste the code in.


--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Guest]

Hi,
Thanks for that but not sure of the code.
Sheet 1, 2, 3 and 4 are all layed out like this:-

A B C
NAME DATE SIZE
Glenn 12.01 12
Dave 14.06 18

Sheet 4 has no info in it except for the headers (Name,
Date, Size) in row 1.
Sheet 1, 2 and 3 contains the data. I have a macro that
will display a inputbox and will then autofilter sheet1,
sheet 2 and sheet 3 to display the result of my input box.
What i am trying to get it to do is when sheet 4 is
selected it will show all the info from sheet 1,2 and 3 on
sheet 4

 

[Bob Phillips]

Glenn,

All you need to do is plug this sort of code onto your macro after the
autofilter

Dim iNextRow As Long
Dim cRows As Long

iNextRow = Cells(Rows.Count, "A").End(xlUp).Row
If iNextRow = 1 And Range("A1").Value = "" Then
Else
iNextRow = iNextRow + 1
End If
With Worksheets("Sheet1")
cRows = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("A1:A" &
cRows).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Cells(iNextRow, "A")
End With

iNextRow = Cells(Rows.Count, "A").End(xlUp).Row
If iNextRow = 1 And Range("A1").Value = "" Then
Else
iNextRow = iNextRow + 1
End If
With Worksheets("Sheet2")
cRows = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("A1:A" &
cRows).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Cells(iNextRow, "A")
End With


iNextRow = Cells(Rows.Count, "A").End(xlUp).Row
If iNextRow = 1 And Range("A1").Value = "" Then
Else
iNextRow = iNextRow + 1
End If
With Worksheets("Sheet3")
cRows = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("A1:A" &
cRows).SpecialCells(xlCellTypeVisible).EntireRow.Copy _
Cells(iNextRow, "A")
End With



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)