previously, i had a problem with this macro that i put
together. when i originally recorded the macro i did so
with a worksheet of say, 100 records......well, when i ran
the macro on a different worksheet, everything worked
fine, except for the fact that only the second worksheet
had 125 records. after the macro was finished running, i
noticed that work had been done on only the first 100
records of the worksheet......the remaining 25 hadn't
changed.

so......i got some help here in the newsgroups. i
followed several suggestions that i change a handful of
lines in the macro code.......from Range(B1:B100) to Range
("B2:B" & Range("B1").End(xlDown).Row).

well, this was the perfect fix for my problem, or so i
thought.

now i'm stuck with this problem: my worksheet now
consists of some 65,000 records......most of them blank of
course, but nonetheless, when i go to run print jobs,
etc., the printer spits out the first 125 records followed
by sheet after sheet of blank paper (which represent the
65000 or so empty records.)

i can only imagine that the B2:B" & Range("B1).End
(xlDown).Row) line is my problem. is there a way in which
i can change this line so that i'm not stuck with so many
records?

thanks in advance!

I guess we would have to see the code to understand if your macro is the
cause or not - of course it may not be self evident and the structure of
your data would make a difference.

--
Regards,
Tom Ogilvy



"Josh in Tampa" wrote in message
...
previously, i had a problem with this macro that i put
together. when i originally recorded the macro i did so
with a worksheet of say, 100 records......well, when i ran
the macro on a different worksheet, everything worked
fine, except for the fact that only the second worksheet
had 125 records. after the macro was finished running, i
noticed that work had been done on only the first 100
records of the worksheet......the remaining 25 hadn't
changed.

so......i got some help here in the newsgroups. i
followed several suggestions that i change a handful of
lines in the macro code.......from Range(B1:B100) to Range
("B2:B" & Range("B1").End(xlDown).Row).

well, this was the perfect fix for my problem, or so i
thought.

now i'm stuck with this problem: my worksheet now
consists of some 65,000 records......most of them blank of
course, but nonetheless, when i go to run print jobs,
etc., the printer spits out the first 125 records followed
by sheet after sheet of blank paper (which represent the
65000 or so empty records.)

i can only imagine that the B2:B" & Range("B1).End
(xlDown).Row) line is my problem. is there a way in which
i can change this line so that i'm not stuck with so many
records?

thanks in advance!

your range is probably restricted to 100 rows
range("a1:a100") as an example

"Josh in Tampa" wrote in message
...
previously, i had a problem with this macro that i put
together. when i originally recorded the macro i did so
with a worksheet of say, 100 records......well, when i ran
the macro on a different worksheet, everything worked
fine, except for the fact that only the second worksheet
had 125 records. after the macro was finished running, i
noticed that work had been done on only the first 100
records of the worksheet......the remaining 25 hadn't
changed.

so......i got some help here in the newsgroups. i
followed several suggestions that i change a handful of
lines in the macro code.......from Range(B1:B100) to Range
("B2:B" & Range("B1").End(xlDown).Row).

well, this was the perfect fix for my problem, or so i
thought.

now i'm stuck with this problem: my worksheet now
consists of some 65,000 records......most of them blank of
course, but nonetheless, when i go to run print jobs,
etc., the printer spits out the first 125 records followed
by sheet after sheet of blank paper (which represent the
65000 or so empty records.)

i can only imagine that the B2:B" & Range("B1).End
(xlDown).Row) line is my problem. is there a way in which
i can change this line so that i'm not stuck with so many
records?

thanks in advance!

-----Original Message-----
previously, i had a problem with this macro that i put
together. when i originally recorded the macro i did so
with a worksheet of say, 100 records......well, when i
ran
the macro on a different worksheet, everything worked
fine, except for the fact that only the second worksheet
had 125 records. after the macro was finished running, i
noticed that work had been done on only the first 100
records of the worksheet......the remaining 25 hadn't
changed.

so......i got some help here in the newsgroups. i
followed several suggestions that i change a handful of
lines in the macro code.......from Range(B1:B100) to Range
("B2:B" & Range("B1").End(xlDown).Row).

well, this was the perfect fix for my problem, or so i
thought.

now i'm stuck with this problem: my worksheet now
consists of some 65,000 records......most of them blank
of
course, but nonetheless, when i go to run print jobs,
etc., the printer spits out the first 125 records
followed
by sheet after sheet of blank paper (which represent the
65000 or so empty records.)

i can only imagine that the B2:B" & Range("B1).End
(xlDown).Row) line is my problem. is there a way in
which
i can change this line so that i'm not stuck with so many
records?

