Problem with running consecutive MACRO's

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

AndrewMcLaughlinAsked:
Who is Participating?
 
Rory ArchibaldConnect With a Mentor Commented:
Oh yes - use:
For Row = .Cells(.Rows.Count, "B").End(xlUp).Row To 1 Step -1

instead.
0
 
Saurabh Singh TeotiaCommented:
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
 
AndrewMcLaughlinAuthor Commented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Saurabh Singh TeotiaCommented:
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
 
Rory ArchibaldCommented:
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
 
Patrick MatthewsCommented:
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
 
AndrewMcLaughlinAuthor Commented:
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
 
Saurabh Singh TeotiaCommented:
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
 
Saurabh Singh TeotiaCommented:
oops...note....
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.