VBA Range cut error

[ashkankord]

Hi, I need some help with the syntax here; my code is trying to cut a range of cells and past them onto a new sheet created by vba, when I use range.copy it workd perfeclt fine but when I do range.cut which is what I want I get the error 1004 application defined or object defined error. Here is the code, I realy appreciate some help here. is there another method used for cut since it changes the original sheet? I am not too sure, just asking..Thanks.

Range(Rows, Rows(findlastrow)).Copy
Set summarysheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
summarysheet.Select
Set destrange = summarysheet.Range("a2")
destrange.PasteSpecial

 

[Fluff]

Hiya
Try something like this

    Set SourceRange = Range(Rows(y), Rows(findlastrow))
    Set summarysheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
    summarysheet.Select
    Set destrange = summarysheet.Range("a2")
    SourceRange.Cut Destination:=destrange

 

[ashkankord]

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

 

[Fluff]

Hi
For the summarysheet you could try

Workbooks(FName).Close savechanges:=False

& for the source sheet either replace the FName in the above code with the filename or alternatively

ActiveWorkbook.Close savechanges:=False