Link to home
Start Free TrialLog in
Avatar of mike637
mike637Flag for United States of America

asked on

excel vba copy range to another workbook

Hello Experts,

I have this code to copy a range in one workbook and copy it to another workbook after I unmerge the cells.  I have tried numerous things but can not seem to get the code to work without errors.  I do not know what I am doing wrong.  Right now with the last change I made - it errors out when it goes to copy the first cell in workbook2.

Can you please evaluate the following code and see if you can identify and correct what my fried-brain can not correct?

Sub copyimport()

    Dim rng As Range
    Dim wb1 As Workbook, wb2 As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim i As Long, j As Long
   
    Application.EnableEvents = False
   
    'On Error GoTo oops
    Set wb1 = ThisWorkbook
    Set ws1 = wb1.ActiveSheet
       
    Windows(2).Activate
    Set wb2 = ActiveWorkbook
   
    wb2.Worksheets("import").Select

    Set ws2 = wb2.Worksheets("import")
    Set rng = ws2.Range("Y46:AN59")
               
    For Each cel In rng
        If cel.MergeCells Then
        cel.UnMerge
    End If
    Next cel
     
    wb2.Activate
   
    For i = 2 To 16
        For j = 1 To 16
               
                wb2.ws2.Range(Cells(i, j)).Copy
                wb1.ws1.Range(Cells(i + 44, j + 25)).PasteSpecial Paste:=xlPasteValues, _                   Operation:=xlNone, SkipBlanks:=False, Transpose:=False
             
            Next j

    Next i
   
    Application.EnableEvents = True

    Set wb1 = Nothing
    Set wb2 = Nothing
    Set ws1 = Nothing
    Set ws2 = Nothing

   
    Exit Sub

oops:
MsgBox "Mucho Problemo"
Set wb1 = Nothing
Set wb2 = Nothing
Set ws1 = Nothing
Set ws2 = Nothing
Exit Sub
   
       
End Sub
Avatar of Norie
Norie

I think the problem is here.
          wb2.ws2.Range(Cells(i, j)).Copy
                wb1.ws1.Range(Cells(i + 44, j + 25)).PasteSpecial Paste:=xlPasteValues, _                   Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Open in new window


If it is, replace it with this.
ws2.Cells(i, j).Copy
ws1.Cells(i + 44, j + 25).PasteSpecial Paste:=xlPasteValues, _
                             Operation:=xlNone, SkipBlanks:=False, Transpose:=False

Open in new window


ws1 and ws2 already refer to worksheets so you they don't need workbook references.

If you only have one cell, for example Cells(i, j) or Cells(i+44, J+25, you don't need Range.
ASKER CERTIFIED SOLUTION
Avatar of Norie
Norie

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of mike637

ASKER

Hi imnorie,

I think the 2nd recommendation will work best since it is a lot quicker.

Thanks,
Michael
Michael

It certainly is, I thought Excel had crashed the looping version was taking so long to run.:)