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.

 

[Fluff]

OK, try this

Sub OpenFile()
   
   Dim Fname As String
   Dim Wbk As Workbook
   Dim Sht As Worksheet
   
   Set Sht = ActiveWorkbook.Sheets("P-Pipeline")
   ChDrive "W:"
   ChDir "W:\abcd\abcd\abcd\abcd\abcd\"
   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("Report")
         Sht.Range("A4:S10009").ClearContents
         .Range("A2:S10000").Copy Sht.Range("A4")
      End With
      Wbk.Close , False
   End If
End Sub

 

[Youngdand]

Actually the active workbook would not be the "Report" one, the "report" one would be the data that needs to be copied and paste to the active workbook "P-Pipeline".

Sorry, Got a little sidelined today with work, glad that fluff has jumped in to save the day for you. and its a very simple elegant solution.

Regards,

Dan.

 

[Jeffreyxx01]

What do I have to write into the line:
ChDrive "W:" ??

 

[Fluff]

What do I have to write into the line:
ChDrive "W:" ??

If the file is on the W drive, then leave that "as is", otherwise change W to the correct drive letter

 

[Jeffreyxx01]

Am I completing this one with the name of the file?

ChDir "W:\abcd\abcd\abcd\abcd\abcd\

Sorry for the bad questions

 

[Youngdand]

Fname = Application.GetOpenFilename(FileFilter:="xls Files (*.xls*), *.xls*", Title:="Select a file", MultiSelect:=False)

Just the directory the file is stored in, as it will prompt you to select the file.

 

[Fluff]

Thinking about it, as we know the name of the file to be opened, the code can be simplified to this

Sub OpenFile()
   
   Dim Wbk As Workbook
   Dim Sht As Worksheet
   
   Set Sht = ActiveWorkbook.Sheets("P-Pipeline")
      Set Wbk = Workbooks.Open("[COLOR=#ff0000]W:\abcd\abcd\abcd\abcd\abcd\Report.xlsx[/COLOR]")
      With Wbk.Sheets("Report")
         Sht.Range("A4:S10009").ClearContents
         .Range("A2:S10000").Copy Sht.Range("A4")
      End With
      Wbk.Close , False
End Sub

Where the part in red needs to be the complete path & filename.

 

[Jeffreyxx01]

Thanks guys,

Fluff this seems to work but, the end of the code is actually taking the wrong data and also it erases the header of the P-pipeline data.

Report = need to take the data from range A2:S
and paste it to P-pipeline to A4:S

 

[Fluff]

Report = need to take the data from range A2:S
and paste it to P-pipeline to A4:S

That's what it should be doing.
It clears A4:S10009 from the Pipeline sheet & then copies A2:S10000 from Report to A4 Pipeline.
Could you please be a bit more specific, about what is happening?

 

[Jeffreyxx01]

It actually works,
It is because at the end on the report sheet, I could see the selection array from a4:s10000 and I thought it missed the first two lines.

Thank you very much Fluff, it works well.