How copy range of data based on header name

[raghavat]

TRYING TO COPY RANGE OF DATA BASEDON HEADER
Dim r, a As Long
a = 1
Do While Sheets("Address123").Cells(a, 1).Value > 0
r = r + 1
a = a + 1
Loop
Sheets("Address123").Select
Address = WorksheetFunction.Match("Address", Rows("1:1"), 0)

Sheets("Address123").Select
Range(" Address:a").Select (error)
Selection.Copy
Sheets("Loc").Range("D1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

While trying like dis facing error.can anyone help me out?

 

[Ralajer]

Welcome to the board raghavat,

The problem is that you want to use the variables' values but because they are in quotes "" the are just interpreted as the string " Address:a".
So instead of Range(" Address:a").Select use Cells(a, Address).Select