Thank you so much, it did work, can you also help me how to close both the sourcesheet and destination sheet after cut&paste in run; here is my code:
P.S. I am still trying to find out how to rate your answer, I will though..
Private Sub CommandButton6_Click()
If Environ("USERNAME") <> "a80438" Then
MsgBox "This is not your button, please select your button!"
Exit Sub
Else
Dim findlastrow As Long
Worksheets("sheet1").Select
findlastrow = Range("a2").End(xlDown).Row
Dim y As Long
y = findlastrow - 100
Dim FName As String
Dim FPath As String
FPath = "#"
FName = "# & ".xls"
Dim summarysheet As Worksheet
Dim folderpath As String
Dim sourcerange As Range
Dim destrange As Range
Dim colrange As Range
Dim WorkBk As Workbook
Dim filename As String
If Dir(FPath & "\" & FName) <> "" Then
MsgBox "File " & FPath & "\" & FName & " already exists"
Exit Sub
Else
Set sourcerange = Range(Rows

, Rows(findlastrow))
Set summarysheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
summarysheet.Select
Set destrange = summarysheet.Range("a2")
sourcerange.Cut Destination:=destrange
summarysheet.Columns.AutoFit
summarysheet.Select
Range("a1").Value = "Customer_Number"
Range("b1").Value = "Account_Number"
Range("c1").Value = "Name_1"
Range("d1").Value = "SSN_1"
Range("e1").Value = "Name_2"
Range("f1").Value = "SSN_2"
Range("g1").Value = "Status"
Range("h1").Value = "SSN_1"
Range("i1").Value = "SSN_2"
Range("j1").Value = "DOB_1"
Range("k1").Value = "DOB_2"
Range("l1").Value = "Comments"
With summarysheet.Range("G2:g200").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Complete, Pend, Unable to Locate"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
With summarysheet.Range("h2:k200").Validation
.Delete
.Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, _
Operator:=xlBetween, Formula1:="Vrfd,Update,Other"
.IgnoreBlank = True
.InCellDropdown = True
.InputTitle = ""
.ErrorTitle = ""
.InputMessage = ""
.ErrorMessage = ""
.ShowInput = True
.ShowError = True
End With
summarysheet.SaveAs filename:=FPath & "\" & FName
'''' I like to close both workbooks here
End If
End If
End Sub