CODEWITHSUNDEEP
excelvbafor-looprange
user2676066
user2676066

Reputation: 1

How to specify a range that is a specific row and specific columns?

I search Column A for a specific value. I want copy the row, that contains the value, from columns C to O, to another sheet.

The worksheet labeled Data is where I am pulling information from columns C to O.

The worksheet labeled Sales is where I want the data pasted.

Sub Test_Copy_Data()
    'Macro to copy sales based on site

    '**********************************************************
    Dim numentries As Integer
    Dim i As Integer
    Dim site As String

    '***********************************************************
    'Define sheet names
    Data = "Data1"
    Sales = "Sales"

    '************************************************************
    'Get number of entries and desired scores
    numentries = Worksheets(Data).UsedRange.Rows.Count

    '*************************************************************

    site = Worksheets(Sales).Range("B1").Value

    '************************************************************

    'Run loop to cycle through all entries (rows) to copy

    For i = 1 To numentries
        If (Worksheets(Data).Cells(i + 2, 1).Value = site) Then
            Worksheets(Data).Range("C:O" & i + 2).Copy _
              Destination:=Worksheets(Sales).Range("A2")     
        End If
    Next i

End Sub

Upvotes: 0

Views: 8903

Answers (1)

Jaycal
Jaycal

Reputation: 2087

Your reference to the range on your Data1 worksheet is incorrect. Update

Worksheets(Data).Range("C:O" & i + 2).Copy Destination:=_
    Worksheets(Sales).Range("A2")

to...

Worksheets(Data).Range("C" & i + 2 & ":O" & i + 2).Copy Destination:=_
    Worksheets(Sales).Range("A2")

and it should work. Note, however, that every time this codes runs, it will paste the found value from the Data sheet into the same location on the Sales sheet (cell A2), so just a guess, but you may want to update this reference as well.

(e.g. Worksheets(Sales).Range("A" & i + 1))

Upvotes: 1

Related Questions