Fastest way to do this?

[Abu Ali]

I have 10000 logins and their respective passwords. Logins are in col A and
passwords are in B.
Logins contain serial no. like LGN00001 to LGN10000

What I want is to have the first 200 logins/ pwds on the first page like 1
to 50 in col A,b then 51 to 100 in C,D then 101 to 150 in E,F and 151 to 200
on G and H,

The same thing is repeated for the rest of logins. 201 to 250 on A,B etc.

any cool macro would help.

thx

 

[Bob Phillips]

Here's a very straight-forward macro

Sub ReFormat()
Dim iSource As Long
Dim iTarget As Long

iSource = 1
iTarget = 1

Do
Cells(iSource, "A").Resize(50, 2).Cut Destination:=Cells(iTarget,
"A")
Cells(iSource + 50, "A").Resize(50, 2).Cut
Destination:=Cells(iTarget, "C")
Cells(iSource + 100, "A").Resize(50, 2).Cut
Destination:=Cells(iTarget, "E")
Cells(iSource + 150, "A").Resize(50, 2).Cut
Destination:=Cells(iTarget, "G")
iSource = iSource + 200
iTarget = iTarget + 51
Loop Until IsEmpty(Cells(iSource, "A").Value)

End Sub



--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Tushar Mehta]

If you are still interested in this issue...

You don't have to use a macro. Adapt the ideas in my posts at
http://www.mrexcel.com/board2/viewtopic.php?p=340943#340943
and
http://www.mrexcel.com/board2/viewtopic.php?p=341020#341020

--
Regards,

Tushar Mehta, MS MVP -- Excel
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

 

[Rob van Gelder]

Abu,

Not necessarily the fastest way.
Your Logons/Passwords should be in Worksheets(1) (or in a different workbook
alrogether!), if they're not then the list will overwrite itself.

Sub testit()
Const cPerColumn = 50, cPerWorksheet = 200, cSourceCols = 2
Dim i As Long, lngLastRow As Long, wks As Worksheet

Set wks = Sheet1

On Error GoTo NoMoreSheets
lngLastRow = wks.Cells(1, 1).End(xlDown).Row
For i = 0 To lngLastRow - 1 Step cPerColumn
Worksheets(i \ cPerWorksheet + 1).Cells(1, (i Mod cPerWorksheet) /
cPerColumn * cSourceCols + 1).Resize(cPerColumn, cSourceCols).Value =
wks.Cells(i + 1, 1).Resize(cPerColumn, cSourceCols).Value
Next
On Error GoTo 0
Exit Sub

NoMoreSheets:
With Worksheets: .Add After:=.Item(.Count): End With
Resume
End Sub


Rob