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]

Glad we could help & thanks for the feedback

 

[Jeffreyxx01]

I might have a last requirement if you have any time on your hands.

I said earlier I have a tons of formula after the column S to be slide down into the sheet.
Do you have a command that will slide the formulas from column S4:BD until it founds data on the left hand side?
Hence I can just run the full command without slide down the formulas.

That would be perfect.

 

[Fluff]

This will copy formulae from T4:BD4 down to the last used row in Col S

Sub OpenFile()
   
   Dim Wbk As Workbook
   Dim Sht As Worksheet
   Dim UsdRws As Long
   
   Set Sht = ActiveWorkbook.Sheets("P-Pipeline")
   Set Wbk = Workbooks.Open("W:\abcd\abcd\abcd\abcd\abcd\Report.xlsx")
   With Wbk.Sheets("Report")
      Sht.Range("A4:S10009").ClearContents
      .Range("A2:S10000").Copy Sht.Range("A4")
   End With
   Wbk.Close , False
   UsdRws = Sht.Range("S" & Rows.Count).End(xlUp).row
   Sht.Range("T4:BD" & UsdRws).FillDown
      
End Sub

 

[Jeffreyxx01]

~Cheers Fluffy,

it works perfectly, very grateful of your time and knowledge. I wish I could do complex macro.

 

[Fluff]

Your welcome

 

[Jeffreyxx01]

Another query I guess,

The command you made for pulling the data from Report to P-Pipeline,
When executing the command it works well but I have to say: Don't save into the Report in order to close the report,
By any chance does the command can avoid having the file "report" to open and just pull the data from it close?

Thanks a lot

 

[Fluff]

This should stop the alerts

Sub OpenFile()
   
   Dim Wbk As Workbook
   Dim Sht As Worksheet
   Dim Usdrws As Long

Application.ScreenUpdating = False

   Set Sht = ActiveWorkbook.Sheets("P-Pipeline")
   Set Wbk = Workbooks.Open("W:\abcd\abcd\abcd\abcd\abcd\Report.xlsx")
   With Wbk.Sheets("Report")
      Sht.Range("A4:S10009").ClearContents
      .Range("A2:S10000").Copy Sht.Range("A4")
   End With
Application.DisplayAlerts = False
   Wbk.Close , False
Application.DisplayAlerts = True
   Usdrws = Sht.Range("S" & Rows.Count).End(xlUp).row
   Sht.Range("T4:BD" & Usdrws).FillDown
      
End Sub

 

[Jeffreyxx01]

That is perfect :D

 

[Jeffreyxx01]

Hello Fluff,

Happy new year and thanks for your great work,
I have tried to change your code to one of my spreadsheet,

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 = Sht.Range("AE" & Rows.Count).End(xlUp).Row
            Sht.Range("AE2:BA" & Usdrws).FillDown
   End If


End Sub

However the last bit does not slide down the formula as the one you made for me before,
Do you know why?

Thanks.

Jeffrey

 

[Fluff]

Try changing this

Usdrws = Sht.Range("AE" & Rows.Count).End(xlUp).Row

to AD