Copy values from one workbook to another with merged cells

Hi,

I have a little problem with a code i'm using.

I have 6 workbooks and one main workbook.
The 6 workbooks are filled in by 6 different people, this is nto a problem.
The main workbook is opened when a meeting starts, and with a click on a button all the info from the 6 other workbooks are put in the main sheet. This works pretty good, but with a few flaws.

The cells that people fill in those 6 workbooks are merged cell ( for example G5:AA5 ), and that data is being copied to the main workbook also in merged cells ( for example G32:AA32 )
All goes wel as long people stay within the width of the merge cells, if they go outside the width of the merge cells i get an error in the code. That happens also when they use ALT+Enter.

I know working with merged cells is not the best option within excel, but can't yet find a different solution.

The fix i like to have : copy the data in the main workbook, in the merged cells, without problems if the cell value is bigger then the merged cells.
Hope somebody can help me.

The codes I use are also found on boards, so credits goes to those people.

The code for collecting the data:

Option Private Module


Private Function GetValue(Path, File, Sheet, ref)
'Deze functie haalt data op uit een gesloten of geopend werkboek




Dim arg As String
    If Right(Path, 1) <> "\" Then Path = Path & "\"
        If Dir(Path & File) = "" Then
                GetValue = "File not found"
            Exit Function
    End If
            arg = "'" & Path & "[" & File & "]" & Sheet & "'!" & _
            Range(ref).Range("a1").Address(, , xlR1C1)
    GetValue = ExecuteExcel4Macro(arg)
End Function

The code in a module to get the data from one workbook: the row giving a problem is commented

Public Sub OphalenTekst_wg1()Dim Path As String, File As String, Sheet As String, TargetSheet As String, CheckValue As String, a As String
Dim i As Integer
Dim SourceArrays As Variant
Dim TargetArrays As Variant




Path = "Q:\Test\Meeting"
File = "WG1.xls"
Sheet = "WG1"
TargetSheet = "Meeting"
SourceArrays = Array("G2", "M2", "C5", "G5", "G7", "C10", "C11", "C12", "D10", "D11", "D12", "E10", "E11", "E12", "E13", "F10", "F11", "F12", "G10", "G11", "G12", "G13", "D14" _
    , "B18", "B19", "C18", "C19", "E18", "E19", "G18", "G19", "H18", "H19", "J18", "J19", "L18", "L19", "M18", "M19", "O18", "O19", "Q18", "Q19", "R18", "R19", "T18", "T19", "V18")
                    
TargetArrays = Array("N29", "A37", "C32", "G32", "G33", "C34", "C35", "C36", "D34", "D35", "D36", "E34", "E35", "E36", "E37", "F34", "F35", "F36", "G34", "G35", "G36", "G37", "B38" _
    , "B40", "B41", "C40", "C41", "E40", "E41", "G40", "G41", "H40", "H41", "J40", "J41", "L40", "L41", "M40", "M41", "O40", "O41", "Q40", "Q41", "R40", "R41", "T40", "T41", "V40")
Worksheets(TargetSheet).Select
            
            For i = LBound(SourceArrays) To UBound(TargetArrays)
                a = Range(SourceArrays(i)).Address
               CheckValue = GetValue(Path, File, Sheet, a) '*****ERROR ON THIS LINE******
               If Len(CheckValue) = 1 And CheckValue = "0" Then CheckValue = Empty
                Range(TargetArrays(i)).Value = CheckValue
            Next i
 
End Sub

Thanks for the assist.

Re: Copy values from one workbook to another with merged cells

KjBox
just tried it, but that doesn't work. Still get the "runtime error 13 - Type mismatch". It also doesn't makes the row heiger to fit the text, its stays at the same height.

Re: Copy values from one workbook to another with merged cells

KjBox
as requested
The main.xls file is where I collect the data, the test file is where I put the data in

forum.ozgrid.com/index.php?attachment/70905/ forum.ozgrid.com/index.php?attachment/70906/

Re: Copy values from one workbook to another with merged cells

jolivanes
Thanks for a possible solution, but where do I put this code in. Will that be instead of the arrays I use or is this a extra piece of code that I need.

Re: Copy values from one workbook to another with merged cells

jolivanes
sorry for the late response, just came back from a little trip.
But that looks indeed in what i was looking for, thank you so much.
I will try to adapt it to my worksheets, and hopefully my problems are gone.
If not, I will be back :wink:

Re: Copy values from one workbook to another with merged cells

jolivanes
I mixed the codes ( the one I aleady was using, and the one you provided) and it got the whole thing working. Again thank you for your help.
Happy holidays