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)
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)
Last edited: