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.

 

[Youngdand]

Do you really need a macro?

You can do this, by right clicking the sheet name, selecting move copy sheet, select the workbook you want to copy to from the drop down list and click create a copy then OK.

Regards,

Dan.

 

[Jeffreyxx01]

I would like to have the macro that pick up the data from one sheet and copy and paste the range data to another workbook to a special range,
hence it will automate the task for the future.

I need it because I have many tasks that needs that type of macro and it would help me to have less workload.

Thanks.

 

[Youngdand]

HI,

Then you need to be a lot more descriptive of what exactly you need. eg, do you need the entire usedrange of the source sheet to be copied? or what range do you need? what is the sheet and document name to send the data to. what position to insert into etc. does the will the target document be open or closed?

with full information, and possibly attached copies of workbooks, or at at least examples, people will be able to give you more help.

Regards,

Dan.

 

[Jeffreyxx01]

Hi Dan and thanks,

I would like to copy data from a workbook that is called "Report" with worksheet name "Report" with range("A1:S10000") that comes from the source : ("W:\abcd\abcd\abcd\abcd\abcd\RawData\Report.xlsx")
and copy these data to this file: W:\abcd\abcd\abcd\abcd\abcd\abcd_ as at 22 12 17)

The date of this files in Red would change every week to +7 days. like 22 29 05 12 and so on...

the data that has to be copied should be paste into the file W:\abcd\abcd\abcd\abcd\abcd\abcd_ as at 22 12 17) at the worksheet: P-pipeline range("A4:S10000")

I hope this helps.

Thanks a lot.

 

[Youngdand]

Hi Dan and thanks,

I would like to copy data from a workbook that is called "Report" with worksheet name "Report" with range("A1:S10000") that comes from the source : ("W:\abcd\abcd\abcd\abcd\abcd\RawData\Report.xlsx")
and copy these data to this file: W:\abcd\abcd\abcd\abcd\abcd\abcd_ as at 22 12 17)

The date of this files in Red would change every week to +7 days. like 22 29 05 12 and so on...

the data that has to be copied should be paste into the file W:\abcd\abcd\abcd\abcd\abcd\abcd_ as at 22 12 17) at the worksheet: P-pipeline range("A4:S10000")

I hope this helps.

Thanks a lot.

Does W:\abcd\abcd\abcd\abcd\abcd\abcd_ as at 22 12 17) exist in the first instance? if not, why not make the macro create the file, Does this task only get run once per week? are you likely to ever overwrite the file? and is so, would it replace the whole range? also is your range always 10,000 lines long, or are you setting this value to be safe? as we can simply select the used range whatever it might be.

If this task is only run once per week, and the sheets does not already exist, then it just as easy to copy the range to a new workbook, and save it in a location with the correct filename you require. IF the reason for not doing this, is because you want put headers etc in, then you can just store the layout on a sheet within the reports workbook in which to copy them from.

Upload an example of the the reports sheet, and the output sheet you require, and i will put something together for you.

Regards,

Dan.

 

[Jeffreyxx01]

Hi Dan,

Thanks for your quick answer,
I actually do not know how to upload any workbook here also I cannot provide worksheet that come from work as it is private data.

I have put the range as safe yes, I know there is the xlend column or row formula.
I will overwrite the file each week with new data taken each Friday, hence can probably use a clearcontents formula or just paste on top of the existing data.
Also I have on the file that need data to be paste a tons of formula on the right hand side after the S column, that is why I just want to copy and paste the data from A2:S to the range A4 : S



The below information does exist, I just changed the name so I do not furnish private information, the bold and red are the one that change every Friday.
W:\abcd\abcd\abcd\abcd\abcd\abcd_ as at 22 12 17)


Thank you very much for your help. I dont know if my answer makes sense to you but hopefully it does.

 

[Fluff]

How about

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

Change values in red as needed

 

[Jeffreyxx01]

I will try your workings, thank you for sharing

How about

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

Change values in red as needed

 

[Jeffreyxx01]

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".