Excel 2003 button issue
 

hi all, i have a button and when pressed it copies sheets to another external
workbook, the problem i'm having is when i need it to clear the content it
clears the header row. I need it to keep the header row but delete the
content.

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
Set wb2 = Workbooks.Open("\\Business Objects\CHR\Export of SGUK.xls")
Set ws = wb2.Sheets("Sheet1")
ws.Cells.ClearContents 'i think this is where i'm going wrong.

Help would be greatly appreciated.

Excel 2003 button issue
 

One way is to use the usedrange and define the range starting from cell A2...

ws.Range("A2", Cells(ws.UsedRange.Rows.Count, _
ws.UsedRange.Columns.Count)).ClearContents

--
Jacob


"Neil Holden" wrote:

hi all, i have a button and when pressed it copies sheets to another external
workbook, the problem i'm having is when i need it to clear the content it
clears the header row. I need it to keep the header row but delete the
content.

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
Set wb2 = Workbooks.Open("\\Business Objects\CHR\Export of SGUK.xls")
Set ws = wb2.Sheets("Sheet1")
ws.Cells.ClearContents 'i think this is where i'm going wrong.

Help would be greatly appreciated.

Excel 2003 button issue
 

Another way:

With ws
.Range("a2", .Cells(.Cells.Count)).ClearContents
End With

..cells(.cells.count) is the last cell (IV65536 in xl2003) in the worksheet.

Neil Holden wrote:

hi all, i have a button and when pressed it copies sheets to another external
workbook, the problem i'm having is when i need it to clear the content it
clears the header row. I need it to keep the header row but delete the
content.

Application.DisplayAlerts = False
Application.ScreenUpdating = False
Set wb1 = ActiveWorkbook
Set wb2 = Workbooks.Open("\\Business Objects\CHR\Export of SGUK.xls")
Set ws = wb2.Sheets("Sheet1")
ws.Cells.ClearContents 'i think this is where i'm going wrong.

Help would be greatly appreciated.

--

Dave Peterson