Copy one worksheet to another from different workbook

[Jeffreyxx01]

Hi guys,

Can someone help me to write a code that copy one worksheet from one workbook to another worksheet in another workbook.
I need to automate a few tasks at work and I cannot make complex macro.

Thanks for your support.

 

[Jeffreyxx01]

It does not work unfortunately, I tried that earlier. :/

 

[Jeffreyxx01]

Also when using AD instead of AE, the second line is replaced with column title.

 

[Fluff]

Ok, how about

    UsdRws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row

 

[Jeffreyxx01]

Works great Fluff, thank you for your help.

 

[Fluff]

Your welcome

 

[Jeffreyxx01]

Hi again Fluff,

there is a problem in the code
When I use the code just by clicking F5, there is no problem but when I make a button, the click copy the title for the second line from AE2:BA2

Can you tell me how to fix it?

Sub OpenFile()   
   Dim Fname As String
   Dim Wbk As Workbook
   Dim Sht As Worksheet
   Dim Usdrws As Long
   
   Set Sht = ActiveWorkbook.Sheets("Pipeline")
   ChDrive "W:"
   ChDir "W:\Insights Team\ALL ACADEMIC\Reporting\Weekly RAM\OTC\Magdalena Spreadsheet"
   Fname = Application.GetOpenFilename(FileFilter:="xls Files (*.xls*), *.xls*", Title:="Select a file", MultiSelect:=False)
   If Fname = "False" Then
      MsgBox "no file selected"
      Exit Sub
   Else
      Set Wbk = Workbooks.Open(Fname)
      With Wbk.Sheets("Registration Enquiry")
         Sht.Range("A2:ad1000").ClearContents
         .Range("A2:ad1000").Copy Sht.Range("A2")
      End With
      Wbk.Close , False
            Usdrws = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
            Sht.Range("AE2:BA" & Usdrws).FillDown
   End If


End Sub

 

[Fluff]

Oops should be

Usdrws = [COLOR=#ff0000]Sht.[/COLOR]Cells.

 

[Jeffreyxx01]

This works great now. I love your stuffs, very good.

 

[Jeffreyxx01]

Hello Fluff,

I need the code to paste the data from where it copies to the new sheet in values. I have written something but it does not copy as values.
Please can you help?

Sub OpenFile()
   
   Dim Fname As String
   Dim Wbk As Workbook
   Dim Sht As Worksheet
   Dim Usdrws As Long
   
   Set Sht = ActiveWorkbook.Sheets("OTC")
   ChDrive "W:"
   ChDir "W:\Insights Team\ALL ACADEMIC\Reporting\Weekly RAM\OTC\Weekly RAM"
   Fname = Application.GetOpenFilename(FileFilter:="xls Files (*.xls*), *.xls*", Title:="Select a file", MultiSelect:=False)
   If Fname = "False" Then
      MsgBox "no file selected"
      Exit Sub
   Else
      Set Wbk = Workbooks.Open(Fname)
      With Wbk.Sheets("January'18 Summary")
         Sht.Range("m41:q54").ClearContents
         .Range("l5:p18").Copy Sht.Range("m41")
         .PasteSpecial xlPasteValues
         Application.CutCopyMode = False
      End With
        Application.DisplayAlerts = False
        Wbk.Close , False
        
   End If


End Sub

 

[Fluff]

Try

   .Range("l5:p18").Copy
   Sht.Range("m41").PasteSpecial xlPasteValues