Try this:

Sub Queries()
'
' Queries Macro
' Macro recorded 23/08/2006 by CBRE
'
' Keyboard Shortcut: Ctrl+Shift+Q
'
Dim m As Long
Dim LRow As Long

For m = 1 To 250
LRow = m + (271 * (m - 1))
If LRow 64000 Then
Worksheets.Add
LRow = 1
End If
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.chintai.net/chintai/shuto/CGI/area_list.cgi?area=shuto_tokyo&city=1190_1200_1210 _1220_1230&s=0000000011111100000001000000003200000 000000000000000000010000000000220&p="
& m _
, Destination:=Range("A" & LRow))
.Name = _
"area_list.cgi?area=shuto_tokyo&city=1190_1200_121 0_1220_1230&s=000000001111110000000100000000320000 0000000000000000000010000000000220&p="
& m
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertEntireRows
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "12"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
' .WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
Next m
End Sub


Mike F
"Pelham" wrote in message
ups.com...
Dear Mike and Don

Thanks - very good of you!

Here is the VBA code which keeps looping depending upon how large 'm'
is. In this case 'm' is 250 (I need to enter this each time) which
means the destination WorkSheet will be larger than 64,000 lines
because each time 272 lines are copied.

Since I do not use the term LastRow, what should I do?


Sub Queries()
'
' Queries Macro
' Macro recorded 23/08/2006 by CBRE
'
' Keyboard Shortcut: Ctrl+Shift+Q
'

For m = 1 To 250

With ActiveSheet.QueryTables.Add(Connection:= _

"URL;http://www.chintai.net/chintai/shuto/CGI/area_list.cgi?area=shuto_tokyo&city=1190_1200_1210 _1220_1230&s=0000000011111100000001000000003200000 000000000000000000010000000000220&p="
& m _
, Destination:=Range("A" & m + (271 * (m - 1))))
.Name = _

"area_list.cgi?area=shuto_tokyo&city=1190_1200_121 0_1220_1230&s=000000001111110000000100000000320000 0000000000000000000010000000000220&p="
& m
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertEntireRows
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlSpecifiedTables
.WebFormatting = xlWebFormattingNone
.WebTables = "12"
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

Next m

End Sub

Regards
Pelham



Don Guillett wrote:
As always, post your code for comments.
You might want
if lastrow64000 then destination sheet="sheet3"


--
Don Guillett
SalesAid Software

"Pelham" wrote in message
ups.com...
Thanks to all of you who have assisted me learn more about VBA through
this News Group - it is working for me slowly ...!

In short, I have created a 20 or so line long VBA script which works
fine for me sometimes. It gets the latest residential real estate data
for Japan's 5 major cities and copies the relevant information straight
into an Excel WorkSheet for me. The code starts with "Sub Queries()"
and ends with "End Sub", but it generates some loops within the code
depending upon how many different web pages it needs to visit each
time. The number of web pages depends upon the number of available
properties in each city whenever I choose to Run the VBA Macro.

However, sometimes there are so many properties that I reach the last
line of the destination WorkSheet and it stops short of what I want...!
Can one of you kind folk please tell me what I have write into the
script to make it automatically open a new destination WorkSheet and
continue copying the data when the current WorkSheet reaches 64,000
lines, please. Also, can you tell where in the code I need to put this
VBA command, please?

Thanks in advance.