CODEWITHSUNDEEP
vbacells
GinKas
GinKas

Reputation: 5

when executing macros/vba how can they be applied on merged cells?

I have the following question: A member from this forum yesterday provided a very good solution to my problem. I have two similar excel workbooks with more than 100 sheets in each, with the same sheet names. I wanted VBA to copy the data from sheet A in workbook A to sheet B in workbook B.

Sub GetDate()

Dim workbookA As Workbook, workbookB As Workbook, sheetA As Worksheet, sheetB As        Worksheet, ws As Worksheet, sheetName As String
Set workbookA = Workbooks("clients.xls")
Set workbookB = Workbooks("KYC.xls")

'Loop through each worksheet in Book A
For Each ws In workbookB.Worksheets
    Set sheetA = workbookA.Worksheets(ws.Name)
    Set sheetB = workbookB.Worksheets(ws.Name)
    sheetA.Range("C5").Copy sheetB.Range("E31")
Next
End Sub

I want to use it agan to get another values from another cell and insert it on workbookB on the approprate sheet each time. The problem is that on workbook A the cell I want to copy is a merged cell and so is the destination cell. I tried to give a name to each of the merged cells but I can't seem to make it. Can someone help if it can be solved?

thank you

Upvotes: 0

Views: 1134

Answers (1)

rex
rex

Reputation: 3183

Try

sheetA.Range("C5").MergeArea.Copy    ' copies the merged cells that include C5
sheetB.Range("E31").PasteSpecial     ' pastes what was copied into E31 and any merged cells it is part of

Upvotes: 1

Related Questions