Solved

Problem with running consecutive MACRO's

Posted on 2007-11-23
9
664 Views
Last Modified: 2008-02-01
Hi,
I am tryign to runt he two macros below consecutively and can not work out what the second one doesn't do anything.

Can anyone suggest a solution?

Thanks,

Andrew

Sub CopyRows2amended2()
    Dim c As Range, Row As Integer, i As Integer
    With Worksheets("Sheet1")
        For Row = .Range("B1").End(xlDown).Row To 1 Step -1
            If .Cells(Row, 1).Value = 1 Then
                For i = 1 To 6
                    .Cells(Row, 1).EntireRow.Insert
                Next i
                Worksheets("Sheet2").Range("A1:Z5").Copy
                .Cells(Row, 2).Select
                ActiveCell.PasteSpecial xlPasteValues
                ActiveCell.PasteSpecial xlPasteFormats
                Cells(Row + 2, 4) = .Cells(Row + 6, 2)
            End If
           
        Next Row
       
        End With
       

       
         Columns("AB:AB").Select
    Selection.Copy

    Columns("A:A").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
       
   
 
End Sub




Sub OfficendPresentHolder()
    Dim c As Range, Row As Integer, i As Integer
    With Worksheets("Sheet1")
        For Row = .Range("B1").End(xlDown).Row To 1 Step -1
            If .Cells(Row, 1).Value = 1 Then
                For i = 1 To 2
                    .Cells(Row, 1).EntireRow.Insert
                Next i
                Worksheets("Sheet2").Range("A1:Z2").Copy
                .Cells(Row, 2).Select
                ActiveCell.PasteSpecial xlPasteValues
                ActiveCell.PasteSpecial xlPasteFormats
                'Cells(Row + 2, 4) = .Cells(Row + 6, 2)
            End If
           
        Next Row
       
        End With
   
 
 
End Sub

0
Comment
Question by:AndrewMcLaughlin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
  • +1
9 Comments
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 20338465
Well before the end sub...place this code...this will run the second run macro...

call OfficendPresentHolder

This will run the macro once the first macro gets over..
0
 

Author Comment

by:AndrewMcLaughlin
ID: 20338561
Sorry  - I didn't explain that very well.  The problem is that if I run the furst macro it works, but then the second macro doesn't do anything.  The same happens if I reverse the order.  I don't understand what is going on?!




Sub CopyRows2amended2()
    Dim c As Range, Row As Integer, i As Integer
    With Worksheets("Sheet1")
        For Row = .Range("B1").End(xlDown).Row To 1 Step -1
            If .Cells(Row, 28).Value = 1 Then
                For i = 1 To 6
                    .Cells(Row, 1).EntireRow.Insert
                Next i
                Worksheets("Sheet2").Range("A1:Z5").Copy
                .Cells(Row, 2).Select
                ActiveCell.PasteSpecial xlPasteValues
                ActiveCell.PasteSpecial xlPasteFormats
                Cells(Row + 2, 4) = .Cells(Row + 6, 2)
            End If
           
        Next Row
       
        End With
       

       
         
 
End Sub




Sub OfficendPresentHolder()
    Dim c As Range, Row As Integer, i As Integer
    With Worksheets("Sheet1")
        For Row = .Range("B1").End(xlDown).Row To 1 Step -1
            If .Cells(Row, 29).Value = 1 Then
                For i = 1 To 2
                    .Cells(Row, 1).EntireRow.Insert
                Next i
                Worksheets("Sheet2").Range("A1:Z2").Copy
                .Cells(Row, 2).Select
                ActiveCell.PasteSpecial xlPasteValues
                ActiveCell.PasteSpecial xlPasteFormats
                Cells(Row + 1, 4) = .Cells(Row + 2, 3)
            End If
           
        Next Row
       
        End With
   
 
 
End Sub
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 20338573
Well Yeah Coz u can run a macro only once...now from that macro to call another macro u need to give a command...

and if u enter this command what i told u..after the first macro is complete its gonna call the second macro...and will start running...

here is the command..just paste it before end sub in first macro

call OfficendPresentHolder


0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 85

Expert Comment

by:Rory Archibald
ID: 20338583
Can you post a workbook so we can see what you're working with?
Have you tried stepping into the second macro and then running it line by line (using f8) to see what is going on?
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 20338598
AndrewMcLaughlin said:
>>    Dim c As Range, Row As Integer, i As Integer

Saurabh seems to have nailed what you're asking about.  Here are two friendly pieces of
advice:

1) Never, ever, ever use an Integer data type for a row number.  Integer tops out at ~32,767 or so;
and Excel 97-2003 worksheet can have 65,536 rows, and an Excel 2007 worksheet can have a
smidge over a million rows.  If you get in the habit of using Integer for rown numbers, one of
these days you will get an overflow error.  Use Long instead.  For that matter, just stop using
Integer altogether and always use Long: under many circumstances, Integer actually runs
*slower* than Long (because the arithmetic engine has to do implicit conversions from your
Integer variable to Long and then back to Integer).

2) It is almost never necessary to actually select ranges of worksheets.  Using unnecessary
Select or Activate statements merely slows down your code.

Regards,

Patrick
0
 

Author Comment

by:AndrewMcLaughlin
ID: 20338620
Thanks for the tips matthewspatrick.

However Saurabh's solution doesn't work.

I have figured that the problem is that after running either of the macros I introduce blank cells into column b and the second macro stops at the first blank line it meets.
0
 
LVL 85

Accepted Solution

by:
Rory Archibald earned 500 total points
ID: 20338631
Oh yes - use:
For Row = .Cells(.Rows.Count, "B").End(xlUp).Row To 1 Step -1

instead.
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 20338672
lol...me didnt took not of that...i thought u want to run the second macro automatically.. once u done with the first macro...
0
 
LVL 59

Expert Comment

by:Saurabh Singh Teotia
ID: 20338674
oops...note....
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

737 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question