Hi Ayush,
That works perfect! Wow...thanks a lot! Your a lifesaver!!
Wish you the best!
Best Regards,
Shivam
Shivam
On Sat, Feb 13, 2010 at 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 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
>> 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
>
>
--
----------------------------------------------------------------------------------
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
