Hello Everyone,

I have three questions concerning a macro I'm trying to write.

I'm importing a text file daily into XL 2003 and saving using the same
filename each day. I'm using the macro recorder to write the macros, since
I'm unfamiliar with VB, then trying to edit.

1. What is the command I need to use to always select the whole sheet, since
the number of lines changes each day?

2.I'm using a vlookup formula. When I run the macro It opens a "Update
Values" window a few times asking for the file reference in vlookup. Once I
respond, it takes forever to continue. Any way to fix this? It doesn't
matter if the referenced file is already opened.

3. Is there a way to automatically insert page breaks each time the data in
a column changes, and ask the user which column to reference?

Thanks for your help,

Dennis

As always, post your coding efforts for comments and suggestions.

--
Don Guillett
SalesAid Software

"Dennis S." <Dennis wrote in message
...
Hello Everyone,

I have three questions concerning a macro I'm trying to write.

I'm importing a text file daily into XL 2003 and saving using the same
filename each day. I'm using the macro recorder to write the macros, since
I'm unfamiliar with VB, then trying to edit.

1. What is the command I need to use to always select the whole sheet,
since
the number of lines changes each day?

2.I'm using a vlookup formula. When I run the macro It opens a "Update
Values" window a few times asking for the file reference in vlookup. Once
I
respond, it takes forever to continue. Any way to fix this? It doesn't
matter if the referenced file is already opened.

3. Is there a way to automatically insert page breaks each time the data
in
a column changes, and ask the user which column to reference?

Thanks for your help,

Dennis

Answered inline:
"Dennis S." <Dennis wrote in message
...
Hello Everyone,

I have three questions concerning a macro I'm trying to write.

I'm importing a text file daily into XL 2003 and saving using the same
filename each day. I'm using the macro recorder to write the macros, since
I'm unfamiliar with VB, then trying to edit.

1. What is the command I need to use to always select the whole sheet,
since
the number of lines changes each day?

If it is organized like a database then
Dim rng as Range
set rng = Activesheet.Range("A1").currentRegion

if you just opened the text file in Excel you can also use

set rng = Activesheet.UsedRange

If you then want to select that range (and that is rarely necessary)

rng.select


2.I'm using a vlookup formula. When I run the macro It opens a "Update
Values" window a few times asking for the file reference in vlookup. Once
I
respond, it takes forever to continue. Any way to fix this? It doesn't
matter if the referenced file is already opened.

This would be hard to advise on. It sounds like you are entering a formula
with an invalid reference. You should adjust your code to build a valid
reference before your code enters the formula. For example, instead of

ActiveCell.formula = "=Vlookup(A1,[Book1.xls]Sheet1!A1:Z26,5,False)"

you could have

Dim bkName as String
bkName = InputBox("Enter Workbook Name:")
ActiveCell.Formula = "=Vlookup(A1,[" & bkName & "]Sheet1!A1:Z26,5,False)"




3. Is there a way to automatically insert page breaks each time the data
in
a column changes, and ask the user which column to reference?

Dim col as Range
Dim i as Long, lastrow as Long
On error resume next
set col = Application.InputBox("Select a cell in the column to process" & _
" using the mouse", Type:=8)
On Error goto 0
if col is nothing then
msgbox "You selected nothing, quitting"
exit sub
end if
lastrow = cells(rows.count,col.column).End(xlup).Row
for i = lastrow to 1 step -2
if cells(i,col.column).value < cells(i-1,col.column).Value then
rows(i).PageBreak = xlPageBreakManual
end if
Next




Thanks for your help,

Dennis


--
Regards,
Tom Ogilvy

Tom,

The page break code seems to always work the first time the value changes
but in the remaining column it's spotty. I clear all page breaks befure
running the code.

Thanks,
Dennis

"Tom Ogilvy" wrote:

Answered inline:
"Dennis S." <Dennis wrote in message
...
Hello Everyone,

I have three questions concerning a macro I'm trying to write.

I'm importing a text file daily into XL 2003 and saving using the same
filename each day. I'm using the macro recorder to write the macros, since
I'm unfamiliar with VB, then trying to edit.

1. What is the command I need to use to always select the whole sheet,
since
the number of lines changes each day?

If it is organized like a database then
Dim rng as Range
set rng = Activesheet.Range("A1").currentRegion

if you just opened the text file in Excel you can also use

