How to set sheet1!cell = sheet2!samecell and set format?

[Paul]

Hi,

I have a workbook with approx. 50 sheets. I am trying to add a summary sheet
by referencing corresponding cells in the existing sheets.

I seem to be doing ok as far as values are concerned but I can't figure out
how to assign cell formats simply.

As follows;

sub MakeSummary()

ActiveWorkbook.Worksheets.Add before:=ActiveWorkbook.Worksheets(1)
ActiveSheet.Name = "Departments"
ActiveSheet.Range("A1").Activate

For intCell = 1 To endRow

' This bit goes ok! Note: Only referencing a single sheet in this
sample.
strCell = "='" & ActiveWorkbook.Worksheets(2).Name & "'" & "!A" &
intCell
ActiveCell.Formula = strCell
strCell = "A" & intCell

' Next bit doesn't work - Why? Is there a better way?
ActiveCell.Range(strCell).Font.Bold = _
ActiveWorkbook.Worksheets(2).Range(strCell).Font.Bold
If ActiveCell.Value = 0 Then
ActiveCell.Value = ""
End If
' Move down 1 cell
ActiveCell.Offset(1, 0).Activate
Next
end sub

Any help much appreciated!

Paul

 

[nath]

-----Original Message-----
Hi,

I have a workbook with approx. 50 sheets. I am trying to add a summary sheet
by referencing corresponding cells in the existing sheets.

I seem to be doing ok as far as values are concerned but I can't figure out
how to assign cell formats simply.

As follows;

sub MakeSummary()

ActiveWorkbook.Worksheets.Add before:=ActiveWorkbook.Worksheets(1)
ActiveSheet.Name = "Departments"
ActiveSheet.Range("A1").Activate

For intCell = 1 To endRow

' This bit goes ok! Note: Only referencing a single sheet in this
sample.
strCell = "='" & ActiveWorkbook.Worksheets (2).Name & "'" & "!A" &
intCell
ActiveCell.Formula = strCell
strCell = "A" & intCell

' Next bit doesn't work - Why? Is there a better way?
ActiveCell.Range(strCell).Font.Bold = _
ActiveWorkbook.Worksheets(2).Range (strCell).Font.Bold
If ActiveCell.Value = 0 Then
ActiveCell.Value = ""
End If
' Move down 1 cell
ActiveCell.Offset(1, 0).Activate
Next
end sub

Any help much appreciated!

Paul


.
I would use if statements like:

if activeworkbook.sheets(2).range(strCell).font.bold =
true then
activesheet.range(strcell.font.bold= true
else
end if

nath.

 

[Gunnar]

If the values you want to summarize is one the same place
on each sheet, you can use the Consolidate function on the
DATA tab..

Gunnar