Transfer data between workbooks VBA

iain88

New member
Joined
Apr 17, 2019
Messages
4
Reaction score
0
Points
0
Excel Version(s)
office 365
Hi

i get an error on my macro below on the top line and cant work out why? wanting to transfer data from one workbook to another, with the data getting added to the bottom of the new list.
work books attached.

------------------------------
Code:
Sub MyAmazingMacro()


Dim Name As String, Device As String, staffdate As String
Worksheets("form").Select
Name = Range("B2")
Device = Range("C2")
StartToday = Range("D2")
Worksheets("asset").Select
Worksheets("asset").Range ("A1")[ATTACH]9064._xfImport[/ATTACH][ATTACH]9065._xfImport[/ATTACH]
If Worksheets("asset").Range("A1").Offset(1, 0) <> "" Then
Worksheets("asset").Range("A1").End(x1down).Select
End If
ActiveCell.Offset(1, 0).Select
ActiveCell.Value = Name
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Device
ActiveCell.Offset(0, 2).Select
ActiveCell.Value = staffdate


End Sub
 
Last edited by a moderator:
.
The following macro presumes both workbook are located on your desktop :

Code:
Sub Update()


    Dim wbk As Workbook
    Dim folderPath As String
    Dim Filename As String
    Dim wb As Workbook
    folderPath = Environ("USERPROFILE") & "\Desktop\" 'contains folder path
    If Right(folderPath, 1) <> "\" Then folderPath = folderPath + "\"
    Filename = Dir(folderPath & "VBA Test 2.xlsx")   '
    Do While Filename <> ""
        Application.DisplayAlerts = False
        Application.ScreenUpdating = False
        Set wb = Workbooks.Open(folderPath & Filename)
        Sheets("Asset").Range("A1:D50" & Range("A" & Rows.Count).End(xlUp).Row).Copy    'change range to copy here
        ThisWorkbook.Worksheets("form").Range("a" & Rows.Count).End(xlUp)(2).PasteSpecial
        ThisWorkbook.Worksheets("form").Activate
        ThisWorkbook.Worksheets("form").Range("A1").Select
        Workbooks(Filename).Close True
        Filename = Dir
    Loop
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True


End Sub
 
@iain
In the future please wrap code with code tags ( click Go advanced - select code click the #button)
I did it for you this time
 
Back
Top