copy paste value TO cell B2, wait for refresh, value copy entire sheet to next sheet ... loop next

[Subu]

Dear Members

I have a sp sheet that fetches data from the web using SMF functions. To avoid refreshing often , I wish to store some data and refresh once in 3 months or so

To this end, I wish to automate some basic tasks and hence this request


what do I have
----------------------
1. I have a list of tickers on cells A2 -> A 50 on Sheet titled "list"
2. I have a second sheet called "financial statement data" where the SMF functions are in place and I have something like a financial statement

what do I wish to automate using XL VBA
-----------------------------------------------------
1. go to cell A2 on sheet "list"
2. copy contents
3. then go to sheet "financial statement data"
4. value copy data in clipboard (ie which was taken from A2.list) to cell B2 of sheet "financial statement data"
5. this will refresh sheet "financial statement data"
6. then copy the entire sheet "financial statement data"
7. then create a fresh / new sheet after sheet "financial statement data"
8. Value paste clipboard to new sheet
9. Paste format to new sheet (so that the new sheet will have values and formats, but no formula)
10. Rename new sheet to value on cell B2 of new sheet
11. skip 1 or add one to a variable to move to cell A3 on sheet "list"
12. loop steps 1 to 11 above untill you reach last value on sheet "list" column A

sample sp sheet uploaded here http://bit.ly/Xvy0VR

thanks in advance for any help on this

best regards

Subu

 

[sijpie]

Very clear requirements, I wish more people would write them like this.

But your steps can be made a lot more efficient
1. go to cell A2 on sheet "list"
2. copy contents
3. then go to sheet "financial statement data"
4. value copy data in clipboard (ie which was taken from A2.list) to cell B2 of sheet "financial statement data"
Replace steps 234 with one statement setting B2 to Sheet1!A2 (well the active cell in the loop)

5. this will refresh sheet "financial statement data"
6. then copy the entire sheet "financial statement data"
7. then create a fresh / new sheet after sheet "financial statement data"
8. Value paste clipboard to new sheet
9. Paste format to new sheet (so that the new sheet will have values and formats, but no formula)

Replace steps 6 through 9 by copying the sheet (ie duplicate sheet). Then set the used range to its value.

10. Rename new sheet to value on cell B2 of new sheet
11. skip 1 or add one to a variable to move to cell A3 on sheet "list"
12. loop steps 1 to 11 above untill you reach last value on sheet "list" column A

Then some error checking thrown in as well

[/COLOR]Option Explicit

Sub UpdateTickers()
    Dim wsFSD As Worksheet, wsT As Worksheet
    Dim rIn As Range, rOut As Range
    Dim i As Long
    
    Application.ScreenUpdating = False
    
    On Error Resume Next
        Set wsFSD = Worksheets("Financial Statement Data")
        If wsFSD Is Nothing Then
            msgbox "Sheet Financial Statement Data does not exist"
            Exit Sub
        End If
        Set wsT = Worksheets("Sheet1")
        If wsT Is Nothing Then
            msgbox "Sheet1 with ticker Data does not exist"
            Exit Sub
        End If
    On Error GoTo 0
    
    Set rIn = wsT.Range("A2")
    Set rOut = wsFSD.Range("B2")
    
    Do While rIn.Offset(i, 0).Value <> vbNullString
        rOut.Value = rIn.Offset(i, 0).Value
        wsFSD.Copy after:=Sheets(Sheets.Count)
        With ActiveSheet
        On Error GoTo DblSheet
            .Name = rOut.Value & "_" & Format(Date, "yy-mm-dd")
        On Error GoTo 0
            .UsedRange.Value = .UsedRange.Value
        End With
        i = i + 1
    Loop
    
    Set wsFSD = Nothing
    Set wsT = Nothing
    Set rIn = Nothing
    Set rOut = Nothing
    Application.ScreenUpdating = True
    
    Exit Sub
DblSheet:
    msgbox "Ticker with identical name"


End Sub

 

[Fromsubu]

Very clear requirements, I wish more people would write them like this.

But your steps can be made a lot more efficient
1. go to cell A2 on sheet "list"
2. copy contents
3. then go to sheet "financial statement data"
4. value copy data in clipboard (ie which was taken from A2.list) to cell B2 of sheet "financial statement data"
Replace steps 234 with one statement setting B2 to Sheet1!A2 (well the active cell in the loop)

5. this will refresh sheet "financial statement data"
6. then copy the entire sheet "financial statement data"
7. then create a fresh / new sheet after sheet "financial statement data"
8. Value paste clipboard to new sheet
9. Paste format to new sheet (so that the new sheet will have values and formats, but no formula)

