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
    Posts
    27
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    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


    Code:
    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)
    Range("A2:M900").Copy
    
    
    ActiveWorkbook.Close
    
    
    
    
    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
    
    
    Loop
    
    
    End Sub
    Last edited by mole999; Jun 3rd, 2019 at 12:31 PM. Reason: code tags
    0 0
     

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,029
    Post Thanks / Like
    Mentioned
    461 Post(s)
    Tagged
    46 Thread(s)

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

    Duplicate https://www.mrexcel.com/forum/excel-...ml#post5287679

    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
  •