Copy & Paste Different Range of Cells from 7 Sheets

[KJones97]

Hi all, Thank you in advance for the help.

I am looking to create a macro that will bring in all cells (A:X) for every row that's value in Column A is "BONDS". However "Bonds" appears in A column cells before the data appears but I want all the "BONDS" lines below the merged header (A:X) that reads DETAIL. This header is in different locations for each sheet which is the problem I have run into.

I would like to copy every every row until the cell in A is blank.

This will happen on 6 sheets: Sheet2, Sheet3, Sheet4, Sheet5, Sheet6, Sheet7

Next I would like this selected data to be pasted into Sheet "Consolidated" Range B:Y with cells in the A column being filled with the name of the corresponding sheet which the row was copied from.

I hope this is enough information to get this done.

 

[DanteAmor]

Try this

Sub Copy_Paste_Different_Range()
    Dim sc As Worksheet, sh As Worksheet, f As Range, r As Long, r2 As Long
    
    Application.ScreenUpdating = False
    Set sc = Sheets("Consolidated")
    For Each sh In Sheets(Array("Sheet2", "Sheet3", "Sheet4")) ', "Sheet5", "Sheet6", "Sheet7"))
        Set f = sh.Rows(1).Find("DETAIL", LookIn:=xlValues, lookat:=xlWhole)
        If Not f Is Nothing Then
            sh.Range("A1:X" & sh.Range("A" & Rows.Count).End(xlUp).Row).AutoFilter f.Column, "BONDS"
            sh.Range("A2:X" & sh.Range("A" & Rows.Count).End(xlUp).Row).Copy
            r = sc.Range("B" & Rows.Count).End(xlUp).Row + 1
            sc.Range("B" & r).PasteSpecial xlPasteValues
            r2 = sc.Range("B" & Rows.Count).End(xlUp).Row
            sc.Range("A" & r & ":A" & r2).Value = sh.Name
            sh.ShowAllData
        End If
    Next
End Sub

 

[KJones97]

Hi Dante,
The inbox for my account does not work properly so I could not view your response. Would it be possible to have our conversation through this thread.

 

[DanteAmor]

Hi Dante,
The inbox for my account does not work properly so I could not view your response. Would it be possible to have our conversation through this thread.

Please, all clarifications must be made in the thread.

According to your requirement in the header you must have a title with the text "DETAIL", I assumed that your header is in row 1.

Please Note
-----------------------
One thing you must keep in mind when you ask a question in a forum... the people you are asking to help you know absolutely nothing about your data, absolutely nothing about how it is laid out in the workbook, absolutely nothing about what you want done with it and absolutely nothing about how whatever it is you want done is to be presented back to you as a result... you must be very specific about describing each of these areas, in detail, and you should not assume that we will be able to "figure it out" on our own. Remember, you are asking us for help... so help us to be able to help you by providing the information we need to do so, even if that information seems "obvious" to you (remember, it is only obvious to you because of your familiarity with your data, its layout and the overall objective for it).

 

[jolivanes]

Why did you start a new thread if you had one going?

 

[KJones97]

I thought that my second thread had a better description of the actions I wanted to perform.

 

[KJones97]

Sorry to both of you Dante and Jolivanes,

I was unaware of the cross posting and only decided to post another column because I thought I could better describe my issue than what Is written above. However, I understand the issue with cross posting and will try to further describe my issue and the pertinent information required to solve it in hopes someone will still help me. Here is a link to my description in the other thread (That I believe is stronger): https://www.mrexcel.com/forum/excel-questions/1103168-find-select-rows-wont-always-same-place.html

Basically, the issue is that the source sheets I want to copy the data from pull new data from other workbooks monthly which means the data I need for this operation wont always be in the same location (Either between other sheets or the same sheet month over month). However, there are always a few constants. The data I want (A:X) will always contain the word "BONDS" in column A for every row I want to pull from each sheet; and, the rows will always be underneath a merged title slide (A:X) that reads "DETAIL" but this merged row isn't always in the same location either. This merged row is useful because there are rows above the "DETAIL" title that include "BONDS" in column A that I don't want to copy over to the "Consolidation" sheet. As well there are rows below the "DETAIL" title that are either blank in column A or have "CASH" written, neither of which I want copied. This is where I am having most of my difficulties figuring out the dynamic aspects of the data I wish to copy paste.

Here are a few other aspects of the workbooks that might be helpful:
- All the rows I wish to pull are clumped together. So the first cell in column A after x amount of rows containing "BONDS" Is the end of the list.

I am new to the forum and am slowly teaching myself the VBA application, any help or advice is much appreciated.

KJones97

Ps. I find typing in the comment box quite tedious as it often misses letters due to auto-save. But I am not able to copy paste from other locations. Is there a better way to approach this?