Run Macro one after the other

[msiyab]

Hi,

I am trying a VBA, with multiple functions. When I run the macro, before one process is finished the second one start which does not give the desired result.

I want it to pause till the previous Sub is complete and then run the next line of commands.

Here is the code:

Sub UnmergeAllCells()
ActiveSheet.Cells.UnMerge
End Sub


Sub UnWrapTextAllCells()
Cells.WrapText = False
End Sub


Sub PasteSpecial_ValuesOnly()
'Copy A Range of Data
  Worksheets("Sheet1").Range("S2").Copy
'PasteSpecial Values Only
  Worksheets("Sheet2").Range("T2").PasteSpecial Paste:=xlPasteValues
'Clear Clipboard (removes "marching ants" around your original data set)
  Application.CutCopyMode = False
End Sub


Sub RowAndColumnAutoFit()
Worksheets("Sheet1").Columns("A:BF").AutoFit
End Sub


Sub deleteMultipleRows()
Rows("3:6").Delete
End Sub


Sub DeleteColumns()
    Worksheets("Sheet1").Range("A:C,E:F,H:J,L:S,U:X,Z:AB,AD:AH,AJ:BF").EntireColumn.Delete
End Sub

[DocAElstein]

Hi msiyab,
I do not really understand most of your post…

---

.... When I run the macro, ...

What macro are you talking about??

---

...When I run the macro, before one process is finished the second one start which does not give the desired result.
I want it to pause till the previous Sub is complete and then run the next line of commands.....

I don’t understand. That does not really make any sense. You are explaining very badly.

---

I think you need to try to explain everything again.

---

What is the problem in doing this?

Sub CallEmAll()
 Call UnmergeAllCells
 Call UnWrapTextAllCells
 Call PasteSpecial_ValuesOnly
 Call RowAndColumnAutoFit
 Call deleteMultipleRows
 Call DeleteColumns
End Sub

---

What is the problem in doing this?

Sub DoItAll()
'Sub UnmergeAllCells()
 ActiveSheet.Cells.UnMerge
'End Sub
'Sub UnWrapTextAllCells()
 Cells.WrapText = False
'End Sub
'Sub PasteSpecial_ValuesOnly()
'Copy A Range of Data
 Worksheets("Sheet1").Range("S2").Copy
'PasteSpecial Values Only
 Worksheets("Sheet2").Range("T2").PasteSpecial Paste:=xlPasteValues
'Clear Clipboard (removes "marching ants" around your original data set)
 Application.CutCopyMode = False
'End Sub
'Sub RowAndColumnAutoFit()
 Worksheets("Sheet1").Columns("A:BF").AutoFit
'End Sub
'Sub deleteMultipleRows()
 Rows("3:6").Delete
'End Sub
'Sub DeleteColumns()
 Worksheets("Sheet1").Range("A:C,E:F,H:J,L:S,U:X,Z:AB,AD:AH,AJ:BF").EntireColumn.Delete
'End Sub
End Sub

---

Alan

[msiyab]

---

What is the problem in doing this?

Sub DoItAll()
'Sub UnmergeAllCells()
 ActiveSheet.Cells.UnMerge
'End Sub
'Sub UnWrapTextAllCells()
 Cells.WrapText = False
'End Sub
'Sub PasteSpecial_ValuesOnly()
'Copy A Range of Data
 Worksheets("Sheet1").Range("S2").Copy
'PasteSpecial Values Only
 Worksheets("Sheet2").Range("T2").PasteSpecial Paste:=xlPasteValues
'Clear Clipboard (removes "marching ants" around your original data set)
 Application.CutCopyMode = False
'End Sub
'Sub RowAndColumnAutoFit()
 Worksheets("Sheet1").Columns("A:BF").AutoFit
'End Sub
'Sub deleteMultipleRows()
 Rows("3:6").Delete
'End Sub
'Sub DeleteColumns()
 Worksheets("Sheet1").Range("A:C,E:F,H:J,L:S,U:X,Z:AB,AD:AH,AJ:BF").EntireColumn.Delete
'End Sub
End Sub

---

Alan

This part of the code does not seem to work. After un-merging and un-wrapping text, I want the text in cell S2 to be cut/copy/pasted to T2, before the blank columns & rows are deleted.

'Sub PasteSpecial_ValuesOnly()
'Copy A Range of Data
 Worksheets("Sheet1").Range("S2").Copy
'PasteSpecial Values Only
 Worksheets("Sheet2").Range("T2").PasteSpecial Paste:=xlPasteValues
'Clear Clipboard (removes "marching ants" around your original data set)
 Application.CutCopyMode = False
'End Sub

[DocAElstein]

Hi
I think that the problem that you are suggesting is not possible.

The text in cell S2 is pasted to T2 before any deleting is done.
Excel will never do those deleting commands until it has finished the copy and paste.
It is impossible for Excel to do those deleting commands until it has finished the copy and paste.

---

I expect you have a different problem.
I expect the macro is not doing what you think it is doing.
I expect the macro is working perfectly, but it is doing something different to what you want.

---

I think you need to explain very carefully and very fully , exactly what it is that you are trying to do
Important is : You must always say exactly what worksheet is having any actions to be taken.

[jolivanes]

Like DocAElstein suggested. Explain in detail.
But also:
In "Sub UnmergeAllCells()" you reference ActiveSheet
In "Sub UnwrapTextAllCells()" you don't reference any sheet.
In "Sub PasteSpecial_ValuesOnly()" you reference 2 sheets (Sheet1 and Sheet2)
In "Sub RowAndColumnAutoFit()" you reference Sheet1
In "Sub deleteMultipleRows()" you again don't reference any sheet
In "Sub DeleteColumns()" you reference sheet1
Always reference properly. That gives you an advantage that code can be run from any sheet and still work as it was designed to do.
Your 6 little macros could be in one like below.
Change Sheet references where required as we don't know which sheets you're talking about.

Sub msiyab()
With Sheets("Sheet1")    '<---- Everything refers to Sheet1 except the first part of the "Sheet2" line. (Obvious I would say)
    .Cells.UnMerge    '<----- Do away with using merged cells. They give you guanranteed problems at one time or another
    .Cells.WrapText = False
    Sheets("Sheet2").Range("T2").Value = .Range("S2").Value
    .Columns("A:BF").AutoFit
    .Rows("3:6").Delete
    .Range("A:C,E:F,H:J,L:S,U:X,Z:AB,AD:AH,AJ:BF").EntireColumn.Delete
End With
End Sub

Now you have half the amout of lines plus you have the advantage that you can run the macro from any sheet you like.
And I don't think you need the "Reply With Quote" Just extra not needed clutter.