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

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 befo=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.B old
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

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

-----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
befo=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.

re
 

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
-----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
befo=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


.