I'm trying to set up a loop to pull data from multiple cells in multiple worksheets

cshortt · Nov 23rd 2016

I have a workbook that contains about 265 vehicle data worksheets. Each uniquely numbered vehicle has it's own worksheet with detailed information about that vehicle. Every vehicle page is identical, accept for the data stored in it. These pages are setup in rows and columns of data, but not in a line-by-line list. One of these pages is a list of all of these vehicles with certain cells of information that i need to pull over from each of the individual vehicle workbooks. I've been trying for days now to set up a loop to bring this information from each individual page to this line-by-line list with little to no success.

Here is an example of what I am trying to do.

First vehicle on the list is vehicle;

A-01 (vehicle number, not cell reference) , I want the VBA code to go to A-01's worksheet and pull data from cells "B30", "B18", "B9", "B12", "B15", "B21", "B33", "B27", "J30", "F24", "H24", "D18". Then paste that data back to a row in the list of vehicles into cells "C7", "D7", "E7", "F7", "G7", "H7", "I7", "J7", "K7", "L7", "M7", "N7", in that order.
Then I want it to go to vehicle A-02 (next worksheet) and copy the data from the same set of cells (B30, B18, B9....etc.) and copy that to the next row on the list worksheet, Then go back and do the same thing for vehicle A-03, then for A-04, then A-05........untill the end of the list.

I can get the data from the first worksheet (vehicle A-01) but I can't figure out how to get the VBA to go to the next worksheet to get the next set of data and paste it.
I have tried a For All loop with some success, but there are 4 or 5 worksheets that I want it to skip over because they contain data that is not relevant to the worksheet I am trying to build.
One other thing I should note, currently the first page contains the data (entered manually) in a table. I am trying to get VBA to paste the data into that table.
I hope this is clear. I've tried to describe it to the best of my ability. Thanks for any help you can offer.

I have uploaded a small sample of the list with what I would like it to look like after running the macro and updating all of the fields.forum.ozgrid.com/index.php?attachment/70701/

**royUK** · Nov 24th 2016

Re: I'm trying to set up a loop to pull data from multiple cells in multiple workshee

There is no attachment.

GOLDENEXCEL · Nov 24th 2016

Re: I'm trying to set up a loop to pull data from multiple cells in multiple workshee

Hi cshortt,
For the VBA to go to the next worksheet to get the next set of data and paste it, you can try this:

Code

Dim ws As Worksheet


    For Each ws In ThisWorkbook.Worksheets


    ''''''Skip sheets which does not relevent
    If ws.Name <> "Statistics" or ws.Name <> "List - By Vehicle Number" or ws.Name <> "Defaults" or ws.Name <> "TEMPLATE" Then


   ************your code copy and past here*****************


    End If
    Next ws

Display More

Thank

cshortt · Nov 24th 2016

Re: I'm trying to set up a loop to pull data from multiple cells in multiple workshee

Quote from GOLDENEXCEL;781352

Hi cshortt,
For the VBA to go to the next worksheet to get the next set of data and paste it, you can try this:

Dim ws As Worksheet

For Each ws In ThisWorkbook.Worksheets

''''''Skip sheets which does not relevent
If ws.Name <> "Statistics" or ws.Name <> "List - By Vehicle Number" or ws.Name <> "Defaults" or ws.Name <> "TEMPLATE" Then

************your code copy and past here*****************

End If
Next ws

Display More

Thanks so much for the help and replies. I did as you suggested and it did not paste the correct data. I put a watch on ws and it is still selecting "Statistics" as the first workbook it pulls data from. Maybe I have the code in the wrong place or something else. I have pasted a copy of my full code for you to look at. Maybe that will help.

Code

Sub Copy_From_Vehicle_Page_To_Vehicle_List()


'Tells excel to start at row 7
Dim xrow As Long
xrow = 7
Dim lastrow As Long
Dim ws As Worksheet


'Selects the List - By Vehicle Number page.
Sheets("List - By Vehicle Number").Select