set rng = Activesheet.UsedRange

If you then want to select that range (and that is rarely necessary)

rng.select


2.I'm using a vlookup formula. When I run the macro It opens a "Update
Values" window a few times asking for the file reference in vlookup. Once
I
respond, it takes forever to continue. Any way to fix this? It doesn't
matter if the referenced file is already opened.

This would be hard to advise on. It sounds like you are entering a formula
with an invalid reference. You should adjust your code to build a valid
reference before your code enters the formula. For example, instead of

ActiveCell.formula = "=Vlookup(A1,[Book1.xls]Sheet1!A1:Z26,5,False)"

you could have

Dim bkName as String
bkName = InputBox("Enter Workbook Name:")
ActiveCell.Formula = "=Vlookup(A1,[" & bkName & "]Sheet1!A1:Z26,5,False)"




3. Is there a way to automatically insert page breaks each time the data
in
a column changes, and ask the user which column to reference?

Dim col as Range
Dim i as Long, lastrow as Long
On error resume next
set col = Application.InputBox("Select a cell in the column to process" & _
" using the mouse", Type:=8)
On Error goto 0
if col is nothing then
msgbox "You selected nothing, quitting"
exit sub
end if
lastrow = cells(rows.count,col.column).End(xlup).Row
for i = lastrow to 1 step -2
if cells(i,col.column).value < cells(i-1,col.column).Value then
rows(i).PageBreak = xlPageBreakManual
end if
Next




Thanks for your help,

Dennis


--
Regards,
Tom Ogilvy

Sounds like you are saying all the same values don't print on the same page.

You can not make 100 rows of data print in 60 rows of space on a sheet.

The code insures that multiple values don't print on the same page which is
what you asked.

--
Regards,
Tom Ogilvy

"Dennis S." wrote in message
...
Tom,

The page break code seems to always work the first time the value changes
but in the remaining column it's spotty. I clear all page breaks befure
running the code.

Thanks,
Dennis

"Tom Ogilvy" wrote:

Answered inline:
"Dennis S." <Dennis wrote in message
...
Hello Everyone,

I have three questions concerning a macro I'm trying to write.

I'm importing a text file daily into XL 2003 and saving using the same
filename each day. I'm using the macro recorder to write the macros,
since
I'm unfamiliar with VB, then trying to edit.

1. What is the command I need to use to always select the whole sheet,
since
the number of lines changes each day?

If it is organized like a database then
Dim rng as Range
set rng = Activesheet.Range("A1").currentRegion

if you just opened the text file in Excel you can also use

set rng = Activesheet.UsedRange

If you then want to select that range (and that is rarely necessary)

rng.select


2.I'm using a vlookup formula. When I run the macro It opens a "Update
Values" window a few times asking for the file reference in vlookup.
Once
I
respond, it takes forever to continue. Any way to fix this? It
doesn't
matter if the referenced file is already opened.

This would be hard to advise on. It sounds like you are entering a
formula
with an invalid reference. You should adjust your code to build a valid
reference before your code enters the formula. For example, instead of

ActiveCell.formula = "=Vlookup(A1,[Book1.xls]Sheet1!A1:Z26,5,False)"

you could have

Dim bkName as String
bkName = InputBox("Enter Workbook Name:")
ActiveCell.Formula = "=Vlookup(A1,[" & bkName &
"]Sheet1!A1:Z26,5,False)"




3. Is there a way to automatically insert page breaks each time the
data
in
a column changes, and ask the user which column to reference?

Dim col as Range
Dim i as Long, lastrow as Long
On error resume next
set col = Application.InputBox("Select a cell in the column to process"
& _
" using the mouse", Type:=8)
On Error goto 0
if col is nothing then
msgbox "You selected nothing, quitting"
exit sub
end if
lastrow = cells(rows.count,col.column).End(xlup).Row
for i = lastrow to 1 step -2
if cells(i,col.column).value < cells(i-1,col.column).Value then
rows(i).PageBreak = xlPageBreakManual
end if
Next




Thanks for your help,

Dennis


--
Regards,
Tom Ogilvy

Tom,

You're correct, I'm wanting to start a new page each time the value changes,
not fit all the same values on the same page.

I tried it again. This time it hangs up on the "If Cells(i,
col.Column).Value < Cells(i - 1, col.Column).Value Then" line.