thanks in advance!
.

Look at the data column B has in it?
Only use a column where all cells have a value and it
should work.

okay....here's the macro:

again, my problem is that my worksheets are now
HUMONGOUS.....65000 rows.

Sub ImportPrep()
'
' ImportPrep Macro
' Macro recorded 10/24/2003 by jbrady
'

'
Columns("C:C").Select
Selection.Delete
Rows("1:1").Select
Selection.Insert Shift:=xlDown
ActiveCell.FormulaR1C1 = "Contact"
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B1").Select
ActiveCell.FormulaR1C1 = "LastName"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!getlastname(RC
[-1])"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B" & Range
("B1").End(xlDown).Row).Select, Type:=xlFillDefault
Range("B2:B" & Range("B1").End(xlDown).Row).Select
ActiveWindow.ScrollRow = 1
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Columns("C:C").Select
Selection.Copy
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("C1").Select
ActiveCell.FormulaR1C1 = "Address1"
Range("D1").Select
ActiveCell.FormulaR1C1 = "City"
Range("E1").Select
ActiveCell.FormulaR1C1 = "State"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Zip"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Phone1"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Email"
Range("I1").Select
ActiveCell.FormulaR1C1 = "DetailCode"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Fax"
Range("A1").Select
End Sub
-----Original Message-----
I guess we would have to see the code to understand if
your macro is the
cause or not - of course it may not be self evident and
the structure of
your data would make a difference.

--
Regards,
Tom Ogilvy



"Josh in Tampa"
wrote in message
...
previously, i had a problem with this macro that i put
together. when i originally recorded the macro i did so
with a worksheet of say, 100 records......well, when i
ran
the macro on a different worksheet, everything worked
fine, except for the fact that only the second worksheet
had 125 records. after the macro was finished running,
i
noticed that work had been done on only the first 100
records of the worksheet......the remaining 25 hadn't
changed.

so......i got some help here in the newsgroups. i
followed several suggestions that i change a handful of
lines in the macro code.......from Range(B1:B100) to
Range
("B2:B" & Range("B1").End(xlDown).Row).

well, this was the perfect fix for my problem, or so i
thought.

now i'm stuck with this problem: my worksheet now
consists of some 65,000 records......most of them blank
of
course, but nonetheless, when i go to run print jobs,
etc., the printer spits out the first 125 records
followed
by sheet after sheet of blank paper (which represent the
65000 or so empty records.)

i can only imagine that the B2:B" & Range("B1).End
(xlDown).Row) line is my problem. is there a way in
which
i can change this line so that i'm not stuck with so
many
records?

thanks in advance!


.

See if this is what you want

Sub ImportPrep_Don()
'Macro created 10/27/2003 by Don Guillett
Columns("C:C").ClearContents
Range("A1") = "Contact"
Range("B1") = "LastName"
Range("C1") = "Address1"
Range("D1") = "City"
Range("E1") = "State"
Range("F1") = "Zip"
Range("G1") = "Phone1"
Range("H1") = "Email"
Range("I1") = "DetailCode"
Range("J1") = "Fax"
Columns("a:j").AutoFit

Set myrng = Range("b2:b" & Range("b1").End(xlDown).Row)
'might be better to use
'Set myrng = Range("b2:b" & Range("b65536").End(xlup).Row)

'myrng.FormulaR1C1 = "=PERSONAL.XLS!getlastname(RC[-1])"
myrng.Formula = "=1*2" 'for my test
myrng.Formula = myrng.Value 'to change to values
End Sub
wrote in message
...
okay....here's the macro:

again, my problem is that my worksheets are now
HUMONGOUS.....65000 rows.

Sub ImportPrep()
'
' ImportPrep Macro
' Macro recorded 10/24/2003 by jbrady
'

'
Columns("C:C").Select
Selection.Delete
Rows("1:1").Select
Selection.Insert Shift:=xlDown
ActiveCell.FormulaR1C1 = "Contact"
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B1").Select
ActiveCell.FormulaR1C1 = "LastName"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!getlastname(RC
[-1])"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B" & Range
("B1").End(xlDown).Row).Select, Type:=xlFillDefault
Range("B2:B" & Range("B1").End(xlDown).Row).Select
ActiveWindow.ScrollRow = 1
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Columns("C:C").Select
Selection.Copy
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("C1").Select
ActiveCell.FormulaR1C1 = "Address1"
Range("D1").Select
ActiveCell.FormulaR1C1 = "City"
Range("E1").Select
ActiveCell.FormulaR1C1 = "State"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Zip"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Phone1"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Email"
Range("I1").Select
ActiveCell.FormulaR1C1 = "DetailCode"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Fax"
Range("A1").Select
End Sub
-----Original Message-----
I guess we would have to see the code to understand if
your macro is the
cause or not - of course it may not be self evident and
the structure of
your data would make a difference.

