sharknado523
New Member
- Joined
- Jan 10, 2018
- Messages
- 5
Hello everyone,
Hope you're doing well.
I have been using a macro for about a year and have not had this issue before, but I recently made a formatting change to the sheet that seems to be causing a bug I can't solve.
Each person has a list of accounts and numbers for each month going across. I need to copy and paste their forecast numbers, and it has to be a paste because I need to carry their comments over to the master file as well as any numbers or formatting changes (for instance, if they make a box red because they want to call out a bad thing in an account).
Each month is also grouped vertically (four columns per month) so we can hide the month when it's over and just see the final number.
What is happening now is when I run this macro, it is randomly copying only visible cells - so, some of the sheets get all messed up. They only see what is filtered in the filtered list running down and only see going across what isn't hidden in the grouped columns. Here is my code:
Columns A and B contain information with every 4th cell being a new account name, and then under it three categories, so it looks like this
COMPANY COMPANY
TOOL
FLUID
AIR
OTHER COMPANY
TOOL
FLUID
AIR
etc. etc.
The macro starts copying at c3 and ends at bs whatever the bottom row is (since people have varying numbers of accounts). The rest of the sheet should be constant, the idea is only to be copying the numbers that people update week to week and cascading the numbers up to a master file that is prepared and sent to the division sales leader.
I was originally using a button-enabled macro to filter the sheet by tool, fluid and air and recently switched to a filtered list. It was after that switch (turning column B into a filtered list) that I started to have this problem, but even then I can't see how that would suddenly also make the vertical column groups an issue.
Hopefully this helps, not sure if I described everything correctly.
Hope you're doing well.
I have been using a macro for about a year and have not had this issue before, but I recently made a formatting change to the sheet that seems to be causing a bug I can't solve.
Each person has a list of accounts and numbers for each month going across. I need to copy and paste their forecast numbers, and it has to be a paste because I need to carry their comments over to the master file as well as any numbers or formatting changes (for instance, if they make a box red because they want to call out a bad thing in an account).
Each month is also grouped vertically (four columns per month) so we can hide the month when it's over and just see the final number.
What is happening now is when I run this macro, it is randomly copying only visible cells - so, some of the sheets get all messed up. They only see what is filtered in the filtered list running down and only see going across what isn't hidden in the grouped columns. Here is my code:
Code:
a.Sheets("Surname").Range("c3:bs38").Copy
o.Sheets("Surname").Range("c3").PasteSpecial Paste:=xlPasteAll
b.Sheets("Surname").Range("c3:bs18").Copy
o.Sheets("Surname").Range("c3").PasteSpecial Paste:=xlPasteAll
c.Sheets("Surname").Range("c3:bs6").Copy
o.Sheets("Surname").Range("c3").PasteSpecial Paste:=xlPasteAll
d.Sheets("Surname").Range("c3:bs22").Copy
o.Sheets("Surname").Range("c3").PasteSpecial Paste:=xlPasteAll
e.Sheets("Surname").Range("c3:bs17").Copy
o.Sheets("Surname").Range("c3").PasteSpecial Paste:=xlPasteAll
f.Sheets("Surname").Range("c3:bs16").Copy
o.Sheets("Surname").Range("c3").PasteSpecial Paste:=xlPasteAll
g.Sheets("Surname").Range("c3:bs6").Copy
o.Sheets("Surname").Range("c3").PasteSpecial Paste:=xlPasteAll
h.Sheets("Surname").Range("c3:bs122").Copy
o.Sheets("Surname").Range("c3").PasteSpecial Paste:=xlPasteAll
i.Sheets("Surname").Range("c3:bs42").Copy
o.Sheets("Surname").Range("c3").PasteSpecial Paste:=xlPasteAll
j.Sheets("Surname").Range("c3:bs42").Copy
o.Sheets("Surname").Range("c3").PasteSpecial Paste:=xlPasteAll
k.Sheets("Surname").Range("c3:bs10").Copy
o.Sheets("Surname").Range("c3").PasteSpecial Paste:=xlPasteAll
l.Sheets("Surname").Range("c3:bs50").Copy
o.Sheets("Surname").Range("c3").PasteSpecial Paste:=xlPasteAll
m.Sheets("Surname").Range("c3:bs30").Copy
o.Sheets("Surname").Range("c3").PasteSpecial Paste:=xlPasteAll
n.Sheets("Surname").Range("c3:bs134").Copy
o.Sheets("Surname").Range("c3").PasteSpecial Paste:=xlPasteAll
p.Sheets("Surname").Range("c3:bs146").Copy
o.Sheets("Surname").Range("c3").PasteSpecial Paste:=xlPasteAll
q.Sheets("Surname").Range("c3:bs58").Copy
o.Sheets("Surname").Range("c3").PasteSpecial Paste:=xlPasteAll
Columns A and B contain information with every 4th cell being a new account name, and then under it three categories, so it looks like this
COMPANY COMPANY
TOOL
FLUID
AIR
OTHER COMPANY
TOOL
FLUID
AIR
etc. etc.
The macro starts copying at c3 and ends at bs whatever the bottom row is (since people have varying numbers of accounts). The rest of the sheet should be constant, the idea is only to be copying the numbers that people update week to week and cascading the numbers up to a master file that is prepared and sent to the division sales leader.
I was originally using a button-enabled macro to filter the sheet by tool, fluid and air and recently switched to a filtered list. It was after that switch (turning column B into a filtered list) that I started to have this problem, but even then I can't see how that would suddenly also make the vertical column groups an issue.
Hopefully this helps, not sure if I described everything correctly.