Want to record value from a live data after each 30 minuts

Welcome to the forum
Try this:
(tested on Excel 2016)

Amend Sheet1 to correct sheet name

Run StartTimer and updates occur every 30 minutes (the first update happens 30 minutes later)
- to test, change the value to 1 to update every minute and the first update happens 1 minute later
Running StopTimer stops the updating

Paste into a general module

Please Login or Register  to view this content.

Paste into ThisWorkbook module

Please Login or Register  to view this content.

Suggested query settings

QueryProperties.jpg

Have you looked at the link to Chip Pearson's page containing the original code? It contains much more than the code - very informative.
http://www.cpearson.com/excel/OnTime.aspx
The code should work with Excel 2010.
The most likely reason is that a typo has crept in somewhere.
When I get back to my PC, I will upload a file containing the code for you.

Just a thought
- where did you paste the Constants?
- they need to be at the TOP of the module BEFORE all procedures

I am totally baffled by your difficulties

This I what I did starting from scratch
- opened a new workbook
- added a data table of windspeeds from the internet
- copy/pasted the code from the Thread directly into a General module
- ran StartTimer
- it worked without a hitch
- everything the same the same EXCEPT 30 minutes changed to 1 minute because I did not want to wait

Workbook attached

I will look at your workbook tomorrow

Procedure Test has been added to your workbook to illustrate how to achieve what you want
- run in attached workbook with {CTRL} k
- dummy data included in first few rows of BC:BG to test the procedure
- range details are printed to the immediate window for testing
(make immediate window in VBA visible with {CTRL} G OR click View\ImmediateWindow)

ImmediateWindow.jpg

VBA needs a way to automatically determine the FirstCell to copy in column BB (in case you insert row above 171 later)
- NamedRange BB_Header created with RefersTo formula =SelectedCoins!$BB$171
- NamedRange formula automatically adjusts if you insert a row or column
- The VBA is told that FirstCell is one row below cell BB_Header

The PasteTo and CopiedFrom ranges are all determined based on FirstCell

Please Login or Register  to view this content.

see solution in previous post

In first post you said

II have web connection in a3:a1071 and this cells value will change after every thirty minutes. Before updating I want to keep a3:a1071 cells data to other cells for every thirty minutes. Such as B3:b1071 for 1st 30 minutes, c3:c1071 for second 30 minutes, d3:d1071 for 3rd next 30 minutes.

But all those columns were incorrect, and there are Headers placed in row 171 which complicates things

The solution in post#10 does what you require below your headers in row 171, but what do you want to happen above row 171?

If you want to include all rows from 3 to 1071 then here is one method to achieve that which is to overwrite the headers and then re-instate them
- set FirstCell= Range("BB3")
- Named Range BB_Header is not required and can be deleted
- the procedure now overwrites your headers in row 171
- use this syntax to re-instate your headers after pasting occurs

Please Login or Register  to view this content.