mdeshazo
New Member
- Joined
- Dec 30, 2002
- Messages
- 17
I am working on creating a smarter form for our clients. They only have access to a webpage which I believe I can have them copy from and paste into excel. From there, after they paste, I want to run a macro that will scrape through and grab the necessary data to help them fill out their form.
I am running Excel 2007 (as are the vast majority of my users).
I have figured I either need to use worksheet_calculate() or worksheet_change. In an effort to keep it simple I am using worksheet_calculate() so that when the user pastes his/her screen scrape into the worksheet (same place every time (cell B2)), then in M1 I have the formula "=len(left(B3,7))", which should always return the value of 7.
What I am hoping to do is not only grab the proper data (pasting it to a worksheet titled "Input") from what the user pastes, but also once the macro is completed, then the macro would go to worksheet titled "Input" and delete the original worksheet the user pasted into.
Below is the code I have devised thus far (for scraping the data) and it isn't working.
Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Application.EnableEvents = False
Select Case Range("M1").Value
Case Is = 7
' Write Budget Number in Cell M3
Cells(3, 13).FormulaR1C1 = "=left(R3C2,7)"
Case Is <> 7
GoTo ErrHnd
End Select
ErrHnd:
Application.ScreenUpdating = True
Application.EnableEvents = True
Response = MsgBox("I didn't work
", vbExclamation + vbOKCancel, "Dang It!")
Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
I am running Excel 2007 (as are the vast majority of my users).
I have figured I either need to use worksheet_calculate() or worksheet_change. In an effort to keep it simple I am using worksheet_calculate() so that when the user pastes his/her screen scrape into the worksheet (same place every time (cell B2)), then in M1 I have the formula "=len(left(B3,7))", which should always return the value of 7.
What I am hoping to do is not only grab the proper data (pasting it to a worksheet titled "Input") from what the user pastes, but also once the macro is completed, then the macro would go to worksheet titled "Input" and delete the original worksheet the user pasted into.
Below is the code I have devised thus far (for scraping the data) and it isn't working.
Private Sub Worksheet_Calculate()
Application.ScreenUpdating = False
Application.EnableEvents = False
Select Case Range("M1").Value
Case Is = 7
' Write Budget Number in Cell M3
Cells(3, 13).FormulaR1C1 = "=left(R3C2,7)"
Case Is <> 7
GoTo ErrHnd
End Select
ErrHnd:
Application.ScreenUpdating = True
Application.EnableEvents = True
Response = MsgBox("I didn't work
", vbExclamation + vbOKCancel, "Dang It!")Application.ScreenUpdating = True
Application.EnableEvents = True
End Sub
Last edited:
