Select Range within Row of Active Cell

Question

I've seen several other questions similar to mine and I've tried several different solutions but I am still getting strange results. My code finds a value in another workbook in Column AA, then I want to copy that row from Column C to Column BC and paste in current workbook. All of the code works except copying from column C to BC. For some reason it starts copying the row from column AC. I've tried a standard range but I think it's relative from the active cell and I don't know if there is a way to do negative column letters so then I tried Offset and I tried .Cells but none select the correct range. Here is a couple of examples of the code I've tried:

Private Sub ComboBox1_Change()

Dim checknum As String
Dim chkrow As String
Dim Rng As Range

prfile1 = Worksheets("setup").Range("B10").Value
prfile2 = Worksheets("setup").Range("B7").Value
filepath = Worksheets("setup").Range("e10").Value

checknum = ComboBox1.Value

'Workbooks.Open filepath & prfile2
Windows(prfile2).Activate
Worksheets("MRegister").Select

With Worksheets("MRegister").Range("AA:AA")
    Set Rng = .Find(What:=checknum, _
                        After:=.Cells(.Cells.Count), _
                        LookIn:=xlFormulas, _
                        LookAt:=xlWhole, _
                        SearchOrder:=xlByRows, _
                        SearchDirection:=xlNext, _
                        MatchCase:=False)
            Rng.Select
            .Range(.Cells(ActiveCell.Row, -24), .Cells(ActiveCell.Row, 28)).Select
            Selection.Copy
End With

Windows(prfile1).Activate
Sheets("ReprintOld").Range("M203:BM203").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

Application.CutCopyMode = False
Windows(prfile2).Activate
Sheets("MRegister").Range("A1").Select
ActiveWorkbook.Saved = True
ActiveWorkbook.Close
Sheets("ReprintOld").Range("A1").Select

End Sub

for Offset:

.Range(ActiveCell.Offset(0, -24), ActiveCell.Offset(0, 28)).Select

for standard Range:

.Range("C" & ActiveCell.Row & ":BC" & ActiveCell.Row).Select

You would think all of these would work, but they all start the selection several columns to the right of the active cell.

You could just use: Intersect(Rg.Entirerow, .Range("C:BC")).Copy — Rory
I like the simplicity of that, but like all of the others I've tried, it also copies from AC:CC. It's very strange. — Chris L
Copying the entire row works, but any type of range is weird. — Chris L
I didn't see you had a Range on the end of the With block. You'd need: Intersect(Rg.Entirerow, .Worksheet.Range("C:BC")).Copy — Rory
That worked great! Ty. post it as an answer and I'll mark it correct. Now can you go through the rest of my 40 pages of code and simplify all of it for me?? lol — Chris L

Rory Rory · Accepted Answer · 2018-10-24T14:44:40

The issue is, as has been mentioned by user3561813, the fact that you have a Range object on the end of your With statement. Perhaps the simplest solution would be to use:

Intersect(Rng.Entirerow, .Worksheet.Range("C:BC")).Copy

Select Range within Row of Active Cell

3 Answers