Hello,
I am trying to create a macro that opens another file, copies specific cells, and then pastes onto my current workbook. I can't seem to define the new workbook when it opens, can someone please tell me where my code is going wrong? Thanks
I am trying to create a macro that opens another file, copies specific cells, and then pastes onto my current workbook. I can't seem to define the new workbook when it opens, can someone please tell me where my code is going wrong? Thanks
VBA Code:
Sub Macro1()
'
' Update LED Table Macro - this macro will open and copy the data from the LED Strategic tab into the workbook
Dim wbCopy As Worksheet
Dim wbDest As Worksheet
Set wbDest = ThisWorkbook.Sheets("Data")
' Open the GB AA Spreadsheet
Dim fd As Office.FileDialog
Dim strFile As String
Set fd = Application.FileDialog(msoFileDialogFilePicker)
With fd
.Filters.Clear
.Filters.Add "Excel Files", "*.xlsx?", 1
.Title = "Choose an Excel file"
.AllowMultiSelect = False
.InitialFileName = "Q:\Bri\Greenbank\Investment\Asset Allocation\"
If .Show = True Then
strFile = .SelectedItems(1)
End If
End With
Workbooks.Open (strFile)
Set wbCopy = ThisWorkbook.Sheets("LED Strategic")
'Copy the Cells
wbCopy.Range("I3,N3,S3,X3,AC3,I10:I14,N10:N14,S10:S14,X10:X14,AC10:AC14,I24,N24,S24,X24,AC24").Copy
wbDest.Range("H2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True
'Close the GB AA Spreadsheet
wbCopy.Close
End Sub