Solved

Problem with running consecutive MACRO's

Posted on 2007-11-23
9
656 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
 
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
Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

 
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

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,…
When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
The viewer will learn how to use a discrete random variable to simulate the return on an investment over a period of years, create a Monte Carlo simulation using the discrete random variable, and create a graph to represent the possible returns over…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.

743 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now