Macro unexpectedly pasting only visible cells

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:

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.
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand

Forum statistics

Threads
1,223,911
Messages
6,175,327
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top