Copy & Paste Different Range of Cells from 7 Sheets

KJones97

New Member
Joined
Jul 2, 2019
Messages
9
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.
 

Some videos you may like

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
Try this

Code:
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

New Member
Joined
Jul 2, 2019
Messages
9
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,594
Office Version
  1. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Sep 5, 2004
Messages
1,690
Office Version
  1. 2013
  2. 2007
Platform
  1. Windows

ADVERTISEMENT

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

KJones97

New Member
Joined
Jul 2, 2019
Messages
9
I thought that my second thread had a better description of the actions I wanted to perform.
 

KJones97

New Member
Joined
Jul 2, 2019
Messages
9
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?
 

Watch MrExcel Video

Forum statistics

Threads
1,123,366
Messages
5,601,210
Members
414,434
Latest member
Riyen

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top