How to copy data from one worksheet to another (using an indirect reference for the latter worksheet)

Question

I'm trying to add some simple VBA functions to excel files at work (i work for a marketing company) and am struggling to code a button that can copy data from one sheet to another (the position it needs to copy to changes daily)

I'm using VBA to try and select the range of values i need copied (and it works) but i'm struggling to paste them in the daily-changing cells required. I've tried writing a formula that presents a pathway to the correct cells and then introducing an indirect function to the VBA code for pasting but i can't seem to get it to work.

Sub Gross_Wrap_Click()

Sheets("AUTOM").Select
Range("C1:C5").Select
Selection.Copy

Sheets("Daily").Select
Range([indirect("'AUTOM!O7'")]).Select
Selection.Paste

End Sub

I expect the end product to be the contents of cells C1:C5 in the AUTOM sheet copied and pasted to the required cells in the DAILY sheet (the required cells are derived from a function and included in VBA with an Indirect pathway).

However, i'm just getting a load of different error messages

Should the cells be pasted next to a certain date (e.g. today or yesterday) on the Daily sheet? — BigBen
@BigBen Yes, not in a cell directly next to the date, but in the same column as yesterdays date — Liam

Damian Damian · Accepted Answer · 2019-05-15T14:43:59

If I didn't messed it reading your code, this should do it:

Option Explicit
Sub Test()

    Dim rng As Range 'Range object, means a cell or a range of cells
    With ThisWorkbook 'here you reference the workbook, so you don't need to write Thisworkbook everytime, just "."
        Set rng = .Sheets("Daily").Find(.Sheets("AUTOM").Range("O7")) 'we set the object rng to the cell in sheets Daily which has the Cell O7 from AUTOM sheet value.
        .Sheets("AUTOM").Range("C1:C5").Copy 'copy the range from AUTOM
        rng.PasteSpecial xlPasteValues  'and paste it's values to the range object we declared and defined above
    End With

End Sub

How to copy data from one worksheet to another (using an indirect reference for the latter worksheet)

1 Answers