VBA loop to collect data from all sheets

[CalRich1023]

Hello,

I need to search through all the sheets in my workbook and gather data from a range of cells, and return that to a "Master" sheet. The range will run from cells "J9:M9", but will vary in amount of rows for each sheet (maybe 1 row, maybe 100+ rows). Obviously I'd like to omit searching on the "Master" sheet also.

Could anybody help me with this?

 

[My Aswer Is This]

Are you wanting to search column J to M starting in row 9 of each sheet all the way down to last value in each column?

And what are we searching for?

And return the results to where on sheet named Master

To help we need specific details like this.

 

[CalRich1023]

Are you wanting to search column J to M starting in row 9 of each sheet all the way down to last value in each column?

And what are we searching for?

And return the results to where on sheet named Master

To help we need specific details like this.

Basically "J9" is an account number. The following columns ("K:M")are additional info about that account. I would like to return all of that additional info along with the account number.

Are you wanting to search column J to M starting in row 9 of each sheet all the way down to last value in each column? Yes. I would like to see all accounts (and additional acct info) that appear on each sheet. Some sheets will only have 1 account. other sheets may have 100+ accounts.

And return the results to where on sheet named Master? Cells "A2:D2" would be fine on the Master.

 

[My Aswer Is This]

Try this:
Run this script from a button on the sheet named Master which I assume is the first sheet in the workbook. If not I will have to modify the script.

Sub Copy_My_Range()
'Modified 4/2/2021  3:45:52 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrow = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1

For i = 2 To Sheets.Count
    Lastrow = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1

    With Sheets(i)
        Lastrowa = .Cells(Rows.Count, "J").End(xlUp).Row
        .Cells(9, "J").Resize(Lastrowa, 4).Copy Sheets("Master").Cells(Lastrow, 1)
    End With
Next
Application.ScreenUpdating = True
End Sub

 

[CalRich1023]

Try this:
Run this script from a button on the sheet named Master which I assume is the first sheet in the workbook. If not I will have to modify the script.

Sub Copy_My_Range()
'Modified 4/2/2021  3:45:52 PM  EDT
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrow = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1

For i = 2 To Sheets.Count
    Lastrow = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1

    With Sheets(i)
        Lastrowa = .Cells(Rows.Count, "J").End(xlUp).Row
        .Cells(9, "J").Resize(Lastrowa, 4).Copy Sheets("Master").Cells(Lastrow, 1)
    End With
Next
Application.ScreenUpdating = True
End Sub

I just realized this may be tougher than I thought. This brings all rows with any formulas (which is 1500 rows/sheet). All of column J has IFERROR(VLOOKUP()"") formulas in it. So excel will recognize that as something it should be copying over to the master sheet, right? The IFERROR returns a blank ("") value if it is false, so it appears empty to the eye. Is there a way to only pull the cells that have actual visible data in them? If that makes sense?

 

[My Aswer Is This]

Your original post said:
Basically "J9" is an account number
so why does a account number result in iferror
So can we just look down another of the columns for a lastrow?
Like columns K L or M

 

[CalRich1023]

Your original post said:
Basically "J9" is an account number
so why does a account number result in iferror
So can we just look down another of the columns for a lastrow?
Like columns K L or M

Unfortunately, all of the columns J:M are linked to, pulling data, & consolidating information from earlier cells (A:C) in the sheet. I need them to be formulas for this reason.

 

[My Aswer Is This]

Unfortunately, all of the columns J:M are linked to, pulling data, & consolidating information from earlier cells (A:C) in the sheet. I need them to be formulas for this reason.

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

 

[CalRich1023]

This is beyond my knowledgebase.
I will continue to monitor this thread to see what I can learn.

Okay, thanks for the attempt though! I appreciate it!

 

[Fluff]

Try changing this line

Lastrowa = .Cells(Rows.Count, "J").End(xlUp).Row

to

Lastrowa = .Range("J:J").Find("*", , xlValues, , xlByRows, xlPrevious, , , False).Row