Macro not pulling the correct data - Help!
Results 1 to 2 of 2

Thread: Macro not pulling the correct data - Help!
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2018
    Post Thanks / Like
    0 Post(s)
    0 Thread(s)

    Default Macro not pulling the correct data - Help!

    Hi Guys,

    I hope you can help with the below macro. It runs well as long as Sheet1 is visible (Ideally I'd like it to work even when sheet1 is hidden) however I've also now noticed that it's actually pulling the data from the last tabbed saved in the workbook rather than referencing Sheet1.

    So if Sheet 1 is the tab saved before closing the workbook the macro works, If it is saved on a different tab then it pulls the wrong data.

    Any help on this would be amazing.

    Currently I use this to extract a line of data out of multiple workbooks to collate the information on one workbook

    Sub LoopThroughDirectory()
    Dim Filepath As String
    Dim erow
    Filepath = Application.ActiveWorkbook.Path
    MyFile = Dir(Filepath + "\*.*")
    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    Sheet1.Rows("2:" & Format(erow)).EntireRow.ClearContents
    Do While Len(MyFile) > 0
    If MyFile = "ZMasterFile.xlsm" Then
    Exit Sub
    End If
    Application.DisplayAlerts = False
    Workbooks.Open (Filepath & "" & MyFile)
    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
    ActiveSheet.Paste Destination:=Worksheets("Raw Data").Range(Cells(erow, 1), Cells(erow, 13))
    MyFile = Dir
    Application.DisplayAlerts = True
    End Sub
    Last edited by mole999; Jun 3rd, 2019 at 12:31 PM. Reason: code tags
    0 0

  2. #2
    MrExcel MVP
    Fluff's Avatar
    Join Date
    Jun 2014
    Post Thanks / Like
    461 Post(s)
    46 Thread(s)

    Default Re: Macro not pulling the correct data - Help!


    Please do not post the same question multiple times. All clarifications, follow-ups, and bumps should be posted back to the original thread.
    Per forum rules, posts of a duplicate nature will be locked or deleted (rule 12 here: Forum Rules).
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10
    0 0

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts