VBA Copying data from one workbook to another workbook, specific ranges & tabs

jmaxwell25

New Member
Joined
Sep 25, 2019
Messages
1
Hi need some help please

I have the following VBA for a report which copy's over Specific data from one workbook to another workbook in therange & tab I need it to, however I would like it to automatically know thefile path & report I want the data copied from, this code currently askswhich report the user would like to open. How do I do this?
Thanks

Sub _TransActuals1()

Dim Fname As String
Dim SrcWbk AsWorkbook
Dim DestWbk AsWorkbook


Set DestWbk =ThisWorkbook

Fname = Application.GetOpenFilename(FileFilter:="ExcelFiles (*.xls*), *.xls*", Title:="Select a File")

If Fname ="False" Then Exit Sub
Set SrcWbk =Workbooks.Open(Fname)

Range("A2").Select
Range("U6000").Select
Selection.Copy
Windows("MonthlyVariance Report.xlsm").Activate
Sheets("Tran Actuals").Select
Range("B2").Select
ActiveSheet.Paste


SrcWbk.Close False

End Sub

 

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.
Will the file name name & path always be the same?
 
Upvote 0
Hi thanks for asking, I have actually cracked it after several hours fishing the internet...

Sub Actuals()

Dim Fname As String
Dim SrcWbk As Workbook
Dim DestWbk As Workbook

strPath2 = "C:\Users\jmmaxwell\Desktop\PERSAPS\Bids_Projects\Variance Reports\MASTERCOPY_Actuals"

Set DestWbk = ThisWorkbook
Set wbkWorkbook2 = Workbooks.Open(strPath2)

Range("A2:u6000").Select
Selection.Copy
Windows("Monthly Variance Report.xlsm").Activate
Sheets("Actuals").Select
Range("B2").Select
ActiveSheet.Paste
wbkWorkbook2.Close SaveChanges:=False

End Sub
 
Upvote 0

Forum statistics

Threads
1,213,492
Messages
6,113,967
Members
448,537
Latest member
Et_Cetera

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