Copy worksheet from one workbook to another workbook with new worksheet name

[sshaffer99]

Hello,

I am looking for help writing vbcode to do the following:

I would like to copy a worksheet called "LOE" from a workbook called "Targeting.xls" into my active workbook (which is called Rollup.xls) as a new worksheet that I would likek to call "Targeting LOE"

I.E.
Workbook1: "Targeting" ==> contains worksheet called "LOE"
copy to
Workbook2: "Rollup" ==> worksheet "Targeting LOE"

I have the following set up if it will help:
Dim wbCopyTo As Workbook
Dim wsCopyTo As Worksheet
Dim wbCopyFrom As Workbook
Dim wsCopyFrom As Worksheet

Please let me know if you can help. Thanks.

 

[AlphaFrog]

Sub Copy_LOE()

    With Workbooks("Rollup.xls")
        Workbooks("Targeting.xls").Sheets("LOE").Copy After:=.Sheets(.Sheets.Count)
        .Sheets(.Sheets.Count).Name = "Targeting LOE"
    End With
    
End Sub

 

[sshaffer99]

Thanks for the reply. I am still having some trouble. Below is my original code which works fine. The code between the ********** is the code I tried to add to copy the worksheet called "Development LOE" from a workbook called Targeting.xls to the current workbook (Rollup.xls), but I am getting the following error code and description: "9 - Subscript out of Range" on the line highlighted in red below.

The value of vPlatform is "Targeting". Prior to the code between the astericks, this code worked fine. Any idea what I am doing wrong to get the worksheet copied?

Sub ImportPlatformData(vPlatform)
Dim rn As Variant
Dim wbCopyTo As Workbook
Dim wsCopyTo As Worksheet
Dim wbCopyFrom As Workbook
Dim wsCopyFrom As Worksheet

Set wbCopyTo = ActiveWorkbook
Set wsCopyTo = ActiveSheet

vFile = Range("z1").Value & "\" & vPlatform & ".xls"
On Error GoTo ImportPlatformData_Exit

Set wbCopyFrom = Workbooks.Open(vFile)
Set wsCopyFrom = wbCopyFrom.Worksheets("Development LOE")

'--------------------------------------------------------------
'Copy Range
rn = "Quelle" & vPlatform
wsCopyFrom.Range("QuelleEntry").Copy
wsCopyTo.Range(rn).PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False

'Copy Worksheet
On Error GoTo Error_Exit
'**************** New Code from MrExcel *******************
With Workbooks("Rollup.xls")
Workbooks("Targeting.xls").Sheets("Development LOE").Copy After:=.Sheets(.Sheets.Count)
.Sheets(.Sheets.Count).Name = "Targeting LOE"
End With
'************************ New Code from MrExcel ***********

'Close file that was opened
wbCopyFrom.Close SaveChanges:=False
GoTo ImportPlatformData_Exit
Error_Exit:
MsgBox (Err.Number & " " & Err.Description)
GoTo ImportPlatformData_Exit
ImportPlatformData_Exit:
End Sub

 

[AlphaFrog]

Is Rollup.xls the workbook that has the macro code? If Yes, then try this...

With ThisWorkbook

Or this if Rollup.xls was the active workbook when you started the macro

With wbCopyTo

 

[sshaffer99]

That worked perfect. Thank you!

But, it presented another problem. When I run the code twice, it obvously doesn't work becaue there is already a worksheet with the same name (In this case "Targeting LOE"). Is there a way to say "replace" or overlay the worksheet when I try to give it a name that it already has?

 

[AlphaFrog]

This deletes sheet "Targeting LOE" if it already exists before copying a new one

[COLOR="Green"]    '**************** New Code from MrExcel *******************[/COLOR]
    With ThisWorkbook
[COLOR="Red"]        On Error Resume Next
        Application.DisplayAlerts = False
        .Sheets("Targeting LOE").Delete
        Application.DisplayAlerts = True
        On Error GoTo Error_Exit[/COLOR]
        Workbooks("Targeting.xls").Sheets("Development LOE").Copy After:=.Sheets(.Sheets.Count)
        .Sheets(.Sheets.Count).Name = "Targeting LOE"
    End With
   [COLOR="Green"] '**************** New Code from MrExcel *******************[/COLOR]