'Tells excel to select cell B7.
    Range("B7").Select
    
    'Tells excell to loop through each worksheet until the last row
    For Each ws In ThisWorkbook.Worksheets
    
    'Tells excel to ship sheets List - By vehicle Number, Defaults and TEMPLATE worksheets during loop
        If ws.Name <> "Statistics" Or ws.Name <> "List - By Vehicle Number" Or ws.Name <> "Defaults" Or ws.Name <> "TEMPLATE" Then


    
    'Tells excel to end loop when a blank cell in colum B is found
    If ActiveCell.Value = "" Then Exit For
        
        
        'Tells excel to copy the data from vehicle sheet to vehicle table list view.
        Range("C7").Value = ws.Range("B30")
        Range("D7").Value = ws.Range("B18")
        Range("E7").Value = ws.Range("B9")
        Range("F7").Value = ws.Range("B12")
        Range("G7").Value = ws.Range("B15")
        Range("H7").Value = ws.Range("B21")
        Range("I7").Value = ws.Range("B33")
        Range("J7").Value = ws.Range("B27")
        Range("K7").Value = ws.Range("J30")
        Range("L7").Value = ws.Range("F24")
        Range("M7").Value = ws.Range("H24")
        Range("N7").Value = ws.Range("D18")
        
        'Tells excel to move down one row in the same column
        ActiveCell.Offset(1, 0).Select
        
     End If
    
    Next ws


End Sub

Display More

**Trebor76** · Nov 25th 2016

Re: I'm trying to set up a loop to pull data from multiple cells in multiple workshee

Quote

Each uniquely numbered vehicle has it's own worksheet with detailed information about that vehicle. Every vehicle page is identical, accept for the data stored in it.

In my humble opinion this is not the way to go. You could just have two tabs - one for the data and the second for the presentation which would have a drop-down for the vehicle number that upon selection would update the tab from the data tab via standard lookup formulas.

That said, see how this macro goes:

Code

Option Explicit
Sub Copy_From_Vehicle_Page_To_Vehicle_List()
     
    Dim lngMyRow    As Long
    Dim ws          As Worksheet
    Dim wsConsSheet As Worksheet
    
    Application.ScreenUpdating = False
    
    Set wsConsSheet = Sheets("List - By Vehicle Number") 'Sheet the data will be copied to. Change to suit
          
    'Tells excell to loop through each worksheet until the last row
    For Each ws In ThisWorkbook.Worksheets
         
         'Tells excel to skip the following sheets
        If ws.Name <> "Statistics" Or ws.Name <> "List - By Vehicle Number" Or ws.Name <> "Defaults" Or ws.Name <> "TEMPLATE" Then
             
            'Set the 'lngMyRow' variable
            If lngMyRow = 0 Then
                lngMyRow = 7
            Else
                lngMyRow = lngMyRow + 1
            End If
             
             'Tells excel to copy the data from vehicle sheet to vehicle table list view.
            With wsConsSheet
                .Range("C" & lngMyRow).Value = ws.Range("B30")
                .Range("D" & lngMyRow).Value = ws.Range("B18")
                .Range("E" & lngMyRow).Value = ws.Range("B9")
                .Range("F" & lngMyRow).Value = ws.Range("B12")
                .Range("G" & lngMyRow).Value = ws.Range("B15")
                .Range("H" & lngMyRow).Value = ws.Range("B21")
                .Range("I" & lngMyRow).Value = ws.Range("B33")
                .Range("J" & lngMyRow).Value = ws.Range("B27")
                .Range("K" & lngMyRow).Value = ws.Range("J30")
                .Range("L" & lngMyRow).Value = ws.Range("F24")
                .Range("M" & lngMyRow).Value = ws.Range("H24")
                .Range("N" & lngMyRow).Value = ws.Range("D18")
            End With
             
        End If
         
    Next ws
    
    Application.ScreenUpdating = True
    
    MsgBox "Complete", vbInformation
     
End Sub

Display More

HTH

Robert

cshortt · Nov 25th 2016

Re: I'm trying to set up a loop to pull data from multiple cells in multiple workshee

Trebor76, thanks so much for the reply. I agree with you about how the database is set up. I built this some 15 years ago and it has more than doubled in size since then. I've been playing with other options on rebuilding this in a more easily manageable way. I actually have started rebuilding it in just the way you stated. But until I get that to work and decide exactly how it will be done, i'm trying to get something working as it exists now. I'll give your code a try today and let you know how it works out.
One problem with the existing database is it almost 100mb in size due to all the photos and takes forever to open. If I split the database into separate workbooks I'm thinking it would load faster. Still in the planning stages of the second iteration of this database.

cshortt · Nov 25th 2016

Re: I'm trying to set up a loop to pull data from multiple cells in multiple workshee

Trebor76,
Again, thanks for the help. The code works, accept for one thing. The loop still does not skip over the worksheets that I don't want it to pull data from. I don't get it. Everyone seems to use this command to try and get the code to skip those sheets, but nothing seems to make it skip them. I've tried renaming them still with no luck. I even set the properties to "VeryHidden" and that did not work either. Just keeps pulling data from them. The only way I can make it work correctly is to delete those sheets all together. Once I do that, none of my vehcile sheets work correctly since one of the sheets has my data validation information in it. Ugh! Frustrating.

**royUK** · Nov 25th 2016

