Web query to extract web table from multiple URL - Help needed

Friends,

Requirement: To do a web query via excel and download web-tables from multiple URL
Issue:

1. The # of rows in web table is dynamic(i.e) for first URL, # of lines maybe 10, whereas for URL-10, the # of lines may be 30. how do i assign Range("Cell").Select and Destination:=Range("$column$row") dynamically? For now, i have hardcoded the values and hence it is overwriting the data.

2. The macro attached doesn't work bcz of too many lines of code. Should i need to split it multiple modules and execute them? Since the # of URL from which the data needs to be extracted is over 1000, can someone please help me to simplify this macro?

Can you please advice?