How to write a macro 30 Nov?

[Eric]

Does anyone have any suggestions on how I can insert the Counter into the web
link? and change the column location for pasting selection?

Following code is generated by recording Macro, please see Coding section

If the counter is 1, then paste into Columns("A:A").Select under Summary
worksheet.

If the counter is 2, then paste into Columns("B:B").Select under Summary
worksheet.

....

If the counter is 10, then paste into Columns("J:J").Select under Summary
worksheet.

Does anyone have any suggestions?
Thanks in advance forany suggestions
Eric


===============
Coding
===============

For Counter = 1 to 10
'Your code
Next Counter


With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.uwants.com/forumdisplay.php?fid=845&page=1",
Destination:= _
Range("A1"))
.Name = "forumdisplay.php?fid=845&page=1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertEntireRows
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With
ActiveWindow.LargeScroll ToRight:=2
Columns("F:F").Select
Selection.Copy
Sheets("Sheet2").Select
Columns("A:A").Select
ActiveSheet.Paste
End Sub

 

[Mike H]

Eric,

I'm not entirely clear what your trying to do but this does what I think
your asking. Note a couple of things. I 've stopped all the sheet selection
it isn't necessary and I don't paste into columns "A:A" I user the variable
'counter to select the column to paste in to.

Sheets("Sheet2").Columns(counter).PasteSpecial


Sub sonic()
For counter = 1 To 10
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.uwants.com/forumdisplay.php?fid=845&page=1",
Destination:=Range("A1"))
.Name = "forumdisplay.php?fid=845&page=1"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertEntireRows
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

Columns("F:F").Copy
Sheets("Sheet2").Columns(counter).PasteSpecial
Next counter
End Sub

 

[Eric]

Thank you very much for suggestions

Could you please give me any suggestions on how to edit the link?
"URL;http://www.uwants.com/forumdisplay.php?fid=845&page=1"
I would like to retrieve the value from counter and insert into 1
If counter is 1, then
"URL;http://www.uwants.com/forumdisplay.php?fid=845&page=1"

If counter is 2, then
"URL;http://www.uwants.com/forumdisplay.php?fid=845&page=2"

....

If counter is 10, then
"URL;http://www.uwants.com/forumdisplay.php?fid=845&page=10"

Do you have any suggestions
Thank everyone very much for any suggestions
Eric

 

[Don Guillett]

Establish your web query and then refresh INSTEAD of adding again by using
this.

Sub RefreshAndCopy()
Sheets("sheet1").QueryTables(1).Refresh

With Sheets("sheet2")
lc = .Cells.Find(What:="*", _
LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column + 1
'MsgBox lc
Sheets("sheet1").Columns("f").Copy .Columns(lc)
End With
End Sub

 

[Eric]

Thank everyone very much for suggestions

Could you please give me the example with loop and web link location?
I get no idea where to place my web link, and loop
Thank everyone very much for any suggestions
Eric

 

[Rick Rothstein]

Assuming the link is stored in a variable named Link and the counter is
stored in a variable named Counter AND assuming the page value is ALWAYS
located at the end of the link's text (that is very important), then this
will change the page to the value of the counter...

Link = Left(Link, InStrRev(Link, "=")) & Counter

 

[Eric]

Thank everyone very much for suggestions

Do you mean like following codes?
There is some error on following coding
Does anyone have any suggestions?
Thank everyone very much for any suggestions
Eric

Sub sonic()
Link = "URL;http://www.uwants.com/forumdisplay.php?fid=845&page=1"

For counter = 1 To 10
Link = Left(Link, InStrRev(Link, "=")) & Counter
With ActiveSheet.QueryTables.Add(Connection:= _
Link, Destination:=Range("A1"))
.Name = "Link"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = True
.BackgroundQuery = True
.RefreshStyle = xlInsertEntireRows
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

Columns("F:F").Copy
Sheets("Sheet2").Columns(counter).PasteSpecial
Next counter
End Sub

 

[Don Guillett]

After you establish the FIRST one in sheet 1, then use this to loop thru the
10 to refresh and copy col F to the next available column in sheet 2. Hide
the first 22 rows on sheet2. Contact me privately if desired.

Sub queryloop()
For i = 2 To 10 'number desired
With Sheets("sheet1").QueryTables(1)
.Connection = "URL;http://www2.uwants.com/forumdisplay.php?fid=845&page="
& i
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

With Sheets("sheet2")
cc = .Columns.Count
'MsgBox cc
lc = .Cells.Find(What:="*", _
LookIn:=xlFormulas, SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column + 1
'MsgBox lc
Sheets("sheet1").Columns("f").Copy .Columns(lc)
End With
Next i
End Sub

 

[Don Guillett]

Actually, could be a little simpler

Sub queryloop()
For i = 2 To 4
With Sheets("sheet1").QueryTables(1)
.Connection = "URL;http://www2.uwants.com/forumdisplay.php?fid=845&page=" &
i
.WebSelectionType = xlEntirePage
.WebFormatting = xlWebFormattingNone
.WebPreFormattedTextToColumns = True
.WebConsecutiveDelimitersAsOne = True
.WebSingleBlockTextImport = False
.WebDisableDateRecognition = False
.WebDisableRedirections = False
.Refresh BackgroundQuery:=False
End With

Sheets("sheet1").Columns("f").Copy Sheets("sheet2").Columns(i)

Next i
End Sub