Hi All, Yes. Thank you all for the generosity displayed for me . This is working now. But, the macro causes loss of formating i.e. Column & row width . Is it possible to avoid this.
Thanks in Advance. Regards, C.G.Kumar AMW-2 On Wed, Apr 13, 2011 at 1:39 AM, hanumant shinde <arsfan2...@yahoo.co.in>wrote: > I think its done. please check and let us know if this is what you wanted > to do. > > > *From:* C.G.Kumar <kumar.bemlmum...@gmail.com> > *To:* excel-macros@googlegroups.com > *Cc:* schreiner_p...@att.net > *Sent:* Mon, 11 April, 2011 8:52:17 PM > *Subject:* Re: $$Excel-Macros$$ MACRO :PASTE SPECIAL > > Thanks Paul. > > I have amended macro.Pls find attached file with Macro named Paul() .But > still the function is not working. > > Objective is to > > 1. Copy each sheet and paste values retaining format in the same sheet > except Sheet M10701403 in the workbook. > > > Hope u suggest me way out. > > > Regards, > > C.G.Kumar > > > > > On Mon, Apr 11, 2011 at 6:28 PM, Paul Schreiner <schreiner_p...@att.net>wrote: > >> I was going to ask what it was doing, or not doingif you stepped through >> the code. >> >> But I think I see your main problem. >> >> the statemtent: >> Select Case "M10701403" >> >> means nothing.. >> you're not COMPARING anything! >> >> I think what you're wanting is: >> >> Select case ws.Name >> Case "M10701403" >> 'Do Nothing >> Case Else >> Cells.Select >> Selection.Copy >> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, >> SkipBlanks _ >> :=False, Transpose:=False >> Application.CutCopyMode = False >> End Select >> >> Next: >> >> ws.UsedRange.Copy >> should copy the cells that are "used" to the Excel clipboard. >> >> Cells.Select >> Selection.Copy >> Immeately replaces the clipboard with ALL cells in the worksheet. >> >> You should use one or the other... using both is a waste of resources. >> >> hope this helps, >> >> Paul >> >> ------------------------------ >> *From:* C.G.Kumar <kumar.bemlmum...@gmail.com> >> *To:* excel-macros@googlegroups.com >> *Sent:* Mon, April 11, 2011 5:47:04 AM >> *Subject:* $$Excel-Macros$$ MACRO :PASTE SPECIAL >> >> Dear all, >> >> Following code i am using to select each worksheet (except ) to copy and >> paste special values in each worksheet in same workbook. But, i am not >> getting desired output . >> >> Could you one help to rectify the following code; >> >> >> Sub ConvertToValues() >> Dim ws As Worksheet >> For Each ws In Worksheets >> Select Case "M10701403" ' EXCLUDED Sheet M107014031 To DO NOTHING >> >> Case Else >> ws.UsedRange.Copy >> Cells.Select >> Selection.Copy >> Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, >> SkipBlanks _ >> :=False, Transpose:=False >> Application.CutCopyMode = False >> End Select >> Next ws >> Application.CutCopyMode = False >> End Sub >> >> >> -- >> >> ---------------------------------------------------------------------------------- >> 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 > > -- > > ---------------------------------------------------------------------------------- > 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
