copypaste from workbook to another

[enzzz88]

Hi guys,

i am a newbie to vba and i am trying to copy several worksheets from a closed file into my current open one.

the code is the following


Sub Import()


'copy data from closed workbook to active workbook


Dim xlApp As Application
Dim xlBook As Workbook
Dim wb1 As Workbook
Dim Sh As Object
Dim report As String


Set xlApp = CreateObject("Excel.Application")
'Path source workbook

Application.FileDialog(msoFileDialogFilePicker).Show
report = Application.FileDialog(msoFileDialogFilePicker).SelectedItems(1)


Set xlBook = xlApp.Workbooks.Open(report)

xlBook.Worksheets("WC_holiday_provision").UsedRange.Copy
xlApp.DisplayAlerts = False
Set wb1 = Workbooks("CZ_Payroll_Macro.xlsb")
Set Sh = wb1.Worksheets("WC_holiday_provision")
Sh.Activate
wb1.Worksheets("WC_holiday_provision").Range("A1").Select
Sh.Paste


Now, i would like to get rid of the SH.ACTIVATE and SH.PASTE and paste directly withouth having to activate every sheet window, any ideas?

thanks a lot

 

[JackDanIce]

And two more corrections:

Private Function LastRow(ByRef wks As Worksheet) As Long


    With wks
        LastRow = .Cells.Find("*", .Cells(1, 1), xlFormulas, xlPart, xlByRows, xlPrevious).Row
    End With
    
End Function


Private Function LastCol(ByRef wks As Worksheet) As Long


    With wks
        LastCol = .Cells.Find("*", .Cells(1, 1), xlFormulas, xlPart, xlByColumns, xlPrevious).Column
    End With
    
End Function