Solved

Problem with running consecutive MACRO's

Posted on 2007-11-23
9
660 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
  • 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

 
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

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

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

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,…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

832 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