Copy File Macro - Prevent from seeing "Chunking Along"

The attached project / copy file macro performs as needed but the user will see as it copies each row. Reminds me of a "chunking along" action.

Is there someway to keep it from doing that ?

Requires an empty workbook named "Backup.xlsx"

Thank you ahead of time !

Never mind. I answered my own questions. Screenupdating = False.

It's been a long day staring at the screen.

Thank you !

Norie:

The syntax hangs on:

Please Login or Register  to view this content.

Error code: Object required.

Looks like the object is there already ?

Is there a way to copy and paste the formatting of the cells being copied as well ?

I've tried (in my code):

Please Login or Register  to view this content.

Logit,

You realize you are not copying column Q, right ?

If you're going to copy all rows that aren't blank in column A, have you considered using a filter rather than looping ?

NoSparks:

I'm not familiar with "filter", other than the Menu Bar tool selection. Please explain.

Re: Leaving out "Q" ... thanks. Another typo.

something along the lines of this will do the copy in one shot

Please Login or Register  to view this content.

NoSparks

Works great.

Is there a means of maintaining the same format of the cells as they are copied over to the backup workbook ?

See #7 above for my previous feeble attempt.

.PasteSpecial xlPasteAll should have looked after that.

For me it copied different fonts and cell colors.

Within the macro, put your cursor somewhere in PasteSpecial and hit F1,
On the help page that comes up click on xlPasteType.

This worked ... do you see anything that should be changed ?

Please Login or Register  to view this content.

That looks OK to me but I think only experimentation will tell you for sure.

After a little further testing I did find that xlPasteAll didn't actually paste all, just most things.
Needed the xlPasteColumnWidths to be implemented first, as you no doubt also discovered.

The cells(1,1).select is just used so as to not leave the sheet with the entire copy area selected (I hate opening up a sheet to find a massive range selected).

Thanks friend. Sent you a private message.