Macro needed

[ineedadedt]

Hello all,

I have a very simple issue but extremely time consuming. I have copy and pasted 126 text files that I pull for my call center into 1 worksheet in Excel.

I need to have a certain format for this data. Currently the data looks like this
A1 Has the date the information was pulled
A2 Has the group name
A3 Has the words "totals"
A4 Has the first persons data
This then goes down for however many people in the group. Some groups have 5, other groups have 15. This number will vary by group.

What I need is the following format
A1 should read "Date""
A2 should read Group Name"
A3 should still read totals
A4 should still have the persons data
B1 Should be the date the information was pulled
B2 Should be the group name

Basically I need to shift over 2 cells and insert "Date" and "Group Name", while keeping all the other data the same.

Any help is greatly appreciated!

Thanks,
Eric

 

[ineedadedt]

any ideas?

 

[northwolves]

Not vert clearly about your problem.

How about upload an attachment?

 

[al_b_cnu]

Hi,

Try this macro which clears then populates sheet2 from sheet1:

Sub CopyData()
Dim iPtr As Integer, iCol As Integer
Dim lRowEnd As Long
Dim R As Range
Dim vData(1 To 4) As Variant
Dim wsFR As Worksheet, wsTo As Worksheet

Set wsFR = Sheets("Sheet1")
Set wsTo = Sheets("Sheet2")

vData(1) = "Date"
vData(2) = "Group Name"
vData(3) = "Totals"
vData(4) = "Data"

wsTo.UsedRange.ClearContents
wsTo.Range("A1:A4").Value = Application.Transpose(vData)

lRowEnd = wsFR.Cells(Rows.Count, "A").End(xlUp).Row
iPtr = 0
iCol = 1
For Each R In wsFR.Range("A1:A" & lRowEnd)
    iPtr = iPtr + 1
    vData(iPtr) = R.Value
    If iPtr = 4 Then
        iCol = iCol + 1
        wsTo.Range(Cells(1, iCol).Address, Cells(4, iCol).Address).Value = _
        Application.Transpose(vData)
        iPtr = 0
    End If
Next R
End Sub

 

[ineedadedt]

Thank you Alan for the code. Unfortunately, that is not what I was looking for. I do not think my post was clear. So below is a sample of the sheet I am working on. All I am in need of is to move A1 and A2 to move to the right, and insert "Date" in A1 and "Group Name" in A2, then go to the next set of data A13/14 and do the same. So on and so forth. Thanks!

scrubbed for mr excel.xls
	A	B	C	D	E
1	8/13/2006
2	NestingCS5and6
3	Totals	1620	457.1377	40.51296	83
4	Agent,1	100	653.93	28.88	85
5	Agent,2	209	264.3493	87.60766	84
6	Agent,3	257	536.0545	9.66537	84
7	Agent,4	121	892.1901	39.42975	91
8	Agent,5	300	284.1467	40.05	88
9	Agent,6	249	377.4779	40.08032	87
10	Agent,7	162	418.2592	54.32716	75
11	Agent,8	88	442.6932	43.18182	74
12	Agent,9	134	658.5746	19.26866	74
13	8/6/2006
14	NestingCS5and6
15	Agent,1	1661	488.0205	36.59904	80
16	Agent,2	154	483.2922	15.44156	75
17	Agent,3	217	240.3134	73.87558	78
18	Agent,4	200	668.045	12.38	81
19	Agent,5	129	846.155	31.72868	88
20	Agent,6	256	374.6289	35.61719	86
21	Agent,7	237	419.8945	31.78059	85
22	Agent,8	185	461.7459	49.55135	76
23	Agent,9	21	545.5238	55.14286	86
24	Agent,10	138	460.5652	57.99275	75
25	Agent,11	124	688.7661	6.733871	70
26	7/30/2006
27	NestingCS5and6
28	Agent,1	1933	440.9141	40.48215	84
29	Agent,2	85	513.8353	12.18824	76
30	Agent,3	316	191.8481	67.03481	80
Sheet2

 

[ineedadedt]

 

[Norie]

Eric

Try this.

Sub test()
Dim rng As Range
    Set rng = Range("A1")
    While rng.Value <> ""
        If IsDate(rng) Then
            rng.Resize(2).Insert Shift:=xlToRight
            rng.Offset(, -1) = "Date"
            rng.Offset(1, -1) = "Group Name"
            Set rng = rng.Offset(1, -1)
        End If
        Set rng = rng.Offset(1)
    Wend
End Sub

 

[ineedadedt]

And again this board comes up huge for me!!

Thank you so much Norie and all who helped!!!!!!!

ray: