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

jbillyo

New Member
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.

John

Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.
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

Try the following:

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

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

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

Code:
``Set h = Sheets("total")``

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

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

In your book use the following

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

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?

Replies
1
Views
114
Replies
0
Views
113
Replies
1
Views
107
Replies
5
Views
509
Replies
3
Views
83

1,203,483
Messages
6,055,678
Members
444,807
Latest member
RustyExcel

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.

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

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