Hi All,

I am using Excel 2007 for Windows.

I would like to use VBA code to copy text, numeric values and formulae to another worksheet in the same workbook. The data spans 6 columns and many rows, starting in cell C8 on Sheet1.

Scenario:

I would like to retain the original formatting but convert the formulae to values; i.e. Paste Special: column widths, formats, values and number formats. The data is to be copied in blocks of 43 rows until there are no more rows to copy. The 6 columns of data are to be pasted to Sheet3, starting in cell C8 (C-H), then a blank column should be left between each 6 column block of data containing 43 rows (or less).


Desired Result:
  1. Copy 6 columns of data by 43 rows from Sheet1 cell C8, paste to Sheet3 cell C8 using Paste Special: column widths, formats, values and number formats.
  2. Leave a blank column between each 6 column block of data.
  3. Copy next block of data by 43 rows and paste special to cell J8
  4. Leave a blank column between each 6 column block of data.
  5. Copy next block of data by 43 rows and paste special to cell Q8, and so on until there is no more data to copy from sheet1.
  6. Please provide an error-trap to accommodate for when there is no more data to copy.


Hope you can help.