I'm using this by creating a macro in Excel, opening the editor and copying
and pasting the code from this thread. Could I be doing something wrong?

Thanks for yor patience.

"Tom Ogilvy" wrote:

Answered inline:
"Dennis S." <Dennis wrote in message
...
Hello Everyone,

I have three questions concerning a macro I'm trying to write.

I'm importing a text file daily into XL 2003 and saving using the same
filename each day. I'm using the macro recorder to write the macros, since
I'm unfamiliar with VB, then trying to edit.

1. What is the command I need to use to always select the whole sheet,
since
the number of lines changes each day?

If it is organized like a database then
Dim rng as Range
set rng = Activesheet.Range("A1").currentRegion

if you just opened the text file in Excel you can also use

set rng = Activesheet.UsedRange

If you then want to select that range (and that is rarely necessary)

rng.select


2.I'm using a vlookup formula. When I run the macro It opens a "Update
Values" window a few times asking for the file reference in vlookup. Once
I
respond, it takes forever to continue. Any way to fix this? It doesn't
matter if the referenced file is already opened.

This would be hard to advise on. It sounds like you are entering a formula
with an invalid reference. You should adjust your code to build a valid
reference before your code enters the formula. For example, instead of

ActiveCell.formula = "=Vlookup(A1,[Book1.xls]Sheet1!A1:Z26,5,False)"

you could have

Dim bkName as String
bkName = InputBox("Enter Workbook Name:")
ActiveCell.Formula = "=Vlookup(A1,[" & bkName & "]Sheet1!A1:Z26,5,False)"




3. Is there a way to automatically insert page breaks each time the data
in
a column changes, and ask the user which column to reference?

Dim col as Range
Dim i as Long, lastrow as Long
On error resume next
set col = Application.InputBox("Select a cell in the column to process" & _
" using the mouse", Type:=8)
On Error goto 0
if col is nothing then
msgbox "You selected nothing, quitting"
exit sub
end if
lastrow = cells(rows.count,col.column).End(xlup).Row
for i = lastrow to 1 step -2
if cells(i,col.column).value < cells(i-1,col.column).Value then
rows(i).PageBreak = xlPageBreakManual
end if
Next




Thanks for your help,

Dennis


--
Regards,
Tom Ogilvy

I see there was a typo in the code:

Dim col as Range
Dim i as Long, lastrow as Long
On error resume next
set col = Application.InputBox("Select a cell in the column to process" & _
" using the mouse", Type:=8)
On Error goto 0
if col is nothing then
msgbox "You selected nothing, quitting"
exit sub
end if
lastrow = cells(rows.count,col.column).End(xlup).Row
for i = lastrow to 2 step -1
if cells(i,col.column).value < cells(i-1,col.column).Value then
rows(i).PageBreak = xlPageBreakManual
end if
Next

Should do it.

--
Regards,
Tom Ogilvy


"Dennis S." wrote in message
...
Tom,

You're correct, I'm wanting to start a new page each time the value
changes,
not fit all the same values on the same page.

I tried it again. This time it hangs up on the "If Cells(i,
col.Column).Value < Cells(i - 1, col.Column).Value Then" line.

I'm using this by creating a macro in Excel, opening the editor and
copying
and pasting the code from this thread. Could I be doing something wrong?

Thanks for yor patience.

"Tom Ogilvy" wrote:

Answered inline:
"Dennis S." <Dennis wrote in message
...
Hello Everyone,

I have three questions concerning a macro I'm trying to write.

I'm importing a text file daily into XL 2003 and saving using the same
filename each day. I'm using the macro recorder to write the macros,
since
I'm unfamiliar with VB, then trying to edit.

1. What is the command I need to use to always select the whole sheet,
since
the number of lines changes each day?

If it is organized like a database then
Dim rng as Range
set rng = Activesheet.Range("A1").currentRegion

if you just opened the text file in Excel you can also use

set rng = Activesheet.UsedRange

If you then want to select that range (and that is rarely necessary)

rng.select


2.I'm using a vlookup formula. When I run the macro It opens a "Update
Values" window a few times asking for the file reference in vlookup.
Once
I
respond, it takes forever to continue. Any way to fix this? It
doesn't
matter if the referenced file is already opened.

This would be hard to advise on. It sounds like you are entering a
formula
with an invalid reference. You should adjust your code to build a valid
reference before your code enters the formula. For example, instead of

