Skip to Content

Refresh sheet

Hi,

My workbook contains 3 sheets.Sheet 2 & sheet3 contains two different Queries.

Sheet1 is teh summary of Sheet2 & sheet3.

When I execute the workbook for the first time , all the data is populated correctly.for eg . sheet1 displayes 1000 rows.If i refresh the queries again with some selection criteria , & if only 100 records are extracted, then sheet1 displys first 100 rows corretcly & the remaining 900 rows are displayed as # , as sheet1 was not cleared after the query refresh

I want to refresh sheet1 , which does not contain any queries .

Reagrds,

Anita

Message was edited by:

Anita Prasanna

Add a comment
10|10000 characters needed characters exceeded

Related questions

2 Answers

  • Posted on Jan 10, 2007 at 11:54 AM

    Hi,

    You may have to clear Target Area after refresh.

    it can be done using VBA code

    we do it with Call ClearTargetArea("C10:I28", gv_worksheet_input)

    where 'input' is sheet name.in ypur case it is sheet1 and C10:I28 varies depending on your result area.

    Hope this helps.

    Add a comment
    10|10000 characters needed characters exceeded

    • here is the VBA code we are using for the same .(defined in module).

      Take any VBA Programmer help.

      Public Sub clear_all()

      Clear_All_Query_Fields

      End Sub

      ' -


      ' Function to Protect the Data_input sheet

      Public Sub Protect_cockpit_chart()

      data_input.Protect gc_password

      End Sub

      ' -


      ' Function to Clear the content of a range

      Public Function ClearTargetArea(in_RangeAddress As String, in_worksheet As String)

      'Dim TargetArea As Range

      If in_worksheet <> vbNullString Then

      ActiveWorkbook.Worksheets(in_worksheet).Range(in_RangeAddress).Cells.Value = gc_xlsNoContent

      End If

      End Function

      ' -


      ' Function to clear the data input table

      Public Function Clear_All_Query_Fields(Optional in_keyf As String = "ALL")

      ' ------------------------------+

      ' Clear headline in input sheet

      Call ClearTargetArea("D9:I9", gv_worksheet_input)

      ' Clear data in input sheet

      Call ClearTargetArea("C10:I28", gv_worksheet_input)

      ' ------------------------------+

      End Function

      Public Sub FormulaTransfer(SAPQueryResult As Range, _

      BlattNamen As String, _

      FromQueryColumn As Integer, _

      FromQueryRow As Integer, _

      ToSAPRow As Integer, _

      ToSAPColumn As Integer)

      Dim mc As Range

      Set mc = SAPQueryResult.Cells(FromQueryRow, FromQueryColumn)

      data_input.Cells(ToSAPRow, ToSAPColumn).Value = mc.Value

      End Sub

      Assign points if its useful.

  • Posted on Jan 11, 2007 at 05:49 AM

    Hi,

    Perform the following step at the end.

    Adjust all the columns in worksheet 1, programatically to solve this...

    Regards,

    Vinay

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.