VBA code Copy and replace sheet

aisietie · Feb 26th 2011

Hello,

The code I use copies "Blad6" of workbook Names, and replce the page"Blad6" in Workbook "Form_transfertest".
The problem is that the code insert a new page, I want that the old sheet "Blad6" in the workbook Form_transfertest is replaced by the copied one from workbook Names.
The code:

Code

Sub copy()


Workbooks.Open Filename:= _
        "[URL="file://\\Post.bpgnet.net\fs\NSCGNT\GENTX\Unit\PLANNING\Planning&Performance_Control\Formtransfer\Form_transfertest.xls"]\Form_transfertest.xls[/URL]"
        Application.EnableEvents = False
    Windows("Names.xls").Activate
    Sheets("Blad6").Select
    Sheets("Blad6").copy Before:=Workbooks("Form_transfertest.xls").Sheets("Blad6")
    Application.EnableEvents = True
    Sheets("Formulier").Select
    ActiveWorkbook.Save
    ActiveWindow.Close
    End Sub

Display More

Greetings

aisietie

**S M C** · Feb 27th 2011

Re: VBA code Copy and replace sheet

Try

Code

Sub copy()


    Workbooks.Open "\Form_transfertest.xls"
    Application.EnableEvents = False
    Workbooks("Form_transfertest.xls").Sheets("Blad6").UsedRange.Clear
    Workbooks("Names.xls").Sheets("Blad6").UsedRange.copy _
        Workbooks("Form_transfertest.xls").Sheets("Blad6").Range(Workbooks("Names.xls").Sheets("Blad6").UsedRange.Cells(1).Address)
    Application.EnableEvents = True
    Sheets("Formulier").Select
    Workbooks("Form_transfertest.xls").Close True
    
End Sub

Display More

aisietie · Feb 27th 2011

Re: VBA code Copy and replace sheet

SMC,

That is 100% OK.

Thanks

aisietie

snb · Feb 27th 2011

Re: VBA code Copy and replace sheet

This should suffice:

Code

Sub copy()
  Application.EnableEvents = False


  With Workbooks.Open( "\Form_transfertest.xls").Sheets("Blad6")
    .UsedRange.Clear
    Workbooks("Names.xls").Sheets("Blad6").UsedRange.copy .cells(1)
    .parent.Close True
  End With  


  Application.EnableEvents = True
End Sub

Display More

VBA code Copy and replace sheet

Participate now!

Share