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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
73,092
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
73,092
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
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,714
Messages
5,833,276
Members
430,201
Latest member
Deepakpilla36

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top