Re: I'm trying to set up a loop to pull data from multiple cells in multiple workshee

Please don't quote every answer it makes the Thread untidy and hard to follow, use the reply button.

You should attach an example workbook and Trebor76 is 100% correct, your present workbook is working in reverse and making hard work .

cshortt · Nov 25th 2016

Re: I'm trying to set up a loop to pull data from multiple cells in multiple workshee

Okay. I'm sorry about the quote thing. For some reason the reply (without quote) button was not showing up on my browser. I closed it down and restarted it, then reloaded the page and it's back now. My humble apologies.
I did attach a sample workbook. It's in my first post.
I think I may have found a work around. When the code copies the data in the cells it includes the sheets that I don't want it to include. Fortunately, it pus this incorrect data in the first three lines. So, on my list page, I inserted three lines above the first line and then hid the lines. This then puts the pasted data on the correct line. It's not perfect, but I'm getting closer.

**Trebor76** · Nov 25th 2016

Re: I'm trying to set up a loop to pull data from multiple cells in multiple workshee

Is there any reason why the tabs aren't named the exact same way as the unit number they're representing i.e. tab A1 is for unit A-01?

If they were we could use use the unit number in column B of the List - By Vehicle Number tab to link the data through via the INDIRECT function - no code required.

kingtimthei · Nov 26th 2016

Re: I'm trying to set up a loop to pull data from multiple cells in multiple workshee

