Fastest way to do this?

  • Thread starter Thread starter Abu Ali
  • Start date Start date
A

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
 
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)
 
Sub MoveLogons()

Dim firstrow As Long, lastrow As Long, cl As Long

cl = 1 ' initialise target column

For firstrow = 1 To 10000 Step 200
lastrow = firstrow + 199
cl = cl + 1

Range(Cells(1, cl), Cells(200, cl)).Value = _
Range(Cells(firstrow, 1), Cells(lastrow, 1)).Value

Next

End Sub

Patrick Molloy
Microsoft Excel MVP
 
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
 
Back
Top