PDA

View Full Version : Manipulate data in a separate worksheet



lcpx
03-30-2006, 01:46 AM
I am developing a report which has 2 worksheets. One is called Menu and another one is ?REPORT?. Basically we have several buttons in the MENU worksheet, when user click on the button, the data will be populated to the REPORT sheet.

Everything is fine when Buttons and Report are in the same worksheet. But if I put the button in a separate worksheet, when I click the button, it just returns error says: Method ?Range? of object ?_WorkSheet? failed and hightlight the line ws.Range(Cells(2, 7), Cells(row, 7)).delete.


Private Sub CommandButton2_Click()

Application.DisplayAlerts = False
Dim row As Long
Dim ws As Excel.Worksheet

Set ws = Worksheets("REPORT")
row = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

Worksheets("REPORT").Select
Worksheets("REPORT").Activate

If row<= 2 Then
row = 2
End If

ws.Range(Cells(2, 1), Cells(row, 7)).delete
Call PopulateData

Application.DisplayAlerts = True

ActiveWindow.FreezePanes = False
Worksheets("REPORT").Range("A3").Activate
ActiveWindow.FreezePanes = True

End Sub


So I have to use another way to do it. I put exactly same code to a new sub in the module.


Public Sub DeleteData()
Application.DisplayAlerts = False
Dim row As Long
Dim ws As Excel.Worksheet

Set ws = Worksheets("REPORT")
row = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

Worksheets("REPORT").Select
Worksheets("REPORT").Activate

If row<= 2 Then
row = 2
End If

ws.Range(Cells(2, 1), Cells(row, 7)).delete
Call PopulateData

Application.DisplayAlerts = True

ActiveWindow.FreezePanes = False
Worksheets("REPORT").Range("A3").Activate
ActiveWindow.FreezePanes = True

End Sub


Then call this SUB in report page

Private Sub CommandButton2_Click()
Call DeleteData
Call PopulateData

ActiveWindow.FreezePanes = False
Worksheets("REPORT").Range("A3").Activate
ActiveWindow.FreezePanes = True
End Sub


It works fine, but I just don?t understand why my first solution doesn?t work. Why I have to put the code in a separate SUB and call it in the worksheet. What?s the difference?

Thanks very much for your time and help!

Best regards,
Pete

jindon
03-30-2006, 02:09 AM
Hi

When you use Cells property, you need to specify its parent object
that is Worksheet in this case, so

ws.Range(ws.Cells(....),ws.Cells(....))

should work properly

lcpx
03-30-2006, 02:31 AM
Thanks a lot, I guess I know what's wrong. Cheers!