VB code to clear entire spreadsheet data (contents)

[tgifgemini]

Hi all,
I need some input from you smart folks. I am populating an excel spreadsheet. Each time, I want to clear the spreadsheet content before repopulating it. I am using the codes below and it is clearing the first 3 rows in the spreadsheet and every other data stays in tact.
This is is my code:

xlWksht.Activate
    'xlWksht.Range("A1").Activate
    xlApp.Selection.CurrentRegion.Select
    xlApp.Selection.ClearContents

Any help will be appreciated.
Thanks,
tgifgemini.

[QVeen72]

Hi tgif,

u can clear the entire content.. but easy way round is to: First have a Blank File which is properly formatted, and When u want to create one, Copy the BlankFile to new Filename and modify it. Next when u want o create one more, Create with a new name or Kill previos file and copy to same name. Check This:

Say ur File Name is "C:\Myxl.xls"

If Dir("C:\myxlnew.xls",VbDirectory) <> "" Then
  Kill "C:\myxlnew.xls"
End If
FileCopy "C:\myxl.xls","C:\myxlnew.xls"

Regards
Veena

[tgifgemini]

Good morning Veena,
thanks for your quick response as always. I set my code as you instructed but I am getting "Run-Time error 70 - permission denied" on this line:

Kill "C:\BiWeeklyPeriod.xls"

This is my entire code:

If Dir("C:\BiWeeklyPeriod.xls", vbDirectory) <> "" Then
       Kill "C:\BiWeeklyPeriod.xls"
    End If
    
    FileCopy "C:\BiWeeklyPeriodTemplate.xls", "C:\BiWeeklyPeriod.xls"

Also what is the syntax to clear Entire spreadsheet?
Thanks,
tgifgemini.

[QVeen72]

Hi,

I think u can open a worksheet..
try this code:

wrksheetobj.Cells.Clear

Regards
Veena

[tgifgemini]

Hi Veena,
thanks. I accomplished the objective with this code:

xlWksht.UsedRange.ClearContents

tgifgemini.