VBA Copy, Paste to Next Column, Repeat

[JonesyUK]

Hi All,

Please can you help?

I have copied the following Refresh1 macro 65 times... (Refresh1 to Refresh 65)

It updates info from the internet, then copies the data every 15 minutes from "NewData" to a new column (starting at "D4:D24" and ending at column "BP4:BP24". i.e. it runs all day from 8am to 11pm.

Please can you suggest a better way of doing this than having 65 macros?? the only change in each macro is Range("D4:D24").Select

Thank You!

Sub Refresh1()
ThisWorkbook.RefreshAll
Application.Wait TimeSerial(Hour(Now), Minute(Now), Second(Now) + 5) [small delay to update numbers before copy]
Worksheets("Start").Activate
Range("NewData").Select
Selection.Copy
Range("D4:D24").Select [next macro uses "E4:E24", then "F4:F24" etc... ]
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Worksheets("Analysis").Activate
ActiveSheet.Range("H1").Select
Application.Wait TimeSerial(Hour(Now), Minute(Now) + 15, Second(Now))
End Sub

 

[fadee2]

try it on a copy of your workbook

Sub Refresh1()
For i = 4 To 69
    ThisWorkbook.RefreshAll
    Application.Wait TimeSerial(Hour(Now), Minute(Now), Second(Now) + 5) '[small delay to update numbers before copy]
    Worksheets("Start").Activate
    Range("NewData").Select
    Selection.Copy
    Range(Cells(4, 4), Cells(24, i)).Select '[next macro uses "E4:E24", then "F4:F24" etc... ]
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Worksheets("Analysis").Activate
    ActiveSheet.Range("H1").Select
    Application.Wait TimeSerial(Hour(Now), Minute(Now) + 15, Second(Now))
Next i
End Sub

 

[JonesyUK]

try it on a copy of your workbook

Sub Refresh1()
For i = 4 To 69
    ThisWorkbook.RefreshAll
    Application.Wait TimeSerial(Hour(Now), Minute(Now), Second(Now) + 5) '[small delay to update numbers before copy]
    Worksheets("Start").Activate
    Range("NewData").Select
    Selection.Copy
    Range(Cells(4, 4), Cells(24, i)).Select '[next macro uses "E4:E24", then "F4:F24" etc... ]
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
    :=False, Transpose:=False
    Application.CutCopyMode = False
    Worksheets("Analysis").Activate
    ActiveSheet.Range("H1").Select
    Application.Wait TimeSerial(Hour(Now), Minute(Now) + 15, Second(Now))
Next i
End Sub

Thanks, it's giving error, variable not defined... is it "Dim i as long" ?

 

[fadee2]

Thanks, it's giving error, variable not defined... is it "Dim i as long" ?

yes

 

[Osvaldo Palmeiro]

Another approach:

Sub Refresh1()
  ThisWorkbook.RefreshAll
  Application.Wait TimeSerial(Hour(Now), Minute(Now), Second(Now) + 5) '[small delay to update numbers before copy]
   Sheets("Start").Range("NewData").Copy
   If Sheets("Start").[D4] = "" Then
    Sheets("Start").[D4].PasteSpecial xlValues
   Else: Sheets("Start").Cells(4, Columns.Count).End(1)(1, 2).PasteSpecial xlValues '[next macro uses "E4:E24", then "F4:F24" etc... ]
   End If
  Application.CutCopyMode = False
  Application.Wait TimeSerial(Hour(Now), Minute(Now) + 15, Second(Now))
End Sub

 

[JonesyUK]

Thank you everyone...