I want to copy a worksheet from workbook "S.xls" and pase it into Worksheet
of Workbook "D.xls" .I am trying to do something like this,also i can't pass
Workbook Byref.

Sub run_copysheet()
Dim Source, Dest As Workbook

Set Source = Application.Workbooks.Open("c:\S.xls")
Set Dest = Application.Workbooks.Open("c:\D.xls")

Call copysheet(Source, "Sheet1", Source, "Sheet1")

End Sub


Function copysheet(ByVal S1 As Workbook, ByVal SSheetName As String, ByVal
D1 As Workbook, ByVal DSheetName As String)

S1.Worksheets(SSheetName).Activate
Rows("1:30").Select
Selection.Copy

Set D1 = ActiveWorkbook
D1.Worksheets(DSheetName).Activate
Cells.Select
ActiveSheet.Paste


End Function

Can you not use something more like:

Workbooks("S.xls").Sheets("Sheet1").Copy
After:=Workbooks("D.xls").Sheets(1)

-seems a lot briefer

J


On 20 Jun, 10:18, kris <k...@discussions.microsoft.com> wrote:
> I want to copy a worksheet from workbook "S.xls" and pase it into Worksheet
> of Workbook "D.xls" .I am trying to do something like this,also i can't pass
> Workbook Byref.
>
> Sub run_copysheet()
> Dim Source, Dest As Workbook
>
> Set Source = Application.Workbooks.Open("c:\S.xls")
> Set Dest = Application.Workbooks.Open("c:\D.xls")
>
> Call copysheet(Source, "Sheet1", Source, "Sheet1")
>
> End Sub
>
> Function copysheet(ByVal S1 As Workbook, ByVal SSheetName As String, ByVal
> D1 As Workbook, ByVal DSheetName As String)
>
> S1.Worksheets(SSheetName).Activate
> Rows("1:30").Select
> Selection.Copy
>
> Set D1 = ActiveWorkbook
> D1.Worksheets(DSheetName).Activate
> Cells.Select
> ActiveSheet.Paste
>
> End Function

I found two problems. One you are passing "source" twice to the function
copysheet instead passing source and dest. Second you are redefining D1 in
the functtion copysheet. I've commented this statement in the code.

There is no problems with the pasing of parameters (worksheet).



Sub run_copysheet()
Dim Source, Dest As Workbook

Set Source = Application.Workbooks.Open("c:\temp\S.xls")
Set Dest = Application.Workbooks.Open("c:\temp\D.xls")

Call copysheet(Source, "Sheet1", Dest, "Sheet1")

End Sub


Function copysheet(ByVal S1 As Workbook, _
ByVal SSheetName As String, ByVal _
D1 As Workbook, ByVal DSheetName As String)

S1.Worksheets(SSheetName).Activate
Rows("1:30").Select
Selection.Copy

'Set D1 = ActiveWorkbook
D1.Worksheets(DSheetName).Activate
Cells.Select
ActiveSheet.Paste


End Function


"kris" wrote:

> I want to copy a worksheet from workbook "S.xls" and pase it into Worksheet
> of Workbook "D.xls" .I am trying to do something like this,also i can't pass
> Workbook Byref.
>
> Sub run_copysheet()
> Dim Source, Dest As Workbook
>
> Set Source = Application.Workbooks.Open("c:\S.xls")
> Set Dest = Application.Workbooks.Open("c:\D.xls")
>
> Call copysheet(Source, "Sheet1", Source, "Sheet1")
>
> End Sub
>
>
> Function copysheet(ByVal S1 As Workbook, ByVal SSheetName As String, ByVal
> D1 As Workbook, ByVal DSheetName As String)
>
> S1.Worksheets(SSheetName).Activate
> Rows("1:30").Select
> Selection.Copy
>
> Set D1 = ActiveWorkbook
> D1.Worksheets(DSheetName).Activate
> Cells.Select
> ActiveSheet.Paste
>
>
> End Function
>

Thanks Joel its now working