delete part of a sheet

  • Thread starter Thread starter bob
  • Start date Start date
B

bob

how would i delete part of a worksheet. the following deletes a worksheet
and copies new data to A1 but i want to leave the first 5 rows protected &
just delete the remaining rows in the sheet then copy source data to C6.


'delete data sheet each time before copy
Application.DisplayAlerts = False 'trun off del warning
Windows("test.xls").Activate
' Sheets("data").Visible = True 'unhide so can copy to
Sheets("data").Select
ActiveWindow.SelectedSheets.Delete
Application.DisplayAlerts = True 'trun warning back on
Windows("Book1").Activate
' COPY to test workbook , Data sheet, place at beginning
ActiveWorkbook.ActiveSheet.Copy Before:=Workbooks("test.xls").Sheets(1)
' Rename first sheet
Workbooks("test.xls").Sheets(1).Name = "data"


thanks
 
Two scenerios...

either you can first copy the first five rows to book1
like:

Rows("1:5").Select
Selection.Copy
Windows("Book1").Activate
sheets("Sheet1").select
Range("A1").Select
Selection.Insert Shift:=xlDown

then paste this sheet after deleting data sheet and your
code....
or delete from row 6 onwards in data sheet..

Windows("test.xls").Activate
' Sheets("data").Visible = True 'unhide so can copy
to
Sheets("data").Select
Rows("6:65536").delete
ActiveWindow.SelectedSheets.Delete

Windows("Book1").Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy
Windows("test.xls").Activate
Range("A6").Select
ActiveSheet.Paste


' Rename first sheet
Workbooks("test.xls").Sheets(1).Name = "data"



Abdul Salam
 
Back
Top