Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Copy data from open file (one I am currently in) and paste into a specific file in two sheets within the file.

0

Hi I was wondering if anyone knows how I can copy data from open file (one I am currently in) and paste into a specific file - paste in two sheets within the file.

I already have a code which will open the most recent file from a directory, but I then want it to select all the data in column A, copy it and paste it into my 'master sheet' - pasting the data in two of the sheets I specify in the master document,

but before pasting  the whole of the sheets it goes into needs to be cleared so the new data will start in cell A1 and go down from there (basically the cells they were in before but in the master document)

I will also need to make the code I already have (below) search every 20 seconds and not return the Msg box if there isnt any but search again, if anyone could help with that too it would be really appreciated. 

Thanks

Option Explicit

Sub NewestFile()

    Dim MyPath As String

    Dim MyFile As String

    Dim LatestFile As String

    Dim LatestDate As Date

    Dim LMD As Date

    MyPath = "C:\new folder"

    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"

    MyFile = Dir(MyPath & "*.LIF", vbNormal)

    If Len(MyFile) = 0 Then

        MsgBox "No files were found...", vbExclamation

        Exit Sub

    End If

    Do While Len(MyFile) > 0

        LMD = FileDateTime(MyPath & MyFile)

        If LMD > LatestDate Then

            LatestFile = MyFile

            LatestDate = LMD

        End If

        MyFile = Dir

    Loop

    Workbooks.Open MyPath & LatestFile

End Sub
Answer
Discuss

Answers

0
Selected Answer

Your title doesn't seem to match with how you describe running the macro, so sorry if I get the order of the code/process incorrect.

Of course a lot depends on your specific implementation, but here I will try to give you the missing pieces...

I assume that you run this macro from your master workbook. Also, declare variables as you wish at the top of your macro, I'll just include the relevant pieces of code here.

Somewhere toward the top of your macro, set a workbook variable to the current (This) workbook:

set wbMaster = ThisWorkbook

Change your .Open line of code to this:

set wbLatest = Workbooks.Open(MyPath & LatestFile)

To clear the data, which I assume was in the master workbook, you can do this:

wbMaster.Worksheets("Your Sheet Name").Cells.Clear

To copy the data from the latest file to the master workbook, do this:

wbLatest.Worksheets("Sheet with Data").Range("A:A").Copy wbMaster.Worksheets("Sheet to put the data").Range("A1")

Running every 20 seconds you can do using the macro that is in this tutorial: Run a Macro at Intervals

This should get you on your way!

(I typed this out by hand so none of it is tested, hopefully I didn't make mistakes, but just a warning)

Update

Sample code:

Option Explicit

Sub NewestFile()

    Dim MyPath As String

    Dim MyFile As String

    Dim LatestFile As String

    Dim LatestDate As Date

    Dim LMD As Date

    Dim wbMaster As Workbook
    Dim wbLatest As Workbook

    Set wbMaster = ThisWorkbook

    MyPath = "C:\new folder"

    If Right(MyPath, 1) <> "\" Then MyPath = MyPath & "\"

    MyFile = Dir(MyPath & "*.LIF", vbNormal)

    If Len(MyFile) = 0 Then

        MsgBox "No files were found...", vbExclamation

        Exit Sub

    End If

    Do While Len(MyFile) > 0

        LMD = FileDateTime(MyPath & MyFile)

        If LMD > LatestDate Then

            LatestFile = MyFile

            LatestDate = LMD

        End If

        MyFile = Dir

    Loop

    Set wbLatest = Workbooks.Open(MyPath & LatestFile)

    ' Clear some stuff
    wbMaster.Worksheets("Sheet1").Cells.Clear

    ' Copy/Paste some stuff
    wbLatest.Worksheets("Sheet with Data").Range("A:A").Copy wbMaster.Worksheets("Sheet to put the data").Range("A1")

End Sub

Try to get this working before you work on making it run automatically.

Change the worksheet names to where you want to get data from and put it.

Run the macro from the master workbook.

Let me know how this works!

Discuss

Discussion

Hi thanks for this,
I'm only a beginner to macros and VB so not sure how to do quite a lot.
how do i put the code you wrote into the code I put above? 
Also it no longer needs to be pasted into two sheets - just one in the master document, not sure whether this effects the your code.

Is it possible to make it clear the sheet only when the new data comes in because the other data goes to a scoreboard using the referencing formulas so would just clear the scoreboard if there is no new data. 

If you are able to put the code together will it do it automatically like you mentioned I read that other article to make it do it automatically but also found a pre made one to get a workbook save automatically (code below). Could you use that code to do it every set time (I've learnt how to change hoe often it runs)

Hope you can help. Many thanks for all you have already done
Thank you
Sub Save1()
Application.DisplayAlerts = False
ThisWorkbook.Save
Application.DisplayAlerts = True
 
Application.OnTime Now + TimeValue("00:01:00"), "Save1"
Application.EnableCancelKey = xlInterrupt
End Sub
Dave1245 (rep: 8) May 24, '20 at 5:20 pm
Post updated, check it out.
don (rep: 1989) May 25, '20 at 8:05 am
Thanks
Dave1245 (rep: 8) May 28, '20 at 5:24 am
Add to Discussion


Answer the Question

You must create an account to use the forum. Create an Account or Login