+ Reply to Thread
Results 1 to 5 of 5

Getting info from specific cells in many files -VBA

  1. #1
    Registered User
    Join Date
    10-11-2020
    Location
    Brooklyn, NY
    MS-Off Ver
    Microsoft 365
    Posts
    3

    Getting info from specific cells in many files -VBA

    Hello I hope someone could help me.

    Is there any way of getting info from specific cells in many files using VBA to be brought back into one file?

    All my files begin with OTDA and the information I need is in about a hundred files.

    I was hoping to be able to use a VBA macro to get the information from the third tab named "Deficiencies List", and the cells I need are B1, B2, B3, G1, G2, G3, G4 and J1

    All the headings are in the same place and use the same name.
    Last edited by vnaza112; 10-11-2020 at 11:07 PM.

  2. #2
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Getting info from specific cells in many files -VBA

    Sounds like a perfect candidate for using Power Query.

    Post a sample file, showing the input structure, and your required output.
    let Source = #table({"Question","Thread", "User"},{{"Answered","Mark Solved", "Add Reputation"}}) in Source

    If I give you Power Query (Get & Transform Data) code, and you don't know what to do with it, then CLICK HERE

    Walking the tightrope between genius and eejit...

  3. #3
    Registered User
    Join Date
    10-11-2020
    Location
    Brooklyn, NY
    MS-Off Ver
    Microsoft 365
    Posts
    3
    Quote Originally Posted by Olly View Post
    Sounds like a perfect candidate for using Power Query.

    Post a sample file, showing the input structure, and your required output.
    Thank you for your reply. Please keep in mind the the example of Input are about hundred.
    Attached Files Attached Files

  4. #4
    Registered User
    Join Date
    10-11-2020
    Location
    Brooklyn, NY
    MS-Off Ver
    Microsoft 365
    Posts
    3
    Quote Originally Posted by vnaza112 View Post
    Thank you for your reply. Please keep in mind the the example of Input are about hundred.
    In case anyone else had this issues as well.

    I found exactly what I needed on YouTube, ”Copy Non Adjacent Cells Data from Multiple Workbooks”
    By: Dinesh Kumar Takyar

    The VBA is as follows:

    Sub copyNonAdjacentCellsData()
    Dim myFile As String, path As String
    Dim erow As Long, col As Long

    path = “E:\Master File OTDA\”
    myFile = Dir(path & “*.xlsx”)

    Application.ScreenUpdating = False

    Do While myFile <> “”
    Workbooks.Open (path & myFile)
    Windows(myFile).Activate

    Set copyrange = Sheets(“Deficiencies List”).Range(“B1,B2,B3,G1,G2,G3,G4,J1”)

    Windows(“master-wbk.xlsm”).Activate

    erow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

    col = 1
    For Each cel In copyrange
    cel.Copy

    Cells(erow, col).PasteSpecial xlPasteValues

    col = col + 1

    Next

    Windows(myFile).Close savechanges:=False
    myFile = Dir()
    Loop
    Range(“A:E”).EntireColumn.AutoFit

    Application.ScreenUpdating = True

    End Sub

  5. #5
    Forum Expert Olly's Avatar
    Join Date
    09-10-2013
    Location
    Darlington, UK
    MS-Off Ver
    Excel 2016, 2019, 365
    Posts
    6,284

    Re: Getting info from specific cells in many files -VBA

    Here's an alternative using Power Query - just change the folder path in the Source line:

    Please Login or Register  to view this content.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Pull specific info for a range of cells
    By balucanb in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 05-10-2019, 10:43 AM
  2. Macro to unlock cells in design mode and pull info from several files into 1
    By luajambeiro in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-16-2019, 12:51 PM
  3. format multiple cells if 2 cells have specific info
    By compgeek1979 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 07-16-2015, 02:56 PM
  4. Search for info and copy found info to specific cells
    By Costas in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 02-19-2010, 07:12 AM
  5. Multiple excel files and collating specific cell info
    By Ska in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 10-20-2007, 04:48 AM
  6. extract non-specific info from multiple cells
    By rossww in forum Excel General
    Replies: 3
    Last Post: 07-25-2006, 06:06 AM
  7. extract specific info from cells in a column
    By Herman in forum Excel - New Users/Basics
    Replies: 1
    Last Post: 10-26-2005, 10:05 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1