Reputation: 11
I am hoping someone can help me.
I have two workbooks. Wb1 = FAC Trial Wb2 = ROMAN
There are numerous worksheets within both workbooks. What I would like to happen is that the data in Wb2, worksheet named Format is copied into Wb1 worksheet named Format.
The worksheet in Wb1 is already named and is to be updated on a daily basis from Wb2. The data in Wb1 worksheet Format would be overwritten each time. I was looking that this would happen at the click of a Command Button. Wb2 would not be open at the time of clicking the Command Button.
I have found some code but it creates a new worksheet each time the code is run and I am unsure of how to change it to suit my needs.
thank you in advance any help is greatly appreciated
Upvotes: 1
Views: 13566
Reputation: 1
Sub ReplaceSheets(wb1 As Workbook, wb2 As Workbook, SheetName As String)
wb1.Sheets(SheetName).Cells.Clear
wb2.Sheets(SheetName).Cells.Copy wb1.Sheets(SheetName).Range("A1")
End Sub
Upvotes: 0
Reputation: 949
This answer is based on @Ituner before
Sub wsCopy()
Dim wb1 As Workbook, wb2 As Workbook, ws1Format As Worksheet
Application.ScreenUpdating = False
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open("[ENTER FILE PATH]\ROMAN.xlsx")
Set ws1Format = wb1.Sheets("Format")
Set ws2Format = wb2.Sheets("Format")
'' Copy the cells of the "Format" worksheet.
ws2Format.Cells.Copy
'' Paste cells to the sheet "Format".
wb1.Sheets("Format").Paste
wb2.Close false 'remove false if you want to be asked if the workbook shall be saved.
Application.ScreenUpdating = True
End Sub
Upvotes: 1
Reputation: 11
The code I was using was
Private Sub CommandButton1_Click ()
With Workbooks ("ROMAN.xls") .Sheets ("Format").Copy_ Before:=Workbooks ("FAC Trial.xls").Sheets ("Format")
End With
End Sub
As is obvious I had to have both workbooks open but I just wanted it to copy the data onto the format worksheet without creating a new one
Upvotes: 0
Reputation: 2985
You can easily record a piece of code to do this yourself, simply use 'Record Macro' on the Developer ribbon.
For now, below should do what you're after (remember to update the filepath for workbook 2).
I have just copied the "Format" worksheet from the ROMAN workbook, then deleted the existing "Format" worksheet and re-named the new one to "Format".
Sub wsCopy()
Dim wb1 As Workbook, wb2 As Workbook, ws1Format As Worksheet
Application.ScreenUpdating = False
Set wb1 = ThisWorkbook
Set wb2 = Workbooks.Open("[ENTER FILE PATH]\ROMAN.xlsx")
Set ws1Format = wb1.Sheets("Format")
Set ws2Format = wb2.Sheets("Format")
'' Copy the "Format" worksheet to wb1.
ws2Format.Copy Before:=ws1Format
wb2.Close
'' Delete existing "Format" worksheet.
Application.DisplayAlerts = False
ws1Format.Delete
Application.DisplayAlerts = True
'' Rename new sheet to "Format".
wb1.Sheets("Format (2)").Name = "Format"
Application.ScreenUpdating = True
End Sub
Upvotes: 2