Macro needed

ineedadedt

Board Regular
Joined
Jan 7, 2004
Messages
163
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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

al_b_cnu

Well-known Member
Joined
Jul 18, 2003
Messages
4,516
Hi,

Try this macro which clears then populates sheet2 from sheet1:
Code:
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

Board Regular
Joined
Jan 7, 2004
Messages
163

ADVERTISEMENT

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
ABCDE
18/13/2006
2NestingCS5and6
3Totals1620457.137740.5129683
4Agent,1100653.9328.8885
5Agent,2209264.349387.6076684
6Agent,3257536.05459.6653784
7Agent,4121892.190139.4297591
8Agent,5300284.146740.0588
9Agent,6249377.477940.0803287
10Agent,7162418.259254.3271675
11Agent,888442.693243.1818274
12Agent,9134658.574619.2686674
138/6/2006
14NestingCS5and6
15Agent,11661488.020536.5990480
16Agent,2154483.292215.4415675
17Agent,3217240.313473.8755878
18Agent,4200668.04512.3881
19Agent,5129846.15531.7286888
20Agent,6256374.628935.6171986
21Agent,7237419.894531.7805985
22Agent,8185461.745949.5513576
23Agent,921545.523855.1428686
24Agent,10138460.565257.9927575
25Agent,11124688.76616.73387170
267/30/2006
27NestingCS5and6
28Agent,11933440.914140.4821584
29Agent,285513.835312.1882476
30Agent,3316191.848167.0348180
Sheet2
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,303
Office Version
  1. 365
Platform
  1. Windows
Eric

Try this.
Code:
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

Board Regular
Joined
Jan 7, 2004
Messages
163
And again this board comes up huge for me!!

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

:pray:
 

Forum statistics

Threads
1,141,308
Messages
5,705,666
Members
421,403
Latest member
cmowla

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top