Copy and Paste Blocks of Data

Sam

New member
Joined
Apr 3, 2014
Messages
36
Reaction score
0
Points
0
Excel Version(s)
2007
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.
 
Top