How to reference a cell in VBA using values in another cell

Question

I need to copy a range of data from another sheet that contains the month of the data that is in say cell A1. So I have Feb 2017 in cell A1 and a row of data in another sheet from 1 Jan 2017 to 31 Dec 2017 with the dates is row A and the data in the rows below. I want to create a macro that whenever I run it, it will read cell A1 and if it is Feb 2017, it will copy the columns of data from 1 Feb 2017 to 28 Feb 2017 and if the cell A1 is March 2017, it will copy from 1 March 2017 to 31 March 2017. How can i do that?

I recorded the macro but the cell referenced is locked so it will always copy from 1 Feb 2017 to 28 Feb 2017 which is not I wanted.

Windows("MASTERFILE.xlsm").Activate
Range("FA2:FT2").Select
Selection.Copy
Windows("NEW_FILE.xlsm").Activate
Sheets("borrowing").Select
Range("B2").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Windows("MASTERFILE.xlsm").Activate
Range("FA25:FT28").Select
Application.CutCopyMode = False
Selection.Copy
Windows("NEW_FILE.xlsm").Activate
Range("B3").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
Sheets("Main Page").Select

End Sub

Please help. Thank you!

Alex P Alex P · Accepted Answer · 2017-03-13T08:57:24

This may help.

Sub GetDataByMonth()
    Dim mth As Integer, year As Integer, data As Range, item As Range

    With Worksheets("Sheet1")
        mth = VBA.DatePart("m", .Range("A1"))
        year = VBA.DatePart("yyyy", .Range("A1"))
    End With

    Set data = Worksheets("Sheet2").Range("A1:A200")

    For Each item In data
        If VBA.DatePart("m", item) = mth And VBA.DatePart("yyyy", item) = year Then
            Range(Cells(Item.Row, 2), Cells(Item.Row, 40)).Copy
            Item.Offset(0, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End If
    Next item
End Sub

Assumes your dates are formatted as dates in the s/sheet
It copies each line at a time.
Assumes the data you want to copy in column B (2) to AN (40)

How to reference a cell in VBA using values in another cell

1 Answers