Thread: HELP with error 1004, Please.

Ok, Im trying to copy some data from closed workbook to the current active woorkbook. I get to the point where code is reading data from closed workbook and can paste it into sheet2 in that workbook. This is my new code:

Sub Copy456()
    
        Dim iCol As Long
        Dim iSht As Long
        Dim i As Long
        
          
                 
        Workbooks.Open ("run1.xlsx")
        
        For i = 1 To Worksheets.Count  ' Loop through worksheets
            Worksheets(i).Activate
        
         ' Loop through columns
         For iSht = 1 To 6 ' no of sheets
         For iCol = 1 To 6 ' no of columns
            
            With Worksheets(i).Columns(iCol)
                
                If ((.Cells(1, 1).Value = "Time")) Then ' if first cell=Time then copy two columns
                    Range(.Cells(1, 2), .End(xlDown)).Select
                    Selection.Copy Destination:=Workbooks("Your Idea.xlsm").Worksheets("Sheet2").Columns((i + 1) + i).Cells(2, 1)
                    Worksheets("Sheet2").Cells(i * 2 + 1) = Worksheets(i).Name
                Else
                    ' do nothing
                    
                End If
            End With
            
        Next iCol
        Next iSht
    Next i
    End Sub

But once I change that part of code:

Selection.Copy Destination:=Worksheets("Sheet2").Columns((i + 1) + i).Cells(2, 1)

into that code:

Destination:=Workbooks("general.xlsx").Worksheets("Sheet2").Columns((i + 1) + i).Cells(2, 1)

It stop working issuing error: "subscription is out of range".
File general.xlsx is an empty file which is closed as well.

When I change code into:

`Selection.Copy Destination:=Workbooks("Your Idea.xlsm").Worksheets("Sheet2").Columns((i + 1) + i).Cells(2, 1)

It then issue an error: "1004 cannot change part of merged cell".
File "Your Idea.xlsm" is the file from which I running this script.

Any help with this problem?

You cannot paste to a closed file. And get rid of merged cells - they are a pain as you can see.