Copy and pasting Data (on the same sheet) using VBA

[mikeHunt]

Hey Everyone!

I'm having trouble writing code to copy data from a table and paste the important columns under the original to compare the data. I needed to put this in a loop to go through all the sheets of the workbook.. Here is my code.. Any help will be appreciated!

Sub CopyData()

Dim WS_Count As Integer
Dim I As Integer

WS_Count = ActiveWorkbookWorksheets.Count

For I = 1 To WS_Count

ActiveWorkbook.Worksheets(I).Range("A1:F80").Copy
With ActiveSheet.Range("A85")
.PasteSpecial x1PasteFormats
.PasteSpecial x1PasteValue
End With

ActiveWorkbook.Worksheets(I).Range("L1:L80").Copy
With ActiveSheets.Range("G85")
.PasteSpecial x1PasteFormats
.PasteSpecial x1PasteValues
End With

Next I

End Sub



It Is givin me errors saying all merged cells need to be the same size??

 

[JLGWhiz]

vba does not easily lend itself to copy data with merged cells due to the underlying application that merged the cells. If you are copying a range where part of a merged cell is within the specified copy range and part of the merged cell is not withing the specified copy range, vba does not know what to do with the other part of the merged cell and will send you the error message. There are other conditions with merged cells that cause the error to display. It might be easier to unmerge the cells then remerge them to copy your data.

 

[Norie]

As well as JLGWhiz's comments on merged cells the constants x1PasteFormats and x1PasteValue should actually be xlPasteFormats and xlPasteValue.