How Do I Retain Table Formatting (Cell Width) When Copying Sheet Information?

[kitchenspoon]

Hi All, (bit of a lurker me).

So ive been building a new spreadsheet that looks into column G, sees if there is a Yes or No and populates a new sheet(s). It all works..fantastic.

However The formatting is all over the place. Id like for it to maintain the formatting (cell size) primarily.

My code is

Please Login or Register  to view this content.

Any ideas?

My code/macro works perfectly, just not copying over the cell size meaning the table that is produced crops alot of information, requiring the user to resize it EVERY TIME

[dflak]

It looks like your range is in columns A to G and it looks like you copy the information to the last worksheet in the workbook.

Put the following code at the end of the loop before Next i

Please Login or Register  to view this content.

[kitchenspoon]

It looks like your range is in columns A to G and it looks like you copy the information to the last worksheet in the workbook.

Put the following code at the end of the loop before Next i

Please Login or Register  to view this content.

Hi,

Thank you for the response, however when adding them two lines of code in i get a "Run-time error'1004'" Error. It's worth noting that the Range has already been defined in the line

Please Login or Register  to view this content.

So i removed the top line of your code, and added just

Please Login or Register  to view this content.

However, with this i get a "PasteSpecial method of Worksheet class failed" error (also Run-time 1004).

Any ideas?

I have provided a dropbox link to download the excel file. The Module in VB to look at it is Module 1 through to 13. Each module does the same action on each sheet, and compiles it into 2 x Yes/No Sheets. The Call macro allows for a button on the Helvar Snags sheet to auto compile, auto save to pdf etc..


Link: https://www.dropbox.com/s/cbtpv9sxv1...heet.xlsm?dl=0
(this link expires in 7 days)

[kitchenspoon]

If anybody can help with this, they will receive one humorous picture of my cat.

[romperstomper]

It's quite inefficient to repeat the same code for every sheet. Just use one routine that takes a sheet as an argument:

Please Login or Register  to view this content.

Then all you need for each sheet is:

Please Login or Register  to view this content.

changing the sheet name as appropriate.

See attached.