Seconding Don and Paul on that. Another point that comes to the table is the lack of proper Subject Titles that clearly and concisely describes the problem/objective. I know that this is hinted in the forum rules, but do we not need to moderate this?
Regards, Sam On Thu, Oct 27, 2011 at 7:39 PM, Paul Schreiner <schreiner_p...@att.net>wrote: > 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 <anil.bha...@tatacommunications.com> > *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 > -- Sam Mathai Chacko -- ---------------------------------------------------------------------------------- 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