This Works for me now - Sub CopyToBook() Set y = Workbooks.Open("Path to the file") Workbooks("Book2.xlsm").Worksheets("Sheet1").Range("C22").Copy y.Sheets("Sheet1").Range("B5").PasteSpecial End Sub
OMG I have been a programmer for centuries and written so many Excel macros. Today I hit problems with Cut and Paste and could not understand why! Looking for a solution , I found this wonderful article and saw the range.copy to destination syntax for the first time! Thousands of copy and paste were unnecessary if only I had known! Working from home is not all it is cracked up to be. Thank you so much Jon. You probably know why my macro was failing! I discovered that whenever I selected a cell or sheet or anything , the address was automatically entered into my cut/paste buffer? I was cutting from one sheet to another , so I was always go to have a select between Cut and Paste to destroy Synchronisation on my intended cut and paste? Once that problem is solved it will be interesting to see if the range.copy still works? Synchronisation is guaranteed there if the the data is buffered at all.
very clear , very helpful .. thanks so much Jon .. btw, i just have a question .. my cells are using links from another tab. I just want to overwrite those links with Paste/Value ... within the same workbook (not from a different workbook) .. is there a smart way to do it (read the From cells, copy to the To cells (in place), since they're are actually the same cells) ? do i still have to write out the whole command you showed above .. provide the "FromCell.Copy ToCell " (wsCopy.Range("A2:A9").Copy wsDest.Range("A2:A9") ??
Just wondering if you can paste data into a cell which is merged ? I want to also keep the formatting of the merged cell which has justification/ font / size / cell colour. The data I want to paste should inherit the merged cell formatting
Hi John, great posting this video on using the Copy & Paste commands on VBA. How would I write a code to copy cell A1 (“A”) and paste the value on Cells B1:B5 (“A” - 5 times).? And how would I repeat this if I want cell A2 (“B”) and paste the value on Cells B6:B10? Can a use a CopyLoop? Pleases advice in using both ‘Relative References’ and not ‘Relative ‘References’ n Macros.
I'm creating a report at work and have this working, but I'm needing to insert the column in between others. The way this is working is pasting over a cell that I already have used. What would I need to do different?
Hi, what if I want to copy and paste it to an active cell(that i will basically and manually choose the paste destination on the time that i am doing a task). Actually, i will need a button for POS system. Thanl you so much.
Great video.. Thanks.. I have a situation where I have to loop through a list of row( records ) and move cells in each row to a specific location in different worksheet that is essentially a template for a pre-printed form and then print the template for each row. There will be a variable number of rows in the source file up to possibly 70 or so. Your example to above is just what I need for copying the cells. Do you have any examples of looping through rows of data and then printing as I just explained?
can i automate copying a range from excel and paste it to a search box in a specific website and it woild copy and paste the result back to my excel file in specific range to highlight the differences? my free book search is taking 3 to four minutes 4 times a day ..does it worth of learing and writing VBA code? how much will it cost me if i find somebody to write it for me?
How do you execute END-DOWN and copy at the cell on lands then tell it to move right and paste . I want to use this macro at numerous locations without referencing the new destination since it will change every time?
Thanks friend. Please I need to copy specific cell "A2" and past it repeatedly as per used row count. suppose in another sheet I have used 10 row "B1:F10", so I need to repeat it in "A1:A10". But the last row unknown. could you help me please?
Hi John, Please check my 3rd line is giving some error. Sub Macro2()
Range("a1:a3").Copy Range("C1:C3") Worksheets("sheet1").Range("a1:a3").Copy Worksheets("sheet2").Range("a1:a3") Workbooks("Book1.xslx").Worksheets("sheet1").Range("a1:a3").Copy Workbooks("Book2.xslx").Worksheets("sheet1").Range("a1:a3") End Sub
Hi Ashish, Book1 needs to be open and saved. One common error I see is that Book1 is not saved. When you have a new workbook and it has not been saved yet, it does not have a file extension. Therefore, "Book1.xlsx" is not a valid reference to the new workbook. I hope that helps.
I have excell sheet where in column D i need to restrict the characters limitations to 40 only when copy and paste some data from any excel sheet in any cell in this column also when typing in same column more than 40 characters a pop up warning msg displayed and prevent the paste action as well as preventing the typing action
Thank you sir for great tutorial, I'm very new to VB. I need to know is how to transfer data from one cell to another cell in the same row. I'm using one cell to submit data then use a command button that will take that data and transfer it to another cell, but each time I do it I want it to select the next vacant cell in the same row.
Hi Thanks for the video. Could you please help to demonstrate, how to create bar-code printing label(lay out) using excel. in my case, my table consist of Product name, Product ID, Price and Quantity that I need to print.
Thank you for the video. In the last line of code where you mentioned about moving data from one workbook to other I get a run- time error 9 Subscript out of range Have you come across something like that anytime Thank you.
Hi Jon, Thanks for sharing your knowledge. I've used your Range Copy Workbooks VBA and I was hoping you might be able to assist me add some other functionality to it. The data source (book1.xlxs) has a list of products with headings in the one sheet such as product name, price, sale date etc. I want to update the (book2.xlsx) workbook with each product in its own sheet. Also when I copy I want it to only add the new items from the previous day sales to the next available row. I hope that makes sense. Thanks in advance for your assistance mate. Cheers Neil
Hi, thanks for the video. I am having problems with a VBA code and wondered if you could help? I'm trying to copy 3 rows (with specific cells selected) of data from one sheet to another (a master data sheet). I've duplicated the copy and past code for all 3 row ranges, but it only seems to copy the 3rd row to the master data sheet. Is there code to find the next empty row and paste/add the next data entry please?
I love it...but I need to copy the text from excel to website and when I past it the next text should have automatically copy and i can past it in website Without opening the excel sheet again then copy it and paste it plssssssss help
Thank you for the video it was helpful to get started. Now I need to amp it up. I have a userfrom with a listbox. It is for tracking purchase orders (PO). My PO information is in a table (T_INV )on a sheet called (purchase orders). When I search for a PO# all the information associated with the PO is displayed in the listbox. When I click the information in the listbox it populates all the textboxes on the userform. Now what I want to have happen is to click a command button called "cmdreconcile" ( this means I am finished with the PO) and have the PO row cut and pasted to another table (Reconciled) on a sheet in the same workbook called "Reconciled" (clever I know). My range of PO data starts at A15 and goes through T15. I also would like to delete the empty row on sheet "purchase Orders". The POs are not reconciled in numeric order because some take longer to clear. But when it pastes into table "reconciled" on sheet "reconciled" I would like them to be pasted in numeric order (this might be done by sorting the table by PO# later? I can pay you for your time.
Hi Jon and/or Community. Is there a way to apply this same concept to pasting the information into another application? Currently I'm trying to write a code to copy data from Excel into a Terminal based system operating within the framework of MicroFocus RUMBA. I know this is possible because I've seen people mention it in forums (w/o the code nor instructions mentioned) and I've worked with a successful integration of something similar using another terminal host at a previous employer. Any help would be greatly appreciated. Thanks!
How can I run this sequence on Marco? 1. Cut text in B105. Paste in G104 2. Move two rows down 3. Cut text in B107. Paste in G106 4. Move two rows down 5. Cut text in B109. Paste in G108 6. Run till end of worksheet
Basic beginner here and can't figure out how to get this to work. What I want excel to do is: If cell c7>=1 then copy range a55:l56 and paste c7 times starting at row a57. Can anyone help?
Hello Jon, I would like to ask you if you can help me on the following. I enter some numbers in a column, then i delete these numbers and enter some other ones in the same column, delete them again etc,etc. The problem is that, every time i enter these data, I want them to be tranfered into a column in a new sheet, and, every time i delete them on the first sheet, the data on the second sheet must NOT be deleted, and when i add the new numbers on the first sheet, these new numbers to be added on the second sheet, below the previous ones, etc ,etc
hello sir, i have a question:i have columns A B C D E F 1 a b1 3 c4 65 A 102 6XX if i select all region from a1 to f3 and paste all in let's say H55, all empty cells among source ranges are removed and it pastes the values starting from H55 then I55 and L55 squeezing everything. instead i want to paste but preserving the columns spaceing between each other source ranges, so H55, L55, O55. is it possible? i hope having been clear
I cannot copy between two separate workbooks. I have a comment "Syntax error". Something must have been wrong in this code. Please take a look again on the last one.
Hi Jon, I have cell A1 in Sheet 1 and cell A2 in Sheet 2. They both contain the same name (ABC). I would like to link A1 to A2. Cell A2 has a hyperlink to a website. I would like for A1 to be able to be updated whenever A2 is updated. I would like A1 to have the completely same functionality as A2, essentially, A opening the same website linked to A2. Summary: I don't want A1 to just reference to A2, I would like A1 and A2 to both open to the same website, BUT A1 to be adaptable to whenever A2 (hyperlink) is changed. Could you please help me? Thanks.
Thank you Jon. Has does one remove the grade shading that accompanies the pasted range to the destination worksheet? Thank you again; your video are a huge help!
Thanks Tanner! In that case you will want to paste values only. This is explained in the 2nd video in the series. ruclips.net/video/AzxTCCuo8OM/видео.html
Thank you...the gray shading is a function of the paste function. I overcame it by adding a Range.Select command move he cursor after pasting. Thanks again.
Hi Jon! Thank you for the tutorial, it has shed some light on what you can do with Excel VBA. May I ask if you have a series or course that teaches an automatic copy and paste from an excel cell to a website form? Would love to watch or view the course.
Sir i have a data having large number of rows and columns. In this data there is a column having a drop down list of serviceability status i.e serviceable or non serviceable. I want that the moment i select serviceable the entire rows having serviceable text copy into another worksheet i.e sheet 2 and when i select non serviceable the entire rows having non serviceable text copy into another worksheet i.e sheet 3. Sir the third and most important requirements is that there is a column having items quantity no in this data. If items quantity is 10 and there is only 06 items are serviceable then is this possible the same row copy in both sheet 2 and sheet 3 i.e in sheet 2 same row will be copy having 6 serviceable items and in sheet 3 same row will be copy having 4 non serviceable items. Sir if this is possible using VBA or without VBA Please share with me. I will be thankful to you.
It's because you haven't saved Book1 yet. Files don't have extensions until they are saved. So try adjusting your code to just Workbooks("Book1") as oppose to Workbooks("Book1.xlsx").
Dim lastRow1 As Long, erow1 As Long lastRow1 = Worksheets(“WO_SendM”).Cells(Rows.Count, 1).End(xlUp).Row For i = 3 To lastRow1 If Worksheets(“WO_SendM”).Cells(i, 9).Value = Me.Label164.Caption Then Worksheets(“WO_SendM”).Cells(i, 4).Copy Worksheets(“WO_SendM”).Cells(i, 5).Copy erow1 = Worksheets(“WO_Ledger”).Cells(Rows.Count, 18).End(xlUp).Row Worksheets(“WO_SendM”).Paste Destination:=Worksheets(“WO_Ledger”).Cells(erow1 + 1, 18) End If Next i Is it possible to get two cell value 4 and 5 in another cell?
An idiot's headsup (me). If you use a macro to copy from one cell to another, and you then make changes to the source list, make sure to re-write code to reflect those changes. There is no undo (!) once you've made a mistake with this macro.
This Works for me now -
Sub CopyToBook()
Set y = Workbooks.Open("Path to the file")
Workbooks("Book2.xlsm").Worksheets("Sheet1").Range("C22").Copy
y.Sheets("Sheet1").Range("B5").PasteSpecial
End Sub
OMG I have been a programmer for centuries and written so many Excel macros.
Today I hit problems with Cut and Paste and could not understand why!
Looking for a solution , I found this wonderful article and saw the range.copy to destination syntax for the first time!
Thousands of copy and paste were unnecessary if only I had known!
Working from home is not all it is cracked up to be. Thank you so much Jon.
You probably know why my macro was failing! I discovered that whenever I selected a cell or sheet or anything , the address was automatically entered into my cut/paste buffer? I was cutting from one sheet to another , so I was always go to have a select between Cut and Paste to destroy Synchronisation on my intended cut and paste?
Once that problem is solved it will be interesting to see if the range.copy still works? Synchronisation is guaranteed there if the the data is buffered at all.
You're a Rockstar! I went from zero to being able to write the macros I needed in a minute thanks to this. Cheers :-)
Awesome, didn't realise this stuff was so simple. You Rock Dude !!!
This is so useful! Thank you very much!
Really really helpful, this way I reduced 30 % of lines in my codes. Thanks!
very clear , very helpful .. thanks so much Jon .. btw, i just have a question .. my cells are using links from another tab. I just want to overwrite those links with Paste/Value ... within the same workbook (not from a different workbook) .. is there a smart way to do it (read the From cells, copy to the To cells (in place), since they're are actually the same cells) ? do i still have to write out the whole command you showed above .. provide the "FromCell.Copy ToCell " (wsCopy.Range("A2:A9").Copy wsDest.Range("A2:A9") ??
Just wondering if you can paste data into a cell which is merged ?
I want to also keep the formatting of the merged cell which has justification/ font / size / cell colour.
The data I want to paste should inherit the merged cell formatting
Thank you for the useful video✌
Awesome. How about if I want to copy a non blank is a range? Say Cell A1 - A10 then I want all non blank copied to cell. Thanks.
Hi John, great posting this video on using the Copy & Paste commands on VBA. How would I write a code to copy cell A1 (“A”) and paste the value on Cells B1:B5 (“A” - 5 times).? And how would I repeat this if I want cell A2 (“B”) and paste the value on Cells B6:B10? Can a use a CopyLoop? Pleases advice in using both ‘Relative References’ and not ‘Relative ‘References’ n Macros.
I'm creating a report at work and have this working, but I'm needing to insert the column in between others. The way this is working is pasting over a cell that I already have used. What would I need to do different?
Hi, what if I want to copy and paste it to an active cell(that i will basically and manually choose the paste destination on the time that i am doing a task). Actually, i will need a button for POS system. Thanl you so much.
Hi Jon, how to copy data from one cell and paste into another merged cell in vba ?
Great video.. Thanks.. I have a situation where I have to loop through a list of row( records ) and move cells in each row to a specific location in different worksheet that is essentially a template for a pre-printed form and then print the template for each row. There will be a variable number of rows in the source file up to possibly 70 or so. Your example to above is just what I need for copying the cells. Do you have any examples of looping through rows of data and then printing as I just explained?
Thank you so much for this incredibly helpful tutorial!!
Glad it was helpful! 😀
can i automate copying a range from excel and paste it to a search box in a specific website and it woild copy and paste the result back to my excel file in specific range to highlight the differences? my free book search is taking 3 to four minutes 4 times a day ..does it worth of learing and writing VBA code? how much will it cost me if i find somebody to write it for me?
How do you execute END-DOWN and copy at the cell on lands then tell it to move right and paste . I want to use this macro at numerous locations without referencing the new destination since it will change every time?
Thanks friend. Please I need to copy specific cell "A2" and past it repeatedly as per used row count. suppose in another sheet I have used 10 row "B1:F10", so I need to repeat it in "A1:A10". But the last row unknown. could you help me please?
thank you for the lesson. i have a question, i have 80 sheets, how can apply this code to all my sheets at once. thank you again.
Hi John, Please check my 3rd line is giving some error.
Sub Macro2()
Range("a1:a3").Copy Range("C1:C3")
Worksheets("sheet1").Range("a1:a3").Copy Worksheets("sheet2").Range("a1:a3")
Workbooks("Book1.xslx").Worksheets("sheet1").Range("a1:a3").Copy Workbooks("Book2.xslx").Worksheets("sheet1").Range("a1:a3")
End Sub
Hi Ashish,
Book1 needs to be open and saved. One common error I see is that Book1 is not saved. When you have a new workbook and it has not been saved yet, it does not have a file extension. Therefore, "Book1.xlsx" is not a valid reference to the new workbook. I hope that helps.
I have excell sheet where in column D i need to restrict the characters limitations to 40 only when copy and paste some data from any excel sheet in any cell in this column also when typing in same column more than 40 characters a pop up warning msg displayed and prevent the paste action as well as preventing the typing action
It's help me a lot.thanks for video
Great videos - definitely worth watching if you are interested in getting started with VBA
Jacque Dreyer Thanks Jacque!
Thank you sir for great tutorial, I'm very new to VB. I need to know is how to transfer data from one cell to another cell in the same row. I'm using one cell to submit data then use a command button that will take that data and transfer it to another cell, but each time I do it I want it to select the next vacant cell in the same row.
Hi Thanks for the video. Could you please help to demonstrate, how to create bar-code printing label(lay out) using excel. in my case, my table consist of Product name, Product ID, Price and Quantity that I need to print.
Thank you for the video.
In the last line of code where you mentioned about moving data from one workbook to other
I get a run- time error 9
Subscript out of range
Have you come across something like that anytime
Thank you.
Hi Jon,
Thanks for sharing your knowledge. I've used your Range Copy Workbooks VBA and I was hoping you might be able to assist me add some other functionality to it.
The data source (book1.xlxs) has a list of products with headings in the one sheet such as product name, price, sale date etc. I want to update the (book2.xlsx) workbook with each product in its own sheet. Also when I copy I want it to only add the new items from the previous day sales to the next available row.
I hope that makes sense.
Thanks in advance for your assistance mate.
Cheers
Neil
Can you tell me how to Cells from Sheets present in 4 or more Workbooks to one Final Workbook?
It would be really helpful.
Thank You.
Hi, thanks for the video. I am having problems with a VBA code and wondered if you could help?
I'm trying to copy 3 rows (with specific cells selected) of data from one sheet to another (a master data sheet). I've duplicated the copy and past code for all 3 row ranges, but it only seems to copy the 3rd row to the master data sheet. Is there code to find the next empty row and paste/add the next data entry please?
I love it...but I need to copy the text from excel to website and when I past it the next text should have automatically copy and i can past it in website Without opening the excel sheet again then copy it and paste it plssssssss help
Thank you for the video it was helpful to get started. Now I need to amp it up. I have a userfrom with a listbox. It is for tracking purchase orders (PO). My PO information is in a table (T_INV )on a sheet called (purchase orders). When I search for a PO# all the information associated with the PO is displayed in the listbox. When I click the information in the listbox it populates all the textboxes on the userform. Now what I want to have happen is to click a command button called "cmdreconcile" ( this means I am finished with the PO) and have the PO row cut and pasted to another table (Reconciled) on a sheet in the same workbook called "Reconciled" (clever I know). My range of PO data starts at A15 and goes through T15. I also would like to delete the empty row on sheet "purchase Orders". The POs are not reconciled in numeric order because some take longer to clear. But when it pastes into table "reconciled" on sheet "reconciled" I would like them to be pasted in numeric order (this might be done by sorting the table by PO# later? I can pay you for your time.
Hi Jon and/or Community. Is there a way to apply this same concept to pasting the information into another application? Currently I'm trying to write a code to copy data from Excel into a Terminal based system operating within the framework of MicroFocus RUMBA. I know this is possible because I've seen people mention it in forums (w/o the code nor instructions mentioned) and I've worked with a successful integration of something similar using another terminal host at a previous employer. Any help would be greatly appreciated. Thanks!
How can I run this sequence on Marco?
1. Cut text in B105. Paste in G104
2. Move two rows down
3. Cut text in B107. Paste in G106
4. Move two rows down
5. Cut text in B109. Paste in G108
6. Run till end of worksheet
Many Thanks
Basic beginner here and can't figure out how to get this to work. What I want excel to do is:
If cell c7>=1 then copy range a55:l56 and paste c7 times starting at row a57. Can anyone help?
Always excellent Jon.
Hello Jon, I would like to ask you if you can help me on the following.
I enter some numbers in a column, then i delete these numbers and enter some other ones in the same column, delete them again etc,etc. The problem is that, every time i enter these data, I want them to be tranfered into a column in a new sheet, and, every time i delete them on the first sheet, the data on the second sheet must NOT be deleted, and when i add the new numbers on the first sheet, these new numbers to be added on the second sheet, below the previous ones, etc ,etc
hello sir, i have a question:i have columns A B C D E F
1 a b1
3 c4 65
A 102 6XX
if i select all region from a1 to f3 and paste all in let's say H55, all empty cells among source ranges are removed and it pastes the values starting from H55 then I55 and L55 squeezing everything. instead i want to paste but preserving the columns spaceing between each other source ranges, so H55, L55, O55. is it possible? i hope having been clear
Is it possible to copy data from one sheet to another which has merged cells? I keep getting run-time error! Even though both sheets are identical!
I cannot copy between two separate workbooks. I have a comment "Syntax error". Something must have been wrong in this code. Please take a look again on the last one.
Hi Jon,
I have cell A1 in Sheet 1 and cell A2 in Sheet 2. They both contain the same name (ABC).
I would like to link A1 to A2. Cell A2 has a hyperlink to a website.
I would like for A1 to be able to be updated whenever A2 is updated.
I would like A1 to have the completely same functionality as A2, essentially, A opening the same website linked to A2.
Summary:
I don't want A1 to just reference to A2, I would like A1 and A2 to both open to the same website, BUT A1 to be adaptable to whenever A2 (hyperlink) is changed.
Could you please help me?
Thanks.
Sir I need VBA code insert Blank Rows at every value change using command button.
Thank you Jon. Has does one remove the grade shading that accompanies the pasted range to the destination worksheet? Thank you again; your video are a huge help!
Thanks Tanner! In that case you will want to paste values only. This is explained in the 2nd video in the series. ruclips.net/video/AzxTCCuo8OM/видео.html
Thank you...the gray shading is a function of the paste function. I overcame it by adding a Range.Select command move he cursor after pasting. Thanks again.
I like your videos. What program do you use to show the buttons pressed or is it just post production?
I use Camtasia for all my videos. It allows you to add the button annotations in post production.
Thank you very much sir!
Hi Jon! Thank you for the tutorial, it has shed some light on what you can do with Excel VBA. May I ask if you have a series or course that teaches an automatic copy and paste from an excel cell to a website form? Would love to watch or view the course.
How to copy and paste a cell (not entire row) from one sheet to another if a condition is met?
I want to copy only edited selection or cell not the other blank cells
Sir i have a data having large number of rows and columns. In this data there is a column having a drop down list of serviceability status i.e serviceable or non serviceable. I want that the moment i select serviceable the entire rows having serviceable text copy into another worksheet i.e sheet 2 and when i select non serviceable the entire rows having non serviceable text copy into another worksheet i.e sheet 3. Sir the third and most important requirements is that there is a column having items quantity no in this data. If items quantity is 10 and there is only 06 items are serviceable then is this possible the same row copy in both sheet 2 and sheet 3 i.e in sheet 2 same row will be copy having 6 serviceable items and in sheet 3 same row will be copy having 4 non serviceable items. Sir if this is possible using VBA or without VBA Please share with me. I will be thankful to you.
Giving "runtime error 9 subscript out of range" when i use your code for "'Range.Copy to other workbooks"
It's because you haven't saved Book1 yet. Files don't have extensions until they are saved. So try adjusting your code to just Workbooks("Book1") as oppose to Workbooks("Book1.xlsx").
hi I want vba code for copying and pasting data from rows to a column
great! thanks
How does PasteSpecial know where to paste? There is no reference to the cell?
I want do not copy on my sheet can we
great
Dim lastRow1 As Long, erow1 As Long
lastRow1 = Worksheets(“WO_SendM”).Cells(Rows.Count, 1).End(xlUp).Row
For i = 3 To lastRow1
If Worksheets(“WO_SendM”).Cells(i, 9).Value = Me.Label164.Caption Then
Worksheets(“WO_SendM”).Cells(i, 4).Copy
Worksheets(“WO_SendM”).Cells(i, 5).Copy
erow1 = Worksheets(“WO_Ledger”).Cells(Rows.Count, 18).End(xlUp).Row
Worksheets(“WO_SendM”).Paste Destination:=Worksheets(“WO_Ledger”).Cells(erow1 + 1, 18)
End If
Next i
Is it possible to get two cell value 4 and 5 in another cell?
An idiot's headsup (me). If you use a macro to copy from one cell to another, and you then make changes to the source list, make sure to re-write code to reflect those changes. There is no undo (!) once you've made a mistake with this macro.
Y