--
Regards,
Tom Ogilvy



"Josh in Tampa"
wrote in message
...
previously, i had a problem with this macro that i put
together. when i originally recorded the macro i did so
with a worksheet of say, 100 records......well, when i
ran
the macro on a different worksheet, everything worked
fine, except for the fact that only the second worksheet
had 125 records. after the macro was finished running,
i
noticed that work had been done on only the first 100
records of the worksheet......the remaining 25 hadn't
changed.

so......i got some help here in the newsgroups. i
followed several suggestions that i change a handful of
lines in the macro code.......from Range(B1:B100) to
Range
("B2:B" & Range("B1").End(xlDown).Row).

well, this was the perfect fix for my problem, or so i
thought.

now i'm stuck with this problem: my worksheet now
consists of some 65,000 records......most of them blank
of
course, but nonetheless, when i go to run print jobs,
etc., the printer spits out the first 125 records
followed
by sheet after sheet of blank paper (which represent the
65000 or so empty records.)

i can only imagine that the B2:B" & Range("B1).End
(xlDown).Row) line is my problem. is there a way in
which
i can change this line so that i'm not stuck with so
many
records?

thanks in advance!


.

This might work a little better:

Sub AAAAA()
Rows(1).Insert
Columns(2).Insert
Set rng = Range(Cells(2, 1), Cells(2, 1).End(xlDown))
rng(1).Offset(0, 1).FormulaR1C1 = _
"=PERSONAL.XLS!getlastname(RC[-1])"
rng.Offset(0, 1).FillDown
rng.Offset(0, 1).Formula = rng.Offset(0, 1).Value
Range("a1").FormulaR1C1 = "Contact"
Range("B1").FormulaR1C1 = "LastName"
Range("C1").FormulaR1C1 = "Address1"
Range("D1").FormulaR1C1 = "City"
Range("E1").FormulaR1C1 = "State"
Range("F1").FormulaR1C1 = "Zip"
Range("G1").FormulaR1C1 = "Phone1"
Range("H1").FormulaR1C1 = "Email"
Range("I1").FormulaR1C1 = "DetailCode"
Range("J1").FormulaR1C1 = "Fax"
Range("A1").Select

End Sub

--
Regards,
Tom Ogilvy

wrote in message
...
okay....here's the macro:

again, my problem is that my worksheets are now
HUMONGOUS.....65000 rows.

Sub ImportPrep()
'
' ImportPrep Macro
' Macro recorded 10/24/2003 by jbrady
'

'
Columns("C:C").Select
Selection.Delete
Rows("1:1").Select
Selection.Insert Shift:=xlDown
ActiveCell.FormulaR1C1 = "Contact"
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B1").Select
ActiveCell.FormulaR1C1 = "LastName"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!getlastname(RC
[-1])"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B" & Range
("B1").End(xlDown).Row).Select, Type:=xlFillDefault
Range("B2:B" & Range("B1").End(xlDown).Row).Select
ActiveWindow.ScrollRow = 1
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Columns("C:C").Select
Selection.Copy
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("C1").Select
ActiveCell.FormulaR1C1 = "Address1"
Range("D1").Select
ActiveCell.FormulaR1C1 = "City"
Range("E1").Select
ActiveCell.FormulaR1C1 = "State"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Zip"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Phone1"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Email"
Range("I1").Select
ActiveCell.FormulaR1C1 = "DetailCode"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Fax"
Range("A1").Select
End Sub
-----Original Message-----
I guess we would have to see the code to understand if
your macro is the
cause or not - of course it may not be self evident and
the structure of
your data would make a difference.

--
Regards,
Tom Ogilvy



"Josh in Tampa"
wrote in message
...
previously, i had a problem with this macro that i put
together. when i originally recorded the macro i did so
with a worksheet of say, 100 records......well, when i
ran
the macro on a different worksheet, everything worked
fine, except for the fact that only the second worksheet
had 125 records. after the macro was finished running,
i
noticed that work had been done on only the first 100
records of the worksheet......the remaining 25 hadn't
changed.

so......i got some help here in the newsgroups. i
followed several suggestions that i change a handful of
lines in the macro code.......from Range(B1:B100) to
Range
("B2:B" & Range("B1").End(xlDown).Row).

well, this was the perfect fix for my problem, or so i
thought.

