CODEWITHSUNDEEP
vbaexcelcopyfind
Carol.Kar
Carol.Kar

Reputation: 5345

Copying data to another sheet

I am having trouble understanding what the below function is doing.

The function itself has the ability to copy data to the sheet Sheet History. However, I do not get how it is doing it?

Sub histFunc()

Dim Y As String
Y = "R" & Range("G7").Value

    Sheets("Sheet History").Select
    Range("h17").Select

    Cells.Find(What:=Y, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
        , SearchFormat:=False).Activate
    Range(Selection, Selection.End(xlToRight)).Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False
    Sheets("Sheet Current").Select

End Sub

Any suggestions how this macro is operating?

I appreciate your replies!

Upvotes: 0

Views: 86

Answers (2)

agold
agold

Reputation: 6276

In short, the code searches for the value in G7 in Sheet History and replaces the right part of that line with values only, i.e. removing references or values.

Step-by-step Explanation

Get the value of cell G7:

Y = "R" & Range("G7").Value

Select sheet Sheet History and select cell H17:

Sheets("Sheet History").Select
Range("h17").Select

Executes the Find method over Cells, all cells in the sheet (note that if no parameter is given it is the range of all Cells in the current Sheet):

Cells.Find(What:=Y, After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _
    xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:=False _
    , SearchFormat:=False).Activate

This returns:

A Range object that represents the first cell where that information is found.

For more info see the Find documentation. Now due to .Activate the (first) cell is selected where the value was found. This selection is extended to the end of the line:

Range(Selection, Selection.End(xlToRight)).Select

Then the CutCopyMode is deactivated to clear the clipboard after usage:

Application.CutCopyMode = False

Now the selected cells are copied and pasted:

Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False

Note that they are pasted on with PasteSpecial on the same location, using xlPasteValues to only maintain the values, and therefore not to have any formulas nor references in the cells.

Now go to Sheet Current:

Sheets("Sheet Current").Select

Upvotes: 1

R3uK
R3uK

Reputation: 14537

After a bit of cleaning, this is what this could look like (explanations below) :

Sub histFunc()
Dim FindRange As Range, _
    LookForValue As String
LookForValue = "R" & Range("G7").Value

With Sheets("Sheet History")
    .Range("h17").Activate
    Set FindRange = .Cells.Find(What:=LookForValue, _
                    After:=ActiveCell, _
                    LookIn:=xlFormulas, _
                    LookAt:=xlPart, _
                    SearchOrder:=xlByRows, _
                    SearchDirection:=xlNext, _
                    MatchCase:=False, _
                    SearchFormat:=False)

    Application.CutCopyMode = False

    Range(FindRange, FindRange.End(xlToRight)).Copy
    FindRange.PasteSpecial Paste:=xlPasteValues, _
                           Operation:=xlNone, _
                           SkipBlanks:=False, _
                           Transpose:=False
End With

Application.CutCopyMode = False
Sheets("Sheet Current").Select

End Sub

I changed Y to LookForValue for better understanding and used a Range variable to reference the result of the Find method.

So this code, step by step :

  1. Define LookForValue as "R" & Range("G7").Value
  2. Search for that value in the formulas of Sheet History
  3. Copy the data block (in the row of the result, from result to right, until there is a blank)
  4. Paste it at the same place but in values, so that you get rid of the formulas!

Upvotes: 0

Related Questions