Reformatting Macro

[amsnss]

Hello all. Was hoping that someone could help me with with a macro that I'm wanting loop. Basically, I have a spreadsheet that has somewhere in the area of 300-400 or more records each day. All of the data is on 1 line, except for a client name and address which drops down a few lines. What I want to do is have a macro that will put this information into more of a database format and delete the extra and blank rows. I want the macro to loop and run until it encounters nothing but blank rows. The macro currently looks something like this if I do it manually. Any help with this would be appreciated. I can give an example spreadsheet that shows how the data is laid out and how I want it to appear if needed.

Sub Reformatting()
'
' Reformatting Macro
' Macro recorded 06/07/2007 by NSmith
'

'
Range("B3:B4").Select
Selection.Copy
Range("C2").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Rows("3:7").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("B4:B5").Select
Selection.Copy
Range("C3").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Rows("4:8").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("B5:B6").Select
Selection.Copy
Range("C4").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Rows("5:9").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("B6:B7").Select
Selection.Copy
Range("C5").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Rows("6:10").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("B7:B8").Select
Selection.Copy
Range("C6").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Rows("7:9").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlUp
Range("A1").Select
End Sub

[amsnss]

Attached is a sample of the spreadsheet I'm trying to format. The first worksheet shows the original data, and the second shows the desired format. There is a macro setup currently in the first tab that was nothing more than me going through the motions of getting the spreadsheet how we want it to appear.

Hopefully this helps. Thanks again!

[boylejob]

amsnss,

See if the code below will get you started.

In order to get around the header information I chose to look at the policy number. Since I have no idea what your real data looks like I decided to assume that each policy number is 10 characters in length. If the item in column A is 10 characters in length then it moves the data and if it is not, it moves on to the next row. You will probably need to change this line of code. If the policy number are always a set length then I would go with that otherwise we will have to come up with some other way to idenify polich numbers from header row information.

 If Len(wsOrig.Cells(lRow, 1)) = 10 Then

Sub aaa()
    
Dim wsOrig As Worksheet
Dim wsFormat As Worksheet
Dim lLastRow As Long
Dim lRow As Long
Dim lRow2 As Long

Set wsOrig = Sheets("OriginalData")
Set wsFormat = Sheets("DesiredSheetFormattingFinal")
lLastRow = wsOrig.Cells(Rows.Count, 4).End(xlUp).Row
lRow2 = 2

For lRow = 1 To lLastRow

    If Len(wsOrig.Cells(lRow, 1)) = 10 Then
        wsFormat.Cells(lRow2, 1).Value = wsOrig.Cells(lRow, 1).Value 'Policy Number
        wsFormat.Cells(lRow2, 2).Value = wsOrig.Cells(lRow, 4).Value 'Name
        wsFormat.Cells(lRow2, 3).Value = wsOrig.Cells(lRow + 1, 4).Value 'Street
        wsFormat.Cells(lRow2, 4).Value = wsOrig.Cells(lRow + 2, 4).Value 'CSZ
        wsFormat.Cells(lRow2, 5).Value = wsOrig.Cells(lRow, 9).Value 'Net Chg Premium
        wsFormat.Cells(lRow2, 6).Value = wsOrig.Cells(lRow, 11).Value 'Ivans LOB
        wsFormat.Cells(lRow2, 7).Value = wsOrig.Cells(lRow, 13).Value 'AFI
        wsFormat.Cells(lRow2, 8).Value = wsOrig.Cells(lRow, 15).Value ' Co Code
        wsFormat.Cells(lRow2, 9).Value = wsOrig.Cells(lRow, 16).Value 'Trn Type
        wsFormat.Cells(lRow2, 10).Value = wsOrig.Cells(lRow, 17).Value 'Cycle
        wsFormat.Cells(lRow2, 11).Value = wsOrig.Cells(lRow, 18).Value 'Seq Num
        wsFormat.Cells(lRow2, 12).Value = wsOrig.Cells(lRow, 19).Value 'Date Added
        wsFormat.Cells(lRow2, 13).Value = wsOrig.Cells(lRow, 21).Value 'Date Proc
        lRow = lRow + 3
        lRow2 = lRow2 + 1
    End If

Next lRow

End Sub

Hope this will at the very least get you started!

[amsnss]

Well, I can't really use the policy number field as a delimiter as the actual policy numbers will differ in length. I should have mentioned that before. Sorry, but thanks for your help so far! None of the data will be consistent with any other data in the sheet except for how it's originally formatted.

amsnss,

See if the code below will get you started.

In order to get around the header information I chose to look at the policy number. Since I have no idea what your real data looks like I decided to assume that each policy number is 10 characters in length. If the item in column A is 10 characters in length then it moves the data and if it is not, it moves on to the next row. You will probably need to change this line of code. If the policy number are always a set length then I would go with that otherwise we will have to come up with some other way to idenify polich numbers from header row information.

Hope this will at the very least get you started!

[boylejob]

amsnss,

Lets take another look at Column A. Is the sample pretty much what you will be work with everytime. In other words will you always have the heading

Policy
Number
-------------------

We might be able to work with that.

Do the policy numbers have a minimum and a maximum length. Do they always begin with a letter or a number? Do they always end with a letter or a number?

We can have multiple things that we look at, but we still need to know what those multiple things are.

If you can provide a few more details, I'll be more than happy to continue helping you move towards a solution.

[amsnss]

The column headings shown in the sheet will always be there and always be the same. The data in those columns is the tricky part as the data will always be different. Basically, I'm receiving an electronic list of customer information for many different customers on one report. Everything for each customer is on the same row except for the address which is on the row below the name, and the city, state, zip which is on the next row below the address. All of the other data is on the same row. What I want to accomplish is basically moving the address and city, state and zip to the same row as the rest of the data and remove the blank rows.

I hope that made sense. It sounded good in my head.

amsnss,

Lets take another look at Column A. Is the sample pretty much what you will be work with everytime. In other words will you always have the heading

Policy
Number
-------------------

We might be able to work with that.

Do the policy numbers have a minimum and a maximum length. Do they always begin with a letter or a number? Do they always end with a letter or a number?

We can have multiple things that we look at, but we still need to know what those multiple things are.

If you can provide a few more details, I'll be more than happy to continue helping you move towards a solution.