Hi Putta
A couple of lines added to determine the next row to fill...
Greetings
Paul Willekens
'=======================================================================
Sub Normalize()
Dim cAddress
Dim cBusiness
Dim cCity
Dim cCompany
Dim cContact
Dim cState
Dim lDone
Dim nRow
'first find the new row in "Output"
nRow = 1
lDone = False
While lDone = False
If Len(Cells(nRow, 1).Value) < 1 Then
lDone = True
Else
nRow = nRow + 1
End If
Wend
'company name
Sheets("Input Data").Select
Cells(1, 1).Select
cCompany = Selection.Value
Sheets("Output").Select
Cells(nRow, 1).Value = cCompany
'address
Sheets("Input Data").Select
Range("B12:C12").MergeCells = False
Cells(12, 2).Select
cAddress = Selection.Value
Range("B12:C12").MergeCells = True
Range("B13:C13").MergeCells = False
Cells(13, 2).Select
cAddress = cAddress & vbCrLf & Selection.Value
Range("B13:C13").MergeCells = True
Range("B14:C14").MergeCells = False
Cells(14, 2).Select
cCity = Selection.Value
cAddress = cAddress & " " & cCity
cCity = Left(cCity, (Len(cCity) - InStrRev(cCity, " ")))
Range("B14:C14").MergeCells = True
Range("B15:C15").MergeCells = False
Cells(15, 2).Select
cState = Selection.Value
cAddress = cAddress & " " & cState
cState = Trim(Replace(cState, "India", ""))
Range("B15:C15").MergeCells = True
Sheets("Output").Select
Cells(nRow, 2).Value = cAddress
'city
Cells(nRow, 3).Value = cCity
'state
Cells(nRow, 4).Value = cState
'contact person
Sheets("Input Data").Select
Range("D11:D15").MergeCells = False
Cells(11, 4).Select
cContact = Selection.Value
cContact = Trim(Replace(cContact, "Contact Person:", ""))
Range("D11:D15").MergeCells = True
Sheets("Output").Select
Cells(nRow, 10).Value = cContact
'business details
Sheets("Input Data").Select
Range("B8:D8").MergeCells = False
Cells(8, 2).Select
cBusiness = Selection.Value
Range("B8:D8").MergeCells = True
Sheets("Output").Select
Cells(nRow, 11).Value = cBusiness
End Sub
'=======================================================================
--
----------------------------------------------------------------------------------
Some important links for excel users:
1. Follow us on TWITTER for tips tricks and links :
http://twitter.com/exceldailytip
2. Join our Facebook Group @ http://www.facebook.com/group.php?gid=287779555678
3. Excel tutorials at http://www.excel-macros.blogspot.com
4. Learn VBA Macros at http://www.quickvba.blogspot.com
5. Excel Tips and Tricks at http://exceldailytip.blogspot.com
To post to this group, send email to excel-macros@googlegroups.com
<><><><><><><><><><><><><><><><><><><><><><>
HELP US GROW !!
We reach over 6,800 subscribers worldwide and receive many nice notes about the
learning and support from the group.Let friends and co-workers know they can
subscribe to group at http://groups.google.com/group/excel-macros/subscribe
To unsubscribe, reply using "remove me" as the subject.
