Copying same data range to a summary sheet

  • Thread starter Thread starter webels
  • Start date Start date
W

webels

Hi
Cant seem to find quite what I am looking for, basically
I have a workbook with between 10 and 20 worksheets. I would like to
copy the same range c12:L42 (no of hours) on each sheet and paste to a
summary sheet. Some of the cells in the ranges may be blank (no
entry), I would like the summary to be pasted into the same range in
the summary sheet and add each entry as follows

If cell c12 has 5 on one sheet and 7 on another and no entry on each
of the other sheets the value in the summary sheet c12 would be 12.

I hope this is an understandable summary of my problem.

Many thanks as usual
Eddie
 
Hi Eddie

Try Ron DeBruin's website, he has some excellent codes to help you do just
what you need.

http://www.rondebruin.nl/summary.htm

HTH
Mick
Click to expand...

Thanks Mick
Some nice code here alright, my range is too wide and i am getting an
error on the line

Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)

It seems that excel runs out of columns for this macro with the range
c12:L42.

Have you any suggestions - excuse my total ignorance.

Thanks
Eddie
 
Hi Eddie

I am only an intermediate myself and get regular help also, so I can say
with so certainty that to make it easier for everyone to see, it is good
practice to paste in as much of the code as possible so any anomolies can be
seen.

Hmmm, as for Excel running out of columns, more likely the problem maybe a
possible typo in the code as each sheet contains:

256 Columns x 65,536 Rows = 16,777,216 Cells.

Regards
Mick.
 
Hi Eddie

I am only an intermediate myself and get regular help also, so I can say
with so certainty that to make it easier for everyone to see, it is good
practice to paste in as much of the code as possible so any anomolies canbe
seen.

Hmmm, as for Excel running out of columns, more likely the problem maybe a
possible typo in the code as each sheet contains:

256 Columns x 65,536 Rows = 16,777,216 Cells.

Regards
Mick.
Click to expand...

Thanks again Mick

Sorry for being so limited with my code the entire code is as follows,
totally Rons except for the reference.
My feeling is it may be a problem as I said running out of columns as
it looks like i will need over 300 columns

Sub Summary_All_Worksheets_With_Formulas()
Dim Sh As Worksheet
Dim Newsh As Worksheet
Dim myCell As Range
Dim ColNum As Integer
Dim RwNum As Long
Dim Basebook As Workbook

With Application
.Calculation = xlCalculationManual
.ScreenUpdating = False
End With

'Delete the sheet "Summary-Sheet" if it exist
Application.DisplayAlerts = False
On Error Resume Next
ThisWorkbook.Worksheets("Summary-Sheet").Delete
On Error GoTo 0
Application.DisplayAlerts = True

'Add a worksheet with the name "Summary-Sheet"
Set Basebook = ThisWorkbook
Set Newsh = Basebook.Worksheets.Add
Newsh.Name = "Summary-Sheet"

'The links to the first sheet will start in row 2
RwNum = 1

For Each Sh In Basebook.Worksheets
If Sh.Name <> Newsh.Name And Sh.Visible Then
ColNum = 1
RwNum = RwNum + 1
'Copy the sheet name in the A column
Newsh.Cells(RwNum, 1).Value = Sh.Name

For Each myCell In Sh.Range("C12:l40") '<--Change the
range
ColNum = ColNum + 1
Newsh.Cells(RwNum, ColNum).Formula = _
"='" & Sh.Name & "'!" & myCell.Address(False, False)
Next myCell

End If
Next Sh

Newsh.UsedRange.Columns.AutoFit

With Application
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub


Many thanks for any help

Eddie
 
Eddie

This section of code

For Each myCell In Sh.Range("C12:l40")

is it ("C12:I40") as it kinda looks like :140) as in 0ne forty instead of
:i40)

Wow, 300 Columns, that's alot considering your range only covers 7 columns,
you must have over 40 sheets of which to transpose into a Summary.

now, i'm not 100% sure, but..! If you are using 2007 onward I farily certain
you can increase the number of Columns, you may need to run a google search
and read any post threads that come up, although I do recall a post here a
while back discussing it.

It could be helpful to understand the nature of the structure and what it is
you are attempting to achieve, as there maybe an alternative to the layout
and structure which may provide a quicker streamlined version.

HTH
Mick
 
Mick

Problem has nothing to do with the number of sheets in the workbook.

C12:L40 may be only 7 columns but contains 290 cells.

The macro places each sheet's range into its own row on new sheet.

Cannot be done in 2003 or earlier with 256 column limit.

2007 no problem with 16384 columns.


Gord Dibben MS Excel MVP
 
Mick

Problem has nothing to do with the number of sheets in the workbook.

C12:L40 may be only 7 columns but contains 290 cells.

The macro places each sheet's range into its own row on new sheet.

Cannot be done in 2003 or earlier with 256 column limit.

2007 no problem with 16384 columns.

Gord Dibben     MS Excel MVP
Click to expand...

Thanks Gord for your input I am using 2003 and have the 256 limit as
you described. Using Micks suggestion I am using the above macro over
two sheets
and making good progress with what I want to achieve.
Basically I get returns on Staff time sheets for hours covered
(differing shifts) in a range C12:l42 and I want
to make sure add all the entries for their corresponding entry on
others sheets to master/summary sheet.

I will let ye know how I get on, many thanks to Mick for his lead and
it is proving very useful

Eddie
 
Good to hear you have a workaround.

Break it over two sheets into manageable chunks is great idea until you
upgrade<g>


Gord
 
Back
Top