Copy non-continuous data

  • I've inherited some spreadsheets that are a formatting nightmare. I tried to script away the fills, fonts, colors, borders, and such, but sometimes there are so many different things on the sheet that Excel balks.

    Next I manually copied and pasted the data as values into a new workbook, I can reduce the size by 90%. The problem is there's a lot of data and it's not all continuous (multiple tables on each sheet, different size ranges on each version of the workbook, etc).

    I have a script that creates a new worksheet and brings EVERYTHING over like this:

    1. Sheets(WSn2).Range("a1:cz100000").Value2 = Sheets(WSn).Range("a1:cz100000").Value2

    But if I use that hammer to do it, the workbook is referencing all these cells as having values even though many of them are, in fact, blank. I'm not getting as much size reduction as I would like.

    Is there a better way?

  • This will copy everything in a range from one sheet to one above the other in a second sheet.

    but it may cause problems if there are formulas referencing fixed areas