skip the some part of VBA

[pivilated]

Hi,
Below VBA do not do all commands. If I do it by break, it works correctly. Please comments your solutions. Thanks.

Dim a, b, c, d, e, f, g, h, k, m


'FX


k = 0
m = 0


k = Workbooks(1).Sheets("FX").Range("A1", Workbooks(1).Sheets("FX").Range("A1").End(xlDown)).Rows.Count
m = Workbooks(1).Sheets("FX").Range("P1", Workbooks(1).Sheets("FX").Range("P1").End(xlDown)).Rows.Count


If k > m Then


Workbooks(1).Sheets("FX").Range("P" & m & ":U" & m).Copy
Workbooks(1).Sheets("FX").Range("P" & m + 1 & ":U" & k).PasteSpecial xlPasteFormulas


Else


Workbooks(1).Sheets("FX").Range("P" & k + 1 & ":U" & m) = ""


End If


'PCDOWN5


a = 0
b = 0


a = Sheets("PCDOWN5").Range("A1", Workbooks(1).Sheets("PCDOWN5").Range("A1").End(xlDown)).Rows.Count
b = Sheets("PCDOWN5").Range("V1", Workbooks(1).Sheets("PCDOWN5").Range("W1").End(xlDown)).Rows.Count


If a > b Then


Sheets("PCDOWN5").Range("W" & b & ":Y" & b).Copy
Sheets("PCDOWN5").Range("W" & b + 1 & ":Y" & a).PasteSpecial xlPasteFormulas


Else


Workbooks(1).Sheets("PCDOWN5").Range("W" & a + 1 & ":Y" & b) = ""


End If


'PCDOWN7


c = 0
d = 0


c = Sheets("PCDOWN7").Range("A1", Sheets("PCDOWN7").Range("A1").End(xlDown)).Rows.Count
d = Sheets("PCDOWN7").Range("V1", Sheets("PCDOWN7").Range("W1").End(xlDown)).Rows.Count


If c > d Then


Sheets("PCDOWN7").Range("W" & d & ":W" & d).Copy
Sheets("PCDOWN7").Range("W" & d + 1 & ":W" & c).PasteSpecial xlPasteFormulas


Else


Sheets("PCDOWN7").Range("W" & c + 1 & ":W" & d) = ""


End If


'PCDOWN4


e = 0
f = 0


e = Sheets("PCDOWN4").Range("A1", Sheets("PCDOWN4").Range("A1").End(xlDown)).Rows.Count
f = Sheets("PCDOWN4").Range("V1", Sheets("PCDOWN4").Range("W1").End(xlDown)).Rows.Count


If e > f Then


Sheets("PCDOWN4").Range("W" & f & ":W" & f).Copy
Sheets("PCDOWN4").Range("W" & f + 1 & ":W" & e).PasteSpecial xlPasteFormulas


Else


Sheets("PCDOWN4").Range("W" & e + 1 & ":W" & f) = ""


End If


'PCDOWN6


g = 0
h = 0


g = Sheets("PCDOWN6").Range("A1", Sheets("PCDOWN6").Range("A1").End(xlDown)).Rows.Count
h = Sheets("PCDOWN6").Range("V1", Sheets("PCDOWN6").Range("V1").End(xlDown)).Rows.Count


If g > h Then


Sheets("PCDOWN6").Range("V" & h & ":AC" & h).Copy
Sheets("PCDOWN6").Range("V" & h + 1 & ":AC" & g).PasteSpecial xlPasteFormulas


Else


Sheets("PCDOWN6").Range("V" & g + 1 & ":AC" & h) = ""


End If

 

[Joe4]

How about you just explain to us in plain English exactly what it is that you are trying to do?

 

[pivilated]

How about you just explain to us in plain English exactly what it is that you are trying to do?

I have 5 sheets in one workbook and in those sheets there are copy paste area and formula area. For example I copy 100 row and but the formula area has 99 rows. I do not want to make formula area 100 row by manually ,so I wrote VBA which I believe it should equal the row number between copy area and formula area.

However, when I run the whole macro, vba works correctly in some sheets. But If I put break and run macro, VBA works properly. It looks it skips some part of VBA

Could you help me?

 

[Joe4]

Could you help me?

Without seeing your sheets, that is a bit difficult.
You would have to provide an exact step-by-step detailed explanation of how this should work, being sure to include range references in your explanation.

 

[pivilated]

Without seeing your sheets, that is a bit difficult.
You would have to provide an exact step-by-step detailed explanation of how this should work, being sure to include range references in your explanation.

In VBA there are 'FX, "PCDOWN5, 'PCDOWN4 etc. Those are name of the sheets. All are same. I do not receive any error. However, After I run macro, for example FX sheets are not updated. others are done well. But this does not only happen to one sheet. If I put break it works well. I do not understand why VBA skips some jobs. Is this normal?

 

[Joe4]

If you are trying to run the exact same code on multiple sheets, rather than duplicate the code for each sheet, simply store your sheet names in an Array, and loop through them in your code.
Then you only need the one block of code instead of repeating it for each sheet.

Here is an example of how to set up this array: https://www.mrexcel.com/forum/excel-questions/902458-sheets-next-loop-issue.html#post4343785

 

[pivilated]

If you are trying to run the exact same code on multiple sheets, rather than duplicate the code for each sheet, simply store your sheet names in an Array, and loop through them in your code.
Then you only need the one block of code instead of repeating it for each sheet.

Here is an example of how to set up this array: https://www.mrexcel.com/forum/excel-questions/902458-sheets-next-loop-issue.html#post4343785

Thanks for your reply. The problem is not every array in each is same. For instance, In fx sheet I copy A to D and for PCDOWN5, I do copy A to W. Columns are not same unfortunately.

 

[Joe4]

Have you tried stepping though your code line-by-line using F8 to see what happens?
If you set it up so that you can see both your code and your sheet at the same time (either by using two monitors, or split-screen), you can go through your code one line at a time using F8 and watch what happens to your sheet. A lot of times, these issues will become evident when you see what is happening.

 

[pivilated]

Have you tried stepping though your code line-by-line using F8 to see what happens?
If you set it up so that you can see both your code and your sheet at the same time (either by using two monitors, or split-screen), you can go through your code one line at a time using F8 and watch what happens to your sheet. A lot of times, these issues will become evident when you see what is happening.

Yes, I run code step by step by F8. And it works well. However If I use F5 (run whole macro), it skips some sheets.

 

[Joe4]

Yes, I run code step by step by F8. And it works well. However If I use F5 (run whole macro), it skips some sheets.

That doesn't make sense to me.

Is there anything at all different when you run step-by-step or all at once?
Specifically, are you on the same sheet when you call the code to run (and are any of your sheets hidden)?
Do you have any event procedure VBA code that may be interfering with this?