Now I'm working on a macro to find and replace column headers in a
spreadsheet that the raw data I use in XL comes from. I've only got a
week left on my contract with the state which may be enough time to get
another set of data out to the counties. (Have applied for a full time
job here, so I might be back.)
I have 2 columns of data, starting at B40, in one workbook, call it
ABC. Column B contains the Source document headers I want to replace in
the other workbook, and Column C has the headers I want to replace them
with. I want to select the downloaded data worksheet, then run the
macro to go the the other workbook, get the old header in cell B40, try
to find it in row 1 in the downloaded datasheet and replace it with the
text in C40. Then go to B41, and repeat the procedure until it hits a
blank cell in column B. If it fails to find the text from column B,
just continue with the next cell in the column.
This will make reformatting the incoming datasheets much quicker, so I
can then copy them into the existing workbook with the new quarter's
data.
Can anyone point me in the right direction as to how to set this up? I
appreciate the help.

Try using a VLOOKUP worksheet function it may be quicker and easier than
coding a macro.

You'll have to put the VLOOKUP in the a cell next to the old header and
then you can cut and replace the Old Header with the New Header.

=VLOOKUP(A6,[sourceworkbook.xls]Sheet3!$A$1:$B$25,2,FALSE)

change the reference in red to the sourceworkbook, sheet and range
where the new headers need to be pulled from.


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=381426

Dave

As suggested above a VLOOKUP will do this for you with ease. You could
however put the vlookups in a new row below the original headers in which
case you just need to add one row rather than multiple columns.

If you want to do it with a macro then it would look something like this
(assuming the data and headers are in sheet 1 of the respective workbooks):

Sub Chng_Header()

Dim foundHead As Range
Dim oHeads As Range
Dim cell As Range
Dim oldHead As String
Dim newHead As String
Dim endRow As Long
Dim Headers As Workbook
Dim Data As Workbook

Set Headers = Workbooks("ABC.xls")
Set Data = Workbooks("Data.xls")

endRow = Headers.Sheets(1).Cells(Rows.Count, 2).End(xlUp).Row
With Headers.Sheets(1)
Set oHeads = Range(.Cells(40, 2), .Cells(endRow, 2))
End With

For Each cell In oHeads
oldHead = cell.Value
newHead = cell.Offset(0, 1).Value
With Data.Worksheets(1).Range("A1:J1") 'Headers to be replaced
Set foundHead = .Find(oldHead, LookIn:=xlValues)
End With
If Not foundHead Is Nothing Then
foundHead.Value = newHead
End If
Next cell

End Sub

Regards
Rowan

"bhofsetz" wrote:


Try using a VLOOKUP worksheet function it may be quicker and easier than
coding a macro.

You'll have to put the VLOOKUP in the a cell next to the old header and
then you can cut and replace the Old Header with the New Header.

=VLOOKUP(A6,[sourceworkbook.xls]Sheet3!$A$1:$B$25,2,FALSE)

change the reference in red to the sourceworkbook, sheet and range
where the new headers need to be pulled from.


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=381426

Rowan wrote:
Dave

As suggested above a VLOOKUP will do this for you with ease. You could
however put the vlookups in a new row below the original headers in which
case you just need to add one row rather than multiple columns.

If you want to do it with a macro then it would look something like this
(assuming the data and headers are in sheet 1 of the respective workbooks):

Sub Chng_Header()

Dim foundHead As Range
Dim oHeads As Range
Dim cell As Range
Dim oldHead As String
Dim newHead As String
Dim endRow As Long
Dim Headers As Workbook
Dim Data As Workbook

Set Headers = Workbooks("ABC.xls")
Set Data = Workbooks("Data.xls")

endRow = Headers.Sheets(1).Cells(Rows.Count, 2).End(xlUp).Row
With Headers.Sheets(1)
Set oHeads = Range(.Cells(40, 2), .Cells(endRow, 2))
End With

For Each cell In oHeads
oldHead = cell.Value
newHead = cell.Offset(0, 1).Value
With Data.Worksheets(1).Range("A1:J1") 'Headers to be replaced
Set foundHead = .Find(oldHead, LookIn:=xlValues)
End With
If Not foundHead Is Nothing Then
foundHead.Value = newHead
End If
Next cell

End Sub

Regards
Rowan


Thanks Rowan and bhofsetz!
I prefer to use a macro for a couple of reasons. One, I'm trying to
learn VBA, I need to know it for this job. 2, most of what I'm doing,
though not specifically this part, is going to people who are total XL
novices and need things as totally automated as possible. 3, I'm hoping
to train someone to do these sheets before I leave, so if I don't get
the job here, the counties will still be able to get this data in a
very useful form. That has to be very automated.
I'm trying to get the macro to run. I've modified it from what you
sent, Rowan, to make the variable names consistent with my naming
system. It's not working at the line I understand the least.

Sub Chng_Header()


Dim rFoundHd As Range
Dim rOldHds As Range
Dim cell As Range
Dim strOldHd As String
Dim strNewHd As String
Dim endRow As Long
Dim HeadersBk As Workbook
Dim DataBk As Workbook


Set HeadersBk = Workbooks("Test Overall Statewide and County
Percentages.xls")
Set DataBk = ActiveWorkbook


endRow = HeadersBk.Sheets(19).Cells(Rows.Count, 2).End(xlUp).Row
With HeadersBk.Sheets(1)
Set rOldHds = Range(.Cells(40, 2), .Cells(endRow, 2))
End With

rOldHds.Select 'TEMP


For Each cell In rOldHds
strOldHd = cell.Value
strNewHd = cell.Offset(0, 1).Value
With DataBk.Worksheets(1).Range("A1:P1") 'Headers to be
replaced
Set rFoundHd = .Find(strOldHd, LookIn:=xlValues)
End With
If Not rFoundHd Is Nothing Then
rFoundHd.Value = strNewHd
End If
Next cell


End Sub

The range rOldHds is not anything. Notice I put in, temporarily, the
rOldHds.Select command to see what it did. I get the error message that
select method failed. So that range is not being set. And since I have
no idea what

Set rOldHds = Range(.Cells(40, 2), .Cells(endRow, 2))

does, other than set a range, so I can't debug it.
Can you see what's wrong?
Thanks for the help.

Dave,
You are getting the error message on this line

rOldHds.Select 'TEMP

becase the workbook and worksheet on which you are trying to select
that range are not the active book and sheet.
You would either have to manually select that book and sheet or do it
in the code with:

HeadersBk.Sheets(1).Activate
then
rOldHds.Select 'TEMP

I also noticed that you are counting rows on Sheet(19) and then
selecting the range on Sheet(1). Is this correct?


--
bhofsetz
------------------------------------------------------------------------
bhofsetz's Profile: http://www.excelforum.com/member.php...o&userid=18807
View this thread: http://www.excelforum.com/showthread...hreadid=381426

bhofsetz wrote:
Dave,
You are getting the error message on this line

rOldHds.Select 'TEMP

becase the workbook and worksheet on which you are trying to select
that range are not the active book and sheet.
You would either have to manually select that book and sheet or do it
in the code with:

HeadersBk.Sheets(1).Activate
then
rOldHds.Select 'TEMP

I also noticed that you are counting rows on Sheet(19) and then
selecting the range on Sheet(1). Is this correct?


Thanks! It's working fine now.