Hi Ayush,
That works perfect! Wow...thanks a lot! Your a lifesaver!!
Wish you the best!
Best Regards,
Shivam
On Feb 13, 1:03 am, ayush jain <jainayus...@gmail.com> wrote:
> Hi Shivam,
>
> Please find enclosed the solution. The below two macros has been designed
> for the solution. For macro purpose, I had to unmerge Column B & C. I hope
> that should not be a problem.Let us know if any further help is required.
>
> Private Sub CommandButton1_Click()
> For i = 1 To 10000
> If ActiveSheet.Range("B" & i).Value = "Primary Supplier 1" Then
> Range("B" & (i + 1) & ":" & "K" & (i + 1)).Select
> Selection.Copy
> Selection.Insert Shift:=xlDown
> For k = 1 To 10
> If ActiveSheet.Range(Chr(65 + k) & (i + 1)).HasFormula =
> False Then
> ActiveSheet.Range(Chr(65 + k) & (i + 1)).ClearContents
> End If
> Next
> Exit For
> End If
> Next
> End Sub
>
> Private Sub CommandButton2_Click()
> For i = 1 To 10000
> If ActiveSheet.Range("B" & i).Value = "Participant 1" Then
> Range("B" & (i + 1) & ":" & "K" & (i + 1)).Select
> Selection.Copy
> Selection.Insert Shift:=xlDown
> For k = 1 To 10
> If ActiveSheet.Range(Chr(65 + k) & (i + 1)).HasFormula =
> False Then
> ActiveSheet.Range(Chr(65 + k) & (i + 1)).ClearContents
> End If
> Next
> Exit For
> End If
> Next
> End Sub
>
> Best regards,
> Ayush Jain
>
>
>
> On Sat, Feb 13, 2010 at 10:56 AM, Shivam.Shah <shivam...@gmail.com> wrote:
> > Any help will be much appreciated. I have attached the file. The row
> > being copied is row "X" for both buttons
>
> > I have 2 buttons in the same worksheet to copy a row and insert the
> > copied row below it. I have this macro running for 2 different rows in
> > the worksheet, assigned to the respective 2 buttons. See my code
> > below.
>
> > ______________________________________________________
>
> > Code:
>
> > Private Sub CommandButton1_Click()
> > Range("$B$7:$L$7").Select
> > Selection.Copy
> > Selection.Insert Shift:=xlDown
> > End Sub
>
> > Private Sub CommandButton2_Click()
> > Range("$B$12:$L$12").Select
> > Selection.Copy
> > Selection.Insert Shift:=xlDown
> > End Sub
>
> > ________________________________________________________
>
> > The problem is:
>
> > 1) I want just the formulas/formatting to be pasted, and not the
> > entered values.
> > 2) And more importantly, if I use Button 1, then row 7 is copied and
> > the copied row is added below row 7 and the rows move down. But then,
> > for Button 2, row 12 is different....now as it has moved down (it has
> > become 13, but the macro for button 2 is still copying row 12), if you
> > understand? How do I keep it fixed that it always copies the contents
> > of row 12 even if a row is added above and the rows move down, making
> > row 12 into 13 and so on.
>
> > Thanks again,
>
> > Shivam
>
> > --
>
> > ----------------------------------------------------------------------------------
> > Some important links for excel users:
> > 1. Follow us on TWITTER for tips tricks and links :
> >http://twitter.com/exceldailytip
> > 2. Excel and VBA Tutorials(Video and Text), Free add-ins downloads at
> >http://www.excelitems.com
> > 3. Excel tutorials athttp://www.excel-macros.blogspot.com
> > 4. Learn VBA Macros athttp://www.quickvba.blogspot.com
> > 5. Excel Tips and Tricks athttp://exceldailytip.blogspot.com
>
> > To post to this group, send email toexcel-mac...@googlegroups.com
> > If you find any spam message in the group, please send an email to:
> > Ayush Jain @ jainayus...@gmail.com
> > <><><><><><><><><><><><><><><><><><><><><><>
> > HELP US GROW !!
>
> > We reach over 6,700 subscribers worldwide and receive many nice notes about
> > the learning and support from the group.Let friends and co-workers know they
> > can subscribe to group at
> >http://groups.google.com/group/excel-macros/subscribe
>
> --
> Best regards,
> Ayush Jain
>
> Shivam.xls
> 95KViewDownload
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
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
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
