Copy a static named range and paste it to a variable cell adress in another worksheet

Question

In Worksheet A, I have a single column of meter readings with a static named range "MeterData". The first row of the range is the date (mm/dd/yy) when the meters were read. The remaining rows contain the meter readings.

Immediately above the range, in the same column is the Month, January thru December, which I select from a drop-down list.

In Worksheet B, I have 12 columns with headings January to December respectively. If, for example, the month selected in Worksheet A is February, then I want the named range to be copied and then pasted under the February heading in Worksheet B. The first heading (January) in worksheet B is in cell G4.

There is good reason why the meter readings are not directly entered in Worksheet B.

Any help with VBA would be very much appreciated. I am a true novice and am slowly learning to copy and adapt short VBA solutions, but don’t really know much more. Thanks for your time and knowledge.

CLR CLR · Accepted Answer · 2017-01-20T16:21:43

Try this. I've commented the code to explain what it's doing so you might better tailor it to your requirements.

'Examine cell above 'MeterData' range - store in mth
mth = Range("MeterData").Offset(-1, 0).Cells(1, 1).Text

'Set destination column to 20 as default
paste_column = 20
'Skip on error as Match might not find the month(?) so will remain at 20
On Error Resume Next
'Re-set destination to column using Match to find occurence of 'mth' on row 4
paste_column = WorksheetFunction.Match(mth, Sheets("WorksheetB").Range("4:4"), 0)

Remove error skipping
On error goto 0

'Copy 'MeterData' range and Paste to WorksheetB, row 5, column found above.
Range("MeterData").Copy Destination:=Sheets("WorksheetB").Cells(5, paste_column)

EDIT: Now with Copy/Paste Special Values

'Examine cell above 'MeterData' range - store in mth
mth = Range("MeterData").Offset(-1, 0).Cells(1, 1).Text

'Set destination column to 20 as default
paste_column = 20
'Skip on error as Match might not find the month(?) so will remain at 20
On Error Resume Next
'Re-set destination to column using Match to find occurence of 'mth' on row 4
paste_column = WorksheetFunction.Match(mth, Sheets("WorksheetB").Range("4:4"), 0)

'Remove error skipping
On Error GoTo 0

'Copy 'MeterData' range into buffer
Range("MeterData").Copy

'Paste to WorksheetB, row 5, column found above.
Sheets("WorksheetB").Cells(5, paste_column).PasteSpecial Paste:=xlPasteValues

'Remove highlighter identifying copied area
Application.CutCopyMode = False

Copy a static named range and paste it to a variable cell adress in another worksheet

1 Answers