How to copy data in VBA from different named workbook each time
Category: [Excel] Demo Available
How to copy data in VBA from different named workbook each time
Requirement:
The code below does what the user wants but wants to know if it is possible to activate the same range from a different named source workbook each time?
Ideally the user does not want any code in the source file.
The user is trying to avoid putting any code in the source workbook. This is an orderform going out to customers, and the user does not want coded workbooks going to them.
The user would like to start with destination as the active workbook - If the user sets destination as wkb1 is there some way of setting source as wkb2?
The user understands both workbooks must be opened.
Sub RetOrdfrmdata()
'
'
Application.ScreenUpdating = False
' Windows("LOFtestvar.xlsx").Activate - SOURCE WORKBOOK
Range("C74:C118").Select
Selection.Copy
Windows("LJA databasetestvar.xlsm").Activate - DESTINATION WORKBOOK
Range("C54").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("a3").Select
End Sub
Solution:
Code:
Sub CopyBetweenWkbs()
' Run this Macro from the Destination Workbook
Dim Mainfile As String
Dim Sourfile As String
Dim fd As FileDialog
Dim fChosen As Integer
Application.ScreenUpdating = False
Mainfile = ActiveWorkbook.Name ' Destination
' Choose Source Workbook to be opened ....
Set fd = Application.FileDialog(msoFileDialogFilePicker)
fd.Title = "Please Select File"
' Adjust to your situation
fd.InitialFileName = "D:\My Documents\Excel\*.xls"
fChosen = fd.Show
If fChosen <> -1 Then
MsgBox "You Cancelled, Nothing done ... ": Exit Sub
Else
Sourfile = Right$(fd.SelectedItems(1), Len(fd.SelectedItems(1)) - InStrRev(fd.SelectedItems(1), "\"))
End If
' Open Selected Workbook
Workbooks.Open (Sourfile)
' Adjust the Copy Instruction to the specific Sheet Names and Ranges
Workbooks(Mainfile).Sheets("Sheet1").Range("C74:C118").Value = Workbooks(Sourfile).Sheets("Sheet1").Range("C54:C98").Value
Application.ScreenUpdating = True
End Sub
Obtained from the OzGrid Help Forum.
Solution provided by Carim.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to use VBA code to clear cells based on specific criteria |
| How to convert split formula in VBA in their respective columns |
| How to use VBA code - Find value from cell in different column and multiply by another cell |
| How to use VBA code to ccolour tabs based on tab/text number |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.