Help copy sheet to closed workbook

[chichaspowa]

Good afternoon,

Was wondering if someone could help me out with a piece of code.

What i need to do is when closing the workbook i would like to copy the sheet called "registos",

and paste it on the sheet "registos" in the closed workbook ... \\server\server\Dest1.xls...


thanks

 

[patel45]

not tested

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Set wb = ThisWorkbook
Set wb1 = Workbooks.Open("\\server\server\Dest1.xls")
wb1.Sheets("registos").Delete
wb.Sheets("registos").Copy after:=wb1.Sheets(Sheets.Count)
wb1.Close True
End Sub

 

[chichaspowa]

getting runtime error 9, out of range on this line ........... wb.Sheets("registos").Copy after:=wb1.Sheets(Sheets.Count)

funny enough i had tried that code already, it works fine if i wanna fetch data from the closed file and not paste into the closed file.

I use it to fetch data on open book event.

Private Sub Workbook_Open()





Dim ws As Worksheet
Dim targetWorkbook As Workbook, wb As Workbook



Set targetWorkbook = Application.ActiveWorkbook



Application.DisplayAlerts = False


Set wb = Workbooks.Open("\\server\server\Dest1.xls")


targetWorkbook.Sheets("Registos").Delete


wb.Sheets("Registos").Copy after:=targetWorkbook.Sheets(targetWorkbook.Sheets.Count)

wb.Close


main.Show




End Sub

 

[chichaspowa]

i got the help i needed thanks for your trouble patel45

 

[patel45]

can you show to the forum how you solved ?