Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 . |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
copy data from sheet2 to sheet1 when sheet2 has variable # of rows | Excel Discussion (Misc queries) | |||
jump to cell in sheet2 when values in sheet1 is true | Excel Discussion (Misc queries) | |||
Click one cell sheet1, display all related recs on sheet2 | Excel Worksheet Functions | |||
formula-add 2 numbers of cell a1 and a2 of sheet1 ans-b9in sheet2 | Excel Worksheet Functions | |||
can i type sheet1 A5 and make it show Sheet2 A6 cell | Excel Discussion (Misc queries) |