ActiveCell.formula = "=Vlookup(A1,[Book1.xls]Sheet1!A1:Z26,5,False)"

you could have

Dim bkName as String
bkName = InputBox("Enter Workbook Name:")
ActiveCell.Formula = "=Vlookup(A1,[" & bkName &
"]Sheet1!A1:Z26,5,False)"




3. Is there a way to automatically insert page breaks each time the
data
in
a column changes, and ask the user which column to reference?

Dim col as Range
Dim i as Long, lastrow as Long
On error resume next
set col = Application.InputBox("Select a cell in the column to process"
& _
" using the mouse", Type:=8)
On Error goto 0
if col is nothing then
msgbox "You selected nothing, quitting"
exit sub
end if
lastrow = cells(rows.count,col.column).End(xlup).Row
for i = lastrow to 1 step -2
if cells(i,col.column).value < cells(i-1,col.column).Value then
rows(i).PageBreak = xlPageBreakManual
end if
Next




Thanks for your help,

Dennis


--
Regards,
Tom Ogilvy

Tom,

It works great, thanks again for your help.

"Tom Ogilvy" wrote:

I see there was a typo in the code:

Dim col as Range
Dim i as Long, lastrow as Long
On error resume next
set col = Application.InputBox("Select a cell in the column to process" & _
" using the mouse", Type:=8)
On Error goto 0
if col is nothing then
msgbox "You selected nothing, quitting"
exit sub
end if
lastrow = cells(rows.count,col.column).End(xlup).Row
for i = lastrow to 2 step -1
if cells(i,col.column).value < cells(i-1,col.column).Value then
rows(i).PageBreak = xlPageBreakManual
end if
Next

Should do it.

--
Regards,
Tom Ogilvy


"Dennis S." wrote in message
...
Tom,

You're correct, I'm wanting to start a new page each time the value
changes,
not fit all the same values on the same page.

I tried it again. This time it hangs up on the "If Cells(i,
col.Column).Value < Cells(i - 1, col.Column).Value Then" line.

I'm using this by creating a macro in Excel, opening the editor and
copying
and pasting the code from this thread. Could I be doing something wrong?

Thanks for yor patience.

"Tom Ogilvy" wrote:

Answered inline:
"Dennis S." <Dennis wrote in message
...
Hello Everyone,

I have three questions concerning a macro I'm trying to write.

I'm importing a text file daily into XL 2003 and saving using the same
filename each day. I'm using the macro recorder to write the macros,
since
I'm unfamiliar with VB, then trying to edit.

1. What is the command I need to use to always select the whole sheet,
since
the number of lines changes each day?

If it is organized like a database then
Dim rng as Range
set rng = Activesheet.Range("A1").currentRegion

if you just opened the text file in Excel you can also use

set rng = Activesheet.UsedRange

If you then want to select that range (and that is rarely necessary)

rng.select


2.I'm using a vlookup formula. When I run the macro It opens a "Update
Values" window a few times asking for the file reference in vlookup.
Once
I
respond, it takes forever to continue. Any way to fix this? It
doesn't
matter if the referenced file is already opened.

This would be hard to advise on. It sounds like you are entering a
formula
with an invalid reference. You should adjust your code to build a valid
reference before your code enters the formula. For example, instead of

ActiveCell.formula = "=Vlookup(A1,[Book1.xls]Sheet1!A1:Z26,5,False)"

you could have

Dim bkName as String
bkName = InputBox("Enter Workbook Name:")
ActiveCell.Formula = "=Vlookup(A1,[" & bkName &
"]Sheet1!A1:Z26,5,False)"




3. Is there a way to automatically insert page breaks each time the
data
in
a column changes, and ask the user which column to reference?

Dim col as Range
Dim i as Long, lastrow as Long
On error resume next
set col = Application.InputBox("Select a cell in the column to process"
& _
" using the mouse", Type:=8)
On Error goto 0
if col is nothing then
msgbox "You selected nothing, quitting"
exit sub
end if
lastrow = cells(rows.count,col.column).End(xlup).Row
for i = lastrow to 1 step -2
if cells(i,col.column).value < cells(i-1,col.column).Value then
rows(i).PageBreak = xlPageBreakManual
end if
Next




Thanks for your help,

Dennis


--
Regards,
Tom Ogilvy