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

  • Thread starter Thread starter Paul
  • Start date Start date
P

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
 
-----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:
Click to expand...

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

nath.
 
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
 
Back
Top