Re: $$Excel-Macros$$ MACRO :PASTE SPECIAL
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 wrote: > I think its done. please check and let us know if this is what you wanted > to do. > > > *From:* C.G.Kumar > *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 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 >> *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 >> >
Re: $$Excel-Macros$$ MACRO :PASTE SPECIAL
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 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
$$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