now i'm stuck with this problem: my worksheet now
consists of some 65,000 records......most of them blank
of
course, but nonetheless, when i go to run print jobs,
etc., the printer spits out the first 125 records
followed
by sheet after sheet of blank paper (which represent the
65000 or so empty records.)

i can only imagine that the B2:B" & Range("B1).End
(xlDown).Row) line is my problem. is there a way in
which
i can change this line so that i'm not stuck with so
many
records?

thanks in advance!


.

Make sure you do this on a copy of your data since you will lose your first
row of data and possibly your third column (address1).

--
Regards,
Tom Ogilvy

"Don Guillett" wrote in message
...
See if this is what you want

Sub ImportPrep_Don()
'Macro created 10/27/2003 by Don Guillett
Columns("C:C").ClearContents
Range("A1") = "Contact"
Range("B1") = "LastName"
Range("C1") = "Address1"
Range("D1") = "City"
Range("E1") = "State"
Range("F1") = "Zip"
Range("G1") = "Phone1"
Range("H1") = "Email"
Range("I1") = "DetailCode"
Range("J1") = "Fax"
Columns("a:j").AutoFit

Set myrng = Range("b2:b" & Range("b1").End(xlDown).Row)
'might be better to use
'Set myrng = Range("b2:b" & Range("b65536").End(xlup).Row)

'myrng.FormulaR1C1 = "=PERSONAL.XLS!getlastname(RC[-1])"
myrng.Formula = "=1*2" 'for my test
myrng.Formula = myrng.Value 'to change to values
End Sub
wrote in message
...
okay....here's the macro:

again, my problem is that my worksheets are now
HUMONGOUS.....65000 rows.

Sub ImportPrep()
'
' ImportPrep Macro
' Macro recorded 10/24/2003 by jbrady
'

'
Columns("C:C").Select
Selection.Delete
Rows("1:1").Select
Selection.Insert Shift:=xlDown
ActiveCell.FormulaR1C1 = "Contact"
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Range("B1").Select
ActiveCell.FormulaR1C1 = "LastName"
Range("B2").Select
ActiveCell.FormulaR1C1 = "=PERSONAL.XLS!getlastname(RC
[-1])"
Range("B2").Select
Selection.AutoFill Destination:=Range("B2:B" & Range
("B1").End(xlDown).Row).Select, Type:=xlFillDefault
Range("B2:B" & Range("B1").End(xlDown).Row).Select
ActiveWindow.ScrollRow = 1
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
Columns("C:C").Select
Selection.Copy
Columns("B:B").Select
Selection.PasteSpecial Paste:=xlValues,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("C1").Select
ActiveCell.FormulaR1C1 = "Address1"
Range("D1").Select
ActiveCell.FormulaR1C1 = "City"
Range("E1").Select
ActiveCell.FormulaR1C1 = "State"
Range("F1").Select
ActiveCell.FormulaR1C1 = "Zip"
Range("G1").Select
ActiveCell.FormulaR1C1 = "Phone1"
Range("H1").Select
ActiveCell.FormulaR1C1 = "Email"
Range("I1").Select
ActiveCell.FormulaR1C1 = "DetailCode"
Range("J1").Select
ActiveCell.FormulaR1C1 = "Fax"
Range("A1").Select
End Sub
-----Original Message-----
I guess we would have to see the code to understand if
your macro is the
cause or not - of course it may not be self evident and
the structure of
your data would make a difference.

--
Regards,
Tom Ogilvy



"Josh in Tampa"
wrote in message
...
previously, i had a problem with this macro that i put
together. when i originally recorded the macro i did so
with a worksheet of say, 100 records......well, when i
ran
the macro on a different worksheet, everything worked
fine, except for the fact that only the second worksheet
had 125 records. after the macro was finished running,
i
noticed that work had been done on only the first 100
records of the worksheet......the remaining 25 hadn't
changed.

so......i got some help here in the newsgroups. i
followed several suggestions that i change a handful of
lines in the macro code.......from Range(B1:B100) to
Range
("B2:B" & Range("B1").End(xlDown).Row).

well, this was the perfect fix for my problem, or so i
thought.

now i'm stuck with this problem: my worksheet now
consists of some 65,000 records......most of them blank
of
course, but nonetheless, when i go to run print jobs,
etc., the printer spits out the first 125 records
followed
by sheet after sheet of blank paper (which represent the
65000 or so empty records.)

i can only imagine that the B2:B" & Range("B1).End
(xlDown).Row) line is my problem. is there a way in
which
i can change this line so that i'm not stuck with so
many
records?

thanks in advance!


.