Copy one worksheet to another from different workbook

Jeffreyxx01

Board Regular
Joined
Oct 23, 2017
Messages
156
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.
 
OK, try this
Code:
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
 
Upvote 0

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
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.
 
Upvote 0
Am I completing this one with the name of the file?

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

Sorry for the bad questions :confused:
 
Upvote 0
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.
 
Last edited:
Upvote 0
Thinking about it, as we know the name of the file to be opened, the code can be simplified to this
Code:
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.
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,223,911
Messages
6,175,329
Members
452,635
Latest member
laura12345

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top