I agree with Don. The first thing I do is delete all requests that do not describe the problem in the subject line.
Posting Tips: Subjects like "Urgent Help Needed" convey no valueable information. the problem isn't urgent for ME, and if the poster didn't need help, he/she wouldn't be posting. Next, I consider myself an "expert" in many facets of Excel and VBA, but I have very little experience in Pivot Tables. Therefore, I skip questions outside of my areas of expertise and let someone who knows what they're talking about answer those questions. So, Subjects that sound interesting, challenging, or within my areas of expertise, I'll open and look at. If the author didn't feel like "wasting" his time writing a descriptive subject line, I don't feel obligated to "waste my time" trying to figure out what he needs help with. I receive HUNDREDS of emails every day... I get to choose which ones to open... just my opinion... Paul ----------------------------------------- “Do all the good you can, By all the means you can, In all the ways you can, In all the places you can, At all the times you can, To all the people you can, As long as ever you can.” - John Wesley ----------------------------------------- ________________________________ From: dguillett1 <dguille...@gmail.com> To: excel-macros@googlegroups.com Sent: Thu, October 27, 2011 9:56:09 AM Subject: Re: $$Excel-Macros$$ Urgent help needed Anil, Please do NOT use urgent in your request as most who would help may even put that request last on their list or ignore the request. Try this code, assuming Master Sheet does NOT exist. ======= Option Explicit Sub ConsodilateSheetsSAS() Dim dlr As Long Dim i As Long Application.ScreenUpdating = False Sheets.Add before:=Sheets(1) With ActiveSheet .Name = "Master Sheet" For i = 2 To Sheets.Count dlr = .Cells(Rows.Count, 1).End(xlUp).Row + 1 Sheets(i).UsedRange.Copy .Cells(dlr, 1) .Cells(dlr, "h") = Sheets(i).Name .Cells(dlr, "H").Interior.Color = vbCyan Next i .Columns.AutoFit .Rows(1).Delete End With Application.ScreenUpdating = True End Sub ============ Don Guillett SalesAid Software dguille...@gmail.com From: Anil Bhange Sent: Thursday, October 27, 2011 5:24 AM To: excel-macros@googlegroups.com Subject: $$Excel-Macros$$ Urgent help needed Hi Expert, I have found one code which can consolidate the excel worksheets into one worksheet, but I wanted my worksheet name should also appear after consolidation in column. I am unable to make changes in this code, please help me urgently, Sub CopyFromWorksheets() Dim wrk As Workbook 'Workbook object - Always good to work with object variables Dim sht As Worksheet 'Object for handling worksheets in loop Dim trg As Worksheet 'Master Worksheet Dim rng As Range 'Range object Dim colCount As Integer 'Column count in tables in the worksheets Set wrk = ActiveWorkbook 'Working in active workbook For Each sht In wrk.Worksheets If sht.Name = "Master" Then MsgBox "There is a worksheet called as 'Master'." & vbCrLf & _ "Please remove or rename this worksheet since 'Master' would be" & _ "the name of the result worksheet of this process.", vbOKOnly + vbExclamation, "Error" Exit Sub End If Next sht 'We don't want screen updating Application.ScreenUpdating = False 'Add new worksheet as the last worksheet Set trg = wrk.Worksheets.Add(After:=wrk.Worksheets(wrk.Worksheets.Count)) 'Rename the new worksheet trg.Name = "Master" 'Get column headers from the first worksheet 'Column count first Set sht = wrk.Worksheets(1) colCount = sht.Cells(1, 255).End(xlToLeft).Column 'Now retrieve headers, no copy&paste needed With trg.Cells(1, 1).Resize(1, colCount) .Value = sht.Cells(1, 100).Resize(1, colCount).Value 'Set font as bold .Font.Bold = True End With 'We can start loop For Each sht In wrk.Worksheets 'If worksheet in loop is the last one, stop execution (it is Master worksheet) If sht.Index = wrk.Worksheets.Count Then Exit For End If 'Data range in worksheet - starts from second row as first rows are the header rows in all worksheets Set rng = sht.Range(sht.Cells(2, 1), sht.Cells(65536, 1).End(xlUp).Resize(, colCount)) 'Put data into the Master worksheet trg.Cells(65536, 1).End(xlUp).Offset(1).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value Next sht 'Fit the columns in Master worksheet trg.Columns.AutoFit 'Screen updating should be activated Application.ScreenUpdating = True End Sub Anil Bhange Assistant Manager Financial Reporting & Compliance, TATA Communications Ltd. VSB, Fort, Mumbai – 400 001, India 'Desk : + 91 22 6659 2320 | IP Phone : 51 2320 |Mobile :+ 91 90290 32123 anil.bha...@tatacommunications.com -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel -- ---------------------------------------------------------------------------------- Some important links for excel users: 1. Follow us on TWITTER for tips tricks and links : http://twitter.com/exceldailytip 2. Join our LinkedIN group @ http://www.linkedin.com/groups?gid=1871310 3. Excel tutorials at http://www.excel-macros.blogspot.com 4. Learn VBA Macros at http://www.quickvba.blogspot.com 5. Excel Tips and Tricks at http://exceldailytip.blogspot.com To post to this group, send email to excel-macros@googlegroups.com <><><><><><><><><><><><><><><><><><><><><><> Like our page on facebook , Just follow below link http://www.facebook.com/discussexcel