Slightly different take on it cshortt,
I usually use Cells(row #, col #) to reference my data.

Try the following:

Code

Sub CombineAllSheetsinOne()
  Dim r, NumSheets As Integer
  'Do not care about the first 2 or the last two worksheets so subtract 4
  NumSheets = ActiveWorkbook.Sheets.Count - 4


  ' first row in new sheet is 7
  r = 7
  For i = 1 To NumSheets
     'start grabbing data from the 3rd worksheet (A-1)
     ActiveWorkbook.Worksheets(i + 2).Activate
 
      Worksheets("List - By Vehicle Number").Cells(r + i - 1, 3).Value = Cells(30, 2).Value
      Worksheets("List - By Vehicle Number").Cells(r + i - 1, 4).Value = Cells(18, 2).Value
      Worksheets("List - By Vehicle Number").Cells(r + i - 1, 5).Value = Cells(9, 2).Value
      Worksheets("List - By Vehicle Number").Cells(r + i - 1, 6).Value = Cells(12, 2).Value
      Worksheets("List - By Vehicle Number").Cells(r + i - 1, 7).Value = Cells(15, 2).Value
      Worksheets("List - By Vehicle Number").Cells(r + i - 1, 8).Value = Cells(21, 2).Value
      Worksheets("List - By Vehicle Number").Cells(r + i - 1, 9).Value = Cells(33, 2).Value
      Worksheets("List - By Vehicle Number").Cells(r + i - 1, 10).Value = Cells(27, 2).Value
      Worksheets("List - By Vehicle Number").Cells(r + i - 1, 11).Value = Cells(30, 10).Value
      Worksheets("List - By Vehicle Number").Cells(r + i - 1, 12).Value = Cells(24, 6).Value
      Worksheets("List - By Vehicle Number").Cells(r + i - 1, 13).Value = Cells(24, 8).Value
      Worksheets("List - By Vehicle Number").Cells(r + i - 1, 14).Value = Cells(18, 4).Value
    
  Next i
End Sub

Display More

cshortt · Nov 26th 2016

Re: I'm trying to set up a loop to pull data from multiple cells in multiple workshee

Thanks kingtim.
I get a "compile error, Variable Not Defined" when it hits NumSheets = ActiveWorkbook.Sheets.Count - 4. Does this need to be defined somewhere?

kingtimthei · Nov 26th 2016

Re: I'm trying to set up a loop to pull data from multiple cells in multiple workshee

Hmmm, I'm not sure.
Try adding "NumSheets" to the Dim Statement since I hadn't defined it in my original example

Code

Dim r, NumSheets As Integer

It worked for me without it, but sometimes it is not so forgiving.
If it is still giving you problems it may be the version of Office you have. I am using MS Office 2010.

**KjBox** · Nov 26th 2016

Re: I'm trying to set up a loop to pull data from multiple cells in multiple workshee

Try changing

Code

NumSheets = ActiveWorkbook.Sheets.Count - 4

to

Code

NumSheets = ThisWorkbook.Sheets.Count - 4

**Trebor76** · Nov 26th 2016

Re: I'm trying to set up a loop to pull data from multiple cells in multiple workshee

Hi cshortt,

Unless you can match the unit numbers in Col. B of the List - By Vehicle Number tab to the relevant vehicle tabs in the workbook the data won't align regardless of what macro you use.

Robert

cshortt · Nov 27th 2016

Re: I'm trying to set up a loop to pull data from multiple cells in multiple workshee

[ATTACH=CONFIG]70717[/ATTACH]

Here is a shot of my screen and what I getting when I run the update macro. I'm looking to skip the discussed worksheets to eliminate the first two lines on this table. The main reason is that the blank cells above the lines are used for filtering criteria. The way I have it set up, the table can be filtered by any one of the column headings. The added lines are causing problems with the filters.

**KjBox** · Nov 27th 2016

Re: I'm trying to set up a loop to pull data from multiple cells in multiple workshee

The code examples rely on the order of the vehicle detail sheets being exactly the same as column B on the "List - By Vehicle Number" sheet.

You can overcome this by including the vehicle unit number (cell B6 on the details sheet) in the data that is being updated, then sorting the table by Unit number.

Try the attached file. You will see that I mixed up the order of the sheets and added a new Vehicle Details sheet (A11), click the "Update Database" button and you will see that the table updates correctly.

I have also added code to the ThisWorkbook Object module so that any changes made to any vehicle details sheet will be immediately reflected on the "List - By Vehicle Number" sheet. If a new Vehicle Details sheet is added then the new vehicle will automatically be added to the table on "List - By Vehicle Number" sheet.

Code assigned to the Update button (in the "List - By Vehicle Number" sheet Object Module) is

Code

Sub btnUpdate_Click()
    Dim ws As Worksheet, lrow As Long
     
    Application.ScreenUpdating = 0
    For Each ws In Sheets
        If Len(ws.Name) < 5 Then
            lrow = IIf(lrow = 0, 7, lrow + 1)
            A_LBVN.Cells(lrow, 2).Resize(, 13) = _
                Array(ws.[b6], ws.[b30], ws.[b18], ws.[b9], ws.[b12], ws.[b15], ws.[b21], _
                      ws.[b33], ws.[b27], ws.[j30], ws.[f24], ws.[h24], ws.[d18])
        End If
    Next
    [Table1].Sort [Table1[Unit number]], , , , , , , 1
    MsgBox "Database successfully updated", , "Database Update"
    
End Sub

Display More

The code in the ThisWorkbook Object Module isPrivate Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

Code

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim lrow As Long
    
    If Len(Sh.Name) < 5 Then
        If Not IsError(Application.Match(Sh.[b6], A_LBVN.ListObjects(1).Range.Columns(1), 0)) Then
            lrow = Application.Match(Sh.[b6], A_LBVN.ListObjects(1).Range.Columns(1), 0) + 5
            A_LBVN.Cells(lrow, 3).Resize(, 12) = _
                Array(Sh.[b30], Sh.[b18], Sh.[b9], Sh.[b12], Sh.[b15], Sh.[b21], _
                      Sh.[b33], Sh.[b27], Sh.[j30], Sh.[f24], Sh.[h24], Sh.[d18])
        Else
            With A_LBVN.ListObjects(1)
                .ListRows.Add
                .ListRows(.ListRows.Count).Range.Columns(1).Resize(, 13) = _
                Array(Sh.[b6], Sh.[b30], Sh.[b18], Sh.[b9], Sh.[b12], Sh.[b15], Sh.[b21], _
                      Sh.[b33], Sh.[b27], Sh.[j30], Sh.[f24], Sh.[h24], Sh.[d18])
            End With
        End If
    End If
    
End Sub

Display More

**KjBox** · Nov 27th 2016

Re: I'm trying to set up a loop to pull data from multiple cells in multiple workshee

I agree with Trebor, the entire structure of this workbook needs to be revised. A size of 100MB for the amount of data involved is crazy.

As Trebor says you could have just 1 sheet for raw data for all the vehicles and a Summary sheet which could use formulas to auto-update.

If you really need photos of every vehicle then have all images stored in a separate file and have a button to find and show the photo for a selected vehicle.

Doing that would reduce the Excel file size for all 250+ vehicles to around 500KB at the most.

**Trebor76** · Nov 27th 2016

Re: I'm trying to set up a loop to pull data from multiple cells in multiple workshee

See what you think of my INDIRECT formula solution on the attached.

cshortt · Nov 27th 2016

Re: I'm trying to set up a loop to pull data from multiple cells in multiple workshee

KjBox,

WOW! SUPER close! Worked fine in the sample you made, but, when I installed the code on the actual database, I get a "Run-Time Error '1004': Sort Method Of Range Class Failed."[ATTACH=CONFIG]70720[/ATTACH]

I'm trying to set up a loop to pull data from multiple cells in multiple worksheets

Participate now!

Share