Copying same data range to a summary sheet
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 |
Copying same data range to a summary sheet
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 |
Copying same data range to a summary sheet
On May 18, 5:11*am, "Vacuum Sealed" wrote:
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 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 |
Copying same data range to a summary sheet
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. |
Copying same data range to a summary sheet
On May 18, 2:09*pm, "Vacuum Sealed" wrote:
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. 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 |
Copying same data range to a summary sheet
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 |
Copying same data range to a summary sheet
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 On Thu, 19 May 2011 23:03:16 +1000, "Vacuum Sealed" wrote: 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. |
Copying same data range to a summary sheet
On May 19, 3:37*pm, Gord Dibben wrote:
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 On Thu, 19 May 2011 23:03:16 +1000, "Vacuum Sealed" wrote: 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. 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 |
Copying same data range to a summary sheet
Good to hear you have a workaround.
Break it over two sheets into manageable chunks is great idea until you upgrade<g Gord On Thu, 19 May 2011 10:31:15 -0700 (PDT), webels wrote: On May 19, 3:37*pm, Gord Dibben wrote: 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 On Thu, 19 May 2011 23:03:16 +1000, "Vacuum Sealed" wrote: 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. 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 |
All times are GMT +1. The time now is 11:36 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com