mike637
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").S elect
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
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").S
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hi imnorie,
I think the 2nd recommendation will work best since it is a lot quicker.
Thanks,
Michael
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.:)
It certainly is, I thought Excel had crashed the looping version was taking so long to run.:)
Open in new window
If it is, replace it with this.
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.