Replace steps 6 through 9 by copying the sheet (ie duplicate sheet). Then set the used range to its value.

10. Rename new sheet to value on cell B2 of new sheet
11. skip 1 or add one to a variable to move to cell A3 on sheet "list"
12. loop steps 1 to 11 above untill you reach last value on sheet "list" column A

Then some error checking thrown in as well

Option Explicit

Sub UpdateTickers()
    Dim wsFSD As Worksheet, wsT As Worksheet
    Dim rIn As Range, rOut As Range
    Dim i As Long
    
    Application.ScreenUpdating = False
    
    On Error Resume Next
        Set wsFSD = Worksheets("Financial Statement Data")
        If wsFSD Is Nothing Then
            msgbox "Sheet Financial Statement Data does not exist"
            Exit Sub
        End If
        Set wsT = Worksheets("Sheet1")
        If wsT Is Nothing Then
            msgbox "Sheet1 with ticker Data does not exist"
            Exit Sub
        End If
    On Error GoTo 0
    
    Set rIn = wsT.Range("A2")
    Set rOut = wsFSD.Range("B2")
    
    Do While rIn.Offset(i, 0).Value <> vbNullString
        rOut.Value = rIn.Offset(i, 0).Value
        wsFSD.Copy after:=Sheets(Sheets.Count)
        With ActiveSheet
        On Error GoTo DblSheet
            .Name = rOut.Value & "_" & Format(Date, "yy-mm-dd")
        On Error GoTo 0
            .UsedRange.Value = .UsedRange.Value
        End With
        i = i + 1
    Loop
    
    Set wsFSD = Nothing
    Set wsT = Nothing
    Set rIn = Nothing
    Set rOut = Nothing
    Application.ScreenUpdating = True
    
    Exit Sub
DblSheet:
    msgbox "Ticker with identical name"


End Sub

thanks a ton

Really appreciate your detailed reply


regards

subu

 

[Fromsubu]

thanks a ton

Really appreciate your detailed reply


regards

subu

dear Sijpie

this copies the same sheet again and again !! i.e. it looks like it does a value copy before the sheet is updated with the new ticker

This is what I did

1. I copied the code to the command / VBA screen
2. checked that the "sheet 1" in your code is changed to "list" - my original file, sheet name
3, Then hit the play button
4, It created sheets with ticker name and date etc (the date was a cool idea), but the values were all the same !! ;-(

could you please help ?

thanks

regards

Subu

 

[sijpie]

It works like a charm on a testsheet I have. What I have done in the code below is to add a line to do a calculation before the sheet is copied. I am assuming that if the tickervalue is put into 'Financial Statement Data'!B2, the sheet will update with new calculated data.

If the data are pulled out of a database, then try uncommenting the line indicated in the code.

Sub UpdateTickers()
    Dim wsFSD As Worksheet, wsT As Worksheet
    Dim rIn As Range, rOut As Range
    Dim i As Long
    
    Application.ScreenUpdating = False
    
    On Error Resume Next
        Set wsFSD = Worksheets("Financial Statement Data")
        If wsFSD Is Nothing Then
            MsgBox "Sheet Financial Statement Data does not exist"
            Exit Sub
        End If
        Set wsT = Worksheets("Sheet1")
        If wsT Is Nothing Then
            MsgBox "Sheet1 with ticker Data does not exist"
            Exit Sub
        End If
    On Error GoTo 0
    
    Set rIn = wsT.Range("A2")
    Set rOut = wsFSD.Range("B2")
    
    Do While rIn.Offset(i, 0).Value <> vbNullString
        rOut.Value = rIn.Offset(i, 0).Value ' copy next ticker
        Application.Calculate
        ' *** if data is pulled from external source, then
        ' *** uncomment next line
        ' Application.CalculateUntilAsyncQueriesDone
        wsFSD.Copy after:=Sheets(Sheets.Count)
        With ActiveSheet
        On Error GoTo DblSheet
            .Name = rOut.Value & "_" & Format(Date, "yy-mm-dd")
        On Error GoTo 0
            .UsedRange.Value = .UsedRange.Value
        End With
        i = i + 1
    Loop
    
    Set wsFSD = Nothing
    Set wsT = Nothing
    Set rIn = Nothing
    Set rOut = Nothing
    Application.ScreenUpdating = True
    
    Exit Sub
DblSheet:
    MsgBox "Ticker with identical name"




End Sub