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:
Hope you can help.
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:
- 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.
- Leave a blank column between each 6 column block of data.
- Copy next block of data by 43 rows and paste special to cell “J8”
- Leave a blank column between each 6 column block of data.
- 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.
- Please provide an error-trap to accommodate for when there is no more data to copy.
Hope you can help.