TaskMaster
Board Regular
- Joined
- Oct 15, 2020
- Messages
- 55
- Office Version
- 365
- 2016
- Platform
- Windows
Hi All,
I am trying to copy and paste values from two separate documents into my main document "Re-build v6". I have got the file locations in cells S2 & S3 and the file name in T2 & T3, as this is a daily file the file name changes and I want to copy the most up to date info. I keep getting an error at "Windows(FileName1).Activate"
S2 = 11. November\Daily Movements - 24.11.20
S3 = 11. November\Daily Movements - 25.11.20
T2 = Daily Movements - 24.11.20.xlsm
T3 = Daily Movements - 25.11.20.xlsm
Thanks in advance.
I am trying to copy and paste values from two separate documents into my main document "Re-build v6". I have got the file locations in cells S2 & S3 and the file name in T2 & T3, as this is a daily file the file name changes and I want to copy the most up to date info. I keep getting an error at "Windows(FileName1).Activate"
S2 = 11. November\Daily Movements - 24.11.20
S3 = 11. November\Daily Movements - 25.11.20
T2 = Daily Movements - 24.11.20.xlsm
T3 = Daily Movements - 25.11.20.xlsm
Thanks in advance.
VBA Code:
Sub Macro1()
'
' Copy Values
Dim FileLocation As String
Dim FileLocation1 As String
Dim FileLocation2 As String
Dim FilePath1 As String
Dim FilePath2 As String
Dim FileName1 As String
Dim FileName2 As String
FileLocation = "\\Users\Documents\Test"
FileLocation1 = "S2"
FileLocation2 = "S3"
FileName1 = "T2"
FileName2 = "T3"
FilePath1 = FileLocation & "\" & Range(FileLocation1).Value & ".xlsm"
FilePath2 = FileLocation & "\" & Range(FileLocation2).Value & ".xlsm"
Workbooks.Open (FilePath1)
Windows(FileName1).Activate
Sheets("Movements").Select
Range("C6").Select
Selection.Copy
Windows("Re-build v6.xlsm").Activate
Range("D24").Select
Selection.PasteSpecial Paste:=xlPasteValues
Windows(FileName1).Activate
Range("C5").Select
Selection.Copy
Windows("Re-build v6.xlsm").Activate
Range("D26").Select
Selection.PasteSpecial Paste:=xlPasteValues
Windows(FileName1).Activate
ActiveWindow.Close SaveChanges:=False
Workbooks.Open (FilePath2)
Sheets("Movements").Select
Range("M6").Select
Selection.Copy
Windows("Re-build v6.xlsm").Activate
Range("D25").Select
Selection.PasteSpecial Paste:=xlPasteValues
Windows(FileName2).Activate
Range("M5").Select
Selection.Copy
Windows("Re-build v6.xlsm").Activate
Range("D27").Select
Selection.PasteSpecial Paste:=xlPasteValues
Windows(FileName2).Activate
ActiveWindow.Close SaveChanges:=False
End Sub