Refresh sheet

[anita_prasanna2]

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

[vinay_bedre2]

Hi,

Perform the following step at the end.

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

Regards,

Vinay

[former_member188325]

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.

[anita_prasanna2]

Hi Murali,

How i will know the range of it , as it is dynamic.Depending on the no of rows in sheet2 it gets updated in sheet1.

Regards,

Anita

[former_member188325]

then you may go for maximum possible range.

[anita_prasanna2]

Hi Murali,

I tried by giving Call ClearTargetArea its says sub or function not defined.

Please check this Sub , may be some code is written inside that.

Reagrds,

ANita

[former_member188325]

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.