Copy sheet to existing worksheet in a closed workbook

[cuchi33]

I have two workbook: 1 and 2, 1 is open, 2 is closed, wb 2 has 3 sheets: A, B, C,
I copy data in sheet 1 from wb 1 to newsheet in wb 2 and rename new created sheet to D. now , i have 4 sheet: A, B,C, D with D has values from sheet 1 of wb1.
i created the code below:

Public Sub CPWS()
Dim fileName
Dim closedBook As Workbook
Dim currentSheet As Worksheet

fileName = Application.GetOpenFilename("Excel Files (*.xlsm), *.xlsm")

If fileName <> False Then
Application.ScreenUpdating = False

Set currentSheet = Application.ActiveSheet
Set closedBook = Workbooks.Open(fileName)
currentSheet.Copy After:=closedBook.Sheets("3")
closedBook.Sheets(4).Name = "D"
Application.ScreenUpdating = True
End If
End Sub
the code works well!

if wb 2 already had A,B,C,D
how could i copy data from sheet 1 of wb 1 to sheet D of wb2? ( copy sheet to existing worksheet in a closed workbook)

 

[t0ny84]

The below code should open Workbook 2 in the background copy Sheet 1 from Workbook 1 to Workbook 2 then if a Sheet named D already exists it will ask you to enter a new name for this sheet, if no Sheet D then it would just name the sheet Sheet D. Workbook 2 would then close with a prompt asking if you want to save.

Sub CopySheet()
Dim wb2 As Workbook
Dim SheetDExists as String

Application.ScreenUpdating = False '<-- This stops changes showing on screen whilst code is running.
Set wb2 = Workbooks.Open("C:\Folder Name\WorkBook2.xlsm")    '<---- change to the file location of Workbook 2, This will open the Workbook 2 in the background.
ThisWorkbook.Sheets("Sheet1") After:=wb2.Sheets(Sheets.Count) '<--- Put the sheet at the end of all other sheets in Workbook 2

If wb2.Sheets("D") = True Then '<- If Sheet D exists in Workbook 2 you will be asked to enter a new name for the sheet.
SheetDExists = InputBox("Sheet D - Already Exists - Enter New Number") 
wb2.ActiveSheet.Name = SheetDExists <--- Renames based on entered into InputBox
Else 
wb2.ActiveSheet.Name = "D"  ' <- If Sheet D doesn't exist then will name it D
    wb2.Close '<-- This will close Workbook 2 and prompt you to save the changes, to have this auto save you would need to review [B][I]Workbook.Close [/I][/B]
Application.ScreenUpdating = True
End Sub

 

[cuchi33]

Compile error:
Block if without End If
then i add end if
'
Application.ScreenUpdating = True
End if
End Sub
there's still a compile errror

 

[cuchi33]

i meant i want to clear sheet D of wb2, then copy sheet 1 of wb1 to sheet D of wb2. ( copy and overwrite).

 

[t0ny84]

Sorry about that, I believe the issue with that is the Application.ScreenUpdating True needs to be between the End If and End Sub.

Below should work, just need to update the sheet names and change the location of the WorkbookB. Without the Application.ScreenUpdating Excel will look like its frozen whilst running.

Sub CopySheet()
Dim wbA As Workbook
Dim wbB As Workbook

Application.ScreenUpdating = False

Set wbA = ThisWorkbook '<= WorkbookA
Set wbB = Workbooks.Open("C:\Excel\WorkbookB")    '<---- change to the file location of Workbook 2, This will open the Workbook 2 in the background

wbA.Worksheets("Sheet1").Activate ' <=dont think required
wbA.Sheets("Sheet1").Columns("A:Z").Select ' <=dont think required

wbB.Worksheets("SheetD").Activate 
wbB.Sheets("SheetD").Columns("A:Z").Select
wbB.Sheets("SheetD").Columns("A:Z").Clear

wbA.Sheets("Sheet1").Columns("A:Z").Copy _
wbB.Sheets("SheetD").Columns("A:Z")

Application.ScreenUpdating = True

End Sub

 

[cuchi33]

Dear Tony
your code works!
Thank you!