Excel cut-paste

Theglyde

New Member
Joined
May 29, 2020
Messages
34
Office Version
  1. 365
Platform
  1. Windows
I have two different excel workbook

one is work in progress, other is archived work that is done

practically, I want to open

« Work »
with my cursor select 10rows in a row, when highlighted

click on button with macro that opens « Archived »
Cuts the rows out of « work » that I selected before clicking button and paste then at the end of column A of Archived. I have all coding that opens, that saves and closes... I have also code that will find last row. What I am missing is how tu cut out the info I have selected before the macro button and paste it
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
It would be much easier for other to modify the code. So, please post your code rather than explanation.
To cut and paste entire rows, we simply use this:
VBA Code:
Workbooks("myBook1.xlsm").Worksheets("mySheet1").Range("1:10").Cut Workbooks("myBook2.xlsm").Worksheets("mySheet1").Range("1:10")
 
Upvote 0
It would be much easier for other to modify the code. So, please post your code rather than explanation.
To cut and paste entire rows, we simply use this:
VBA Code:
Workbooks("myBook1.xlsm").Worksheets("mySheet1").Range("1:10").Cut Workbooks("myBook2.xlsm").Worksheets("mySheet1").Range("1:10")

Thank you, new to this, I did coding a bit a long time ago and find myself doing lots lately and forgot some.

in your example above though the range would always be the same. My problem is we have 10 jobs in a row and sometimes it might be row 1 to 10,


other time might be row 4 to 16 hence why I need to code so that what ever row I selected before pressing the Button macro is my range
 
Upvote 0
Try this.
Edit your variables, workbook names as your requirements.
VBA Code:
Sub cut()
    Dim lastrow As Long
    Application.CutCopyMode = False
    Application.ScreenUpdating = False

    'Select your required rows
    Selection.Copy
    Workbooks.Open ("myBook.xlsx") 'You need to provide the full path of your workbook here.
    lastrow = Range("A" & Rows.Count).End(xlUp).Row + 1
    Range("A" & lastrow).PasteSpecial xlPasteAll
    Workbooks("myBook.xlsx").Close SaveChanges:=True
    Selection.Delete

    Application.ScreenUpdating = True
    Application.CutCopyMode = True
  
End Sub
 
Last edited:
Upvote 0
Try this.
Edit your variables, workbook names as your requirements.
VBA Code:
Sub cut()
    Dim lastrow As Long
    Application.CutCopyMode = False
    Application.ScreenUpdating = False

    'Select your required rows
    Selection.Copy
    Workbooks.Open ("myBook.xlsx") 'You need to provide the full path of your workbook here.
    lastrow = Range("A" & Rows.Count).End(xlUp).Row + 1
    Range("A" & lastrow).PasteSpecial xlPasteAll
    Workbooks("myBook.xlsx").Close SaveChanges:=True
    Selection.Delete

    Application.ScreenUpdating = True
    Application.CutCopyMode = True
 
End Sub
Thank you so much, will give this a shot... Greatly appreciated
 
Upvote 0
Tried your code above... Almost perfect, It does take what I selected, it opens the files, saves and then gives me error code on the line

Workbooks("myBook.xlsx").Close SaveChanges:=True

but it did copy what I had selected. I guess it is not closing and saving « mybook.xlsx » and because of that does it make it to the selection.delete but greatful for this code... had been looking for a while... if you can see whats wrong please let me know. The only difference I have which should not change anything is that my files is .xlsm

thanks again
 
Upvote 0
Looks like, you change the path only to open the workbook.
The workbook referenced here
Workbooks("myBook.xlsx").Close SaveChanges:=True
is the same workbook that you opened earlier.
Means
Workbooks.Open ("myBook.xlsx")
And
Workbooks("myBook.xlsx").Close
both are referencing the same workbook. So if you put the same filepath on both, then it should work.
Please let me know if not.
 
Upvote 0
Looks like, you change the path only to open the workbook.
The workbook referenced here
Workbooks("myBook.xlsx").Close SaveChanges:=True
is the same workbook that you opened earlier.
Means
Workbooks.Open ("myBook.xlsx")
And
Workbooks("myBook.xlsx").Close
both are referencing the same workbook. So if you put the same filepath on both, then it should work.
Please let me know if not.
I did, I copied the path and pasted in both... I don’t know why not working. It did open it so I don’t see why not closing
 
Upvote 0
I did, I copied the path and pasted in both... I don’t know why not working. It did open it so I don’t see why not closing

I thought maybe it was an error because the files was on server and maybe to slow to save, but moved it to my computer, changed the path to the one on desktop and it gave me the same bug at that line
 

Attachments

  • EC00C0AA-BFBD-43E3-95B6-383CFA122D39.jpeg
    EC00C0AA-BFBD-43E3-95B6-383CFA122D39.jpeg
    13.1 KB · Views: 6
Upvote 0
I thought maybe it was an error because the files was on server and maybe to slow to save, but moved it to my computer, changed the path to the one on desktop and it gave me the same bug at that line
1BCE2987-0FF8-4045-9081-0B59C02D5671.jpeg
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

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
Back
Top