How do I search for text in a row and then copy range of values from a range of cells?

[jbillyo]

Hello,
I'm still learning to use excel.

The frustrating thing is I know what I want to do but don't yet have the knowhow to do it.
I hope this makes sense.

I have a spreadsheet that has multiple worksheets.
In each worksheet there is a column which contains the text "Total", I also know that it will be in row 7 but it varies in each worksheet.
I then want to copy a range of cells in the "total" column ?9:?39 into another worksheet which has copied the "total" value from each row into a totals worksheet.

Thanks in advance
John

 

[jbillyo]

Thank you for those that have looked into my question.
Can I try to clarify again

I want to find the cell that contains "TOTAL" It is in row 7 but not always the same column.
I then want to copy that column data (wherever it is) using the column range ?9:?39 onto another worksheet.
Is this something that is possible?

Thanks again
John

 

[DanteAmor]

Try the following:

Sub Copy_Range()
    Dim h As Worksheet, ws As Worksheet
    Dim j As Long, b As Range
    '
    Application.ScreenUpdating = False
    Set h = Sheets("total")
    h.Rows("2:" & Rows.Count).ClearContents
    j = 1
    For Each ws In Sheets
        If h.Name <> ws.Name Then
            Set b = ws.Rows(7).Find("Total", LookIn:=xlValues, lookat:=xlWhole)
            If Not b Is Nothing Then
                ws.Range(ws.Cells(9, b.Column), ws.Cells(39, b.Column)).Copy
                h.Cells(2, j).PasteSpecial xlValues
                j = j + 1
            End If
        End If
    Next
    Application.ScreenUpdating = True
    Application.CutCopyMode = False
    MsgBox "End"
End Sub

 

[jbillyo]

Thank you Dante,
Unfortunately it returns a runtime error 9 subscript out of range.
I have tried the code on the "Data Entry" sheet where the totals are. I have also tried it on the raw data sheet where the formula calculates the sum of raw score.
Cheers

 

[DanteAmor]

In this line you have to put the name of your sheet "Total" (target sheet)

Set h = Sheets("total")

 

[jbillyo]

Hi Dante,
I've had a play around with it but I can't seem to get the macro to return anything on the Data Entry ws

Thanks
John

 

[DanteAmor]

You could upload a copy of your file to a free site such as www.box.com or www.dropbox.com. Once you do that, mark it for 'Sharing' and you will be given a link to the file that you can post here. If the workbook contains confidential information, you could replace it with generic data.



In the file, give an example of what should be copied

 

[jbillyo]

I have added some notes on the Data Entry worksheet of what I am trying to do. I hope this makes sense. Apologies if this is a little clunky but it's where I am at with Excel skills.
https://www.dropbox.com/s/3gz8irvgrwa4c8z/Rising Stars Arithmetic Year 5 V2.xlsm?dl=0

Thanks for your help
John

 

[DanteAmor]

In your book use the following

Sub Copy_Range()
    Dim h As Worksheet, j As Long, s As Long
    '
    Application.ScreenUpdating = False
    Set h = Sheets("Data Entry")
    h.Range("G9:AP39").ClearContents
    j = Columns("G").Column
    For s = h.Index + 1 To Sheets.Count
        Set b = Sheets(s).Rows(7).Find("Total", LookIn:=xlValues, lookat:=xlWhole)
        If Not b Is Nothing Then
            h.Range(h.Cells(9, j), h.Cells(39, j)).Value = _
                Sheets(s).Range(Sheets(s).Cells(9, b.Column), Sheets(s).Cells(39, b.Column)).Value
            j = j + 1
        End If
    Next
    Application.ScreenUpdating = True
    MsgBox "End"
End Sub

 

[jbillyo]

It has come up with a compile error variable not found. Do I have to put in the range of cells to copy? If so, how do i do that if it isn't always the same cells - can it do it automatically?