Concatenate two rows to one row in multiple worksheets

[Swanson1962]

Hi everyone!

Been searching for hours, everyone has something unique but not quite the same as this.

I have one very large Excel file that has multiple tabs.
Rows 1 and 2 have data all the way across:

<TABLE style="WIDTH: 289pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=385><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 125pt; mso-width-source: userset; mso-width-alt: 6070" width=166><COL style="WIDTH: 116pt; mso-width-source: userset; mso-width-alt: 5668" width=155><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20 width=64></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 125pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=166>Column A</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; WIDTH: 116pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" width=155>Column B</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>Row 1</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Required</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Not Required</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>Row 2</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Name</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Address</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8"></TD></TR></TBODY></TABLE>

I need to have Row 3 concatenated with a dash:

<TABLE style="WIDTH: 289pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=385><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8" height=20>Row 3</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Required - Name</TD><TD style="BORDER-BOTTOM: #ece9d8; BORDER-LEFT: #ece9d8; BACKGROUND-COLOR: transparent; BORDER-TOP: #ece9d8; BORDER-RIGHT: #ece9d8">Not Required - Address</TD></TR></TBODY></TABLE>


Thank you so much in advance for your help --

Lisa

 

[Andrew Poulsom]

Can't you use a formula?

=A1&" - "&A2

 

[jim may]

You mean like....

Excel Workbook
	A	B
1	Required	Not Required
2	Name	Address
3	Required-Name	Not Required-Address
Sheet1

Excel 2007

Cell Formulas
Range	Formula
A3	=A1& "-" &A2
B3	=B1& "-" &B2

 

[JoeMo]

Select all the sheets (select 1st sheet then hold down shift key and select last sheet); then enter in A3

=A1&"-"&A2

and copy across to B3.

 

[Swanson1962]

Hi - Thank you so much for your input... unfortunately trying to put it into a macro... I do have a snippet that works for just one sheet:

X = 1
Do While X < 30
Cells(3, X).Value = Cells(1, X) + " " + Cells(2, X)
X = X + 1
Loop

If you have any suggestions to make it work in an array macro I would really appreciate it.

Thank you

 

[JoeMo]

maybe:

For Each sh in ThisWorkbook.Worksheets
With sh
X = 1
Do While X < 30
    .Cells(3, X).Value = .Cells(1, X) + " " + .Cells(2, X)
    X = X + 1
Loop
End With
Next sh

 

[Swanson1962]

It worked!!! Thank goodnes as I've been googling for hour with no luck, lol.

Thank you, thank you

PS: This is really the best resource site out on the web. I'm always looking in here for answers and thanks to all who imput to help us rookies out --

Have a GREAT night!

 

[Swanson1962]

PS: For others who may look at this thread, the "Do While X < 30" was something I vamped in and it means the code will loop through to the first 29 columns. I suppose you can put it to what you want and in most instances it's Do While X <> "" (looking for the first empty space).

Here is complete code:

_________________________________________

Sub ConcatenateRowinMultipleWkshts()

For Each sh In ThisWorkbook.Worksheets
With sh
X = 1
Do While X < 30
.Cells(3, X).Value = .Cells(1, X) + " " + .Cells(2, X)
X = X + 1
Loop
End With
Next sh

End Sub

_______________________________________

Thanks again

 

[Swanson1962]

Hi -

Well, I guess I spoke too soon -- I'd been testing this code on my 97 excel and when I tried to use in the 2007 upgrade it kept bugging...

Any ideas? Do I need a dim statement here or something???

Thanks so much