VBA Range cut error

ashkankord

New Member
Joined
May 9, 2013
Messages
6
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(y), Rows(findlastrow)).Copy
Set summarysheet = Workbooks.Add(xlWBATWorksheet).Worksheets(1)
summarysheet.Select
Set destrange = summarysheet.Range("a2")
destrange.PasteSpecial
 

Some videos you may like

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
Hiya
Try something like this
Code:
    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

New Member
Joined
May 9, 2013
Messages
6
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(y), 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
 
Last edited:

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
46,340
Office Version
  1. 365
Platform
  1. Windows
Hi
For the summarysheet you could try
Code:
Workbooks(FName).Close savechanges:=False
& for the source sheet either replace the FName in the above code with the filename or alternatively
Code:
ActiveWorkbook.Close savechanges:=False
 

Watch MrExcel Video

Forum statistics

Threads
1,109,041
Messages
5,526,417
Members
409,701
Latest member
nitmani

This Week's Hot Topics

Top