Selecting a specific range based on a stored value

[_Echo_2]

Hello,

I'm new to VBA and have been developing a macro for a database. I've completed about 95% of it and it all works successfully.

However, I'm having trouble with the last thing I want it to do, which is replacing data.

So here is the situation:

I have a form and a 'master' sheet. The master sheet is a collection of certain information from all the different forms (all forms have their own individual sheets in the workbook). The master is sorted alphanumerically by the data in column H. Data is on the master sheet from row 11 downwards (rows are being added and sorted every time a form is made) and from columns B to AC.

Lets say on the master sheet there are 3 rows in column H that have the same value. I want the macro to search column H for all instances of that value (it should find 3 cells). I want it to then select the range as follows: the row and column B of the first time the value appears (B14) to the row and column AC of the last time the value appears (AC16). So it looks like this:

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]...[/TD]
[TD]H[/TD]
[TD]...[/TD]
[TD]AB[/TD]
[TD]AC[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD]M1[/TD]
[TD][/TD]
[TD]V0[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD]M2[/TD]
[TD][/TD]
[TD]V1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD]M3[/TD]
[TD][/TD]
[TD]V1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD]M4[/TD]
[TD]data[/TD]
[TD]V2[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD]M5[/TD]
[TD]data[/TD]
[TD]V2[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]16[/TD]
[TD][/TD]
[TD]M6[/TD]
[TD]data[/TD]
[TD]V2[/TD]
[TD]data[/TD]
[TD]data[/TD]
[TD]data[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD][/TD]
[TD]M7[/TD]
[TD][/TD]
[TD]V3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]...[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]x[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
So referring to the table above, I want the macro to find all "V2" (V2 would be a stored value) in column H then select the range B14:AC16.

Below is the code that I have that precedes the step I'm asking about above. The Do Until Empty loop will create a block of data on the sheet "Temp." The block of data is the same size as the range I want to select on the master sheet (i.e. 3 rows, 28 columns). I want to copy the block of data on "Temp" and then paste it into the selected range on the master (i.e. 'Pasting over' or replacing the data on the master). strName is the stored value (V2 in the example table).
_____________________________
Sub Replace_Test()

Dim strName As String

strName = ActiveSheet.Range("C2")​

Dim ws As Worksheet

With ThisWorkbook
Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))

ws.Name = "Temp"​

End With

Sheets(strName).Select

Ct = 20

Do

Range("C" & Ct, "F" & Ct).Select
Selection.Copy
Range("P2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
​

:=False, Transpose:=True​

Application.CutCopyMode = False
Range("P2:P29").Copy
Sheets("Temp").Activate
Range("A" & Rows.Count).End(xlUp).Offset(1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
​

:=False, Transpose:=True​

Application.CutCopyMode = False
Sheets(strName).Select
Ct = Ct + 1
​

Loop Until IsEmpty(Cells(Ct, 3))

Sheets("Temp").Activate
Range(Range("A2"), Range("AB2").End(xlDown).End(xlToRight)).Select
Selection.Copy
Sheets("Master").Activate

??? Search col H for strName
??? Select range as intended

Paste

End Sub
_____________________________



If anything is unclear or I could explain things better please let me know. As I said, I'm learning VBA and this is my first post on the forum.

Thank you!