You can download the source code from here: bit.ly/2uIlmeD If you plan to copy data using VBA then this article has everything that you need: excelmacromastery.com/excel-vba-copy/ Enjoy!
I need one excel vba code User have Download rawdata from ERP and saved in c drive folder Macro should open and copy paste it data in my active xlsm file. Here I need to find without open and edit rawdata, macro should give message box If user have to open raw data file or edit the file.
thanks Paul but the range is dynamic is so better than specific range when i add every time the data automatically transfer data without i have to change the range into code
Hi Sir i dont know the range values in some of my sheet.. how to select the values dynamically in range based on the number of rows and columns available in that particular sheet. Thanks Asrar
This does not work for me, I have been going around in circles doing everything in this tutorial and it just does not work. I get an error on the rgDestiantion.Pastespecial xlpastevalues. I looked at your examples and copied it exactly the same and still it will not work and gets hung up here. Any ideas what could be the issue? [SOLVED] For some reason when i changed to xlpasteall it worked.
Hi sir, Really very very useful your video and code. Same order I will do one automation in excel workbook.. could you please help me. How to approach you. Kindly advice
Your video is the best in explaining how to use copy/paste in vba. How can I modify the vba code to copy different ranges in different worksheets and are in different columns and rows? Thank you and keep up the good work!
I have wrote the code, and I am trying to copy from "COPY_FROM.xlsx" to other file. But getting error for Workbooks variable not defined..: Help. Code: Option Explicit Sub CopyData() ' file from which to copy Dim filename As String filename = ThisWorkbook.Path & Application.PathSeparator & "COPY_FROM.xlsx" Dim wk As Workbook Set wk = Woorkbooks.Open(filename, ReadOnly:=True)
Dim rgSource As Range, rgDestination As Range
Set rgSource = wk.Worksheets("Sheet1").Range("C1:E11") Set rgDestination = ThisWorkbook.Worksheets("Sheet1").Range("G1")
Please can you help me with this. I going round in circles and hitting the same 1004 error at the same point no matter what method I use. I love the source to destination technique as there is no need to make sheets active. Basically I am copying selected columns from one datatable on one sheet and pasting the selected columns to form a slimmer datatable on another sheet. For HDR = 1 To LSTHDR
HDV = Cells(HDR, 1).Value CPYC = Application.WorksheetFunction.Match(HDV, INP.Range("A1:Z1"), 0) PSTC = PSTC + 1 Set RngSource = ThisWorkbook.Worksheets("Input").Range(Cells(1, CPYC), Cells(LDROW, CPYC)) Set RngDestination = ThisWorkbook.Worksheets("Output").Range(Cells(1, PSTC), Cells(LDROW, PSTC)) RngDestination.Value = RngSource.Value Next HDR The error line is Set RngSource =ThisWorkbook... All of the variables exist in the local window at this point ie CPYC (Copy column) and LDROW (last data row), so I know its' not the variables, but I can't see anyting else. I have the whole code below so you can see that my variables are set correctly. I have been stuck on this for over a week now. Sub FlexiCopy() Dim OUT As Worksheet, INP As Worksheet, CON As Worksheet Set OUT = ThisWorkbook.Worksheets("Output") Set INP = ThisWorkbook.Worksheets("Input") Set CON = ThisWorkbook.Worksheets("Control Sheet") Dim HDRCNT As Integer, LSTHDR As Integer Dim HDR As Integer Dim HDV As String Dim CPYC As Integer, PSTC As Integer Dim LDROW As Long Dim RngSource As Range, RngDestination As Range LDROW = INP.Cells(Rows.Count, 1).End(xlUp).Row PSTC = 0 'XXXX CHECK FOR GAPS IN HEADER LIST XXXX HDRCNT = Application.WorksheetFunction.CountA(Range("A1:A10")) LSTHDR = Range("a11").End(xlUp).Row If LSTHDR HDRCNT Then MsgBox ("Check for gaps in Header Column") Exit Sub End If 'XXXX CHECK FOR EXISTING & UNIQUE COLUMN HEADERS XXXX For HDR = 1 To LSTHDR HDV = Cells(HDR, 1).Value If Application.WorksheetFunction.CountIf(INP.Range("A1:Z1"), HDV) = 1 Then GoTo HDVOK End If If Application.WorksheetFunction.CountIf(INP.Range("A1:Z1"), HDV) > 1 Then MsgBox ("Header Column: " & HDV & " is duplicated") Exit Sub End If MsgBox ("Header Column: " & HDV & " does not exist") Exit Sub HDVOK: Next HDR 'XXXX COPY & PASTE REQUIRED COLUMNS XXXXX
For HDR = 1 To LSTHDR
HDV = Cells(HDR, 1).Value CPYC = Application.WorksheetFunction.Match(HDV, INP.Range("A1:Z1"), 0) PSTC = PSTC + 1 Set RngSource = ThisWorkbook.Worksheets("Input").Range(Cells(1, CPYC), Cells(LDROW, CPYC)) Set RngDestination = ThisWorkbook.Worksheets("Output").Range(Cells(1, PSTC), Cells(LDROW, PSTC)) RngDestination.Value = RngSource.Value Next HDR Application.CutCopyMode = False CON.Activate Range("A11").Select End Sub
What if I want to copy specific cell where answer was selected from a questionnaire with Q1, Q2, and so forth. The answers are from 1 to 5. So question Q1 will be on A2 row; Q2 on A3row, The answer 1 will be on B2column, answer 2 on B3, answer 3 on B4, answer 4 on B5, answer 5 on B6. So I want the selected answers 1, 2, 3, 4, 5 to be copied and pasted in invisible Answers sheet. The answers have check box "Agree" and # 1 near the box, check box "Strongly Agree" and # 2 near and so forth. I wonder should I label/name the check box as 1 for answer 1 "Agree"; check box as 2 for answer "Strongly disagree" 2 and so forth. So should I say something in the code something like: If checkbox 1 is selected, then place the value 1 in the Answer sheet. So when the responder finishes all the 50 questions, when the survey person opens the invisible Answer sheet, the answers are there with all the calculation sum or count, also I want to put on the answer sheet what is the answer besides the #. Example: "Agree" on A2, Q1 on B2; check box 1 on column B3.
This is awesome, only problem is I'm not getting the CODE when i click on your link provided. i only get a zip file with data in the workbook, but no VBA code.
Can you explain how to copy and paste with multiple column ranges. eg. A:D, F, AW, AI, AE. I am not able use your code and I also want dynamic without need to put range to last rows. Thank you.
Hi, resolved my below query the workbook had a space after the last letter, this was causing he runtime error. all working great and so much simpler than other options. Can you advise if you have a tutorial on how to select next empty row that can be integrated into the VBA. I am a real VBA novice and your videos are really helping me develop my skills
Is it possible to write a code that catches a growing source table? For example, each week I extract a new table from our software. And it gains new lines each week. Can I write a code that encompasses the new lines by itself? Thanks for the video!
i love the simplicity of the code vs other online tutorials, however the code wont run past the Set rgsource = ThisWorkbook.Worksheets("Hen").Range("A12:BA35") stage, due to a run time error! what am i doing wrong?
Hey, I'm writing a VBA code and I'm trying to use variable inside Range("A11":"A" & variable). This syntax is giving error Can you suggest me what's wrong and what should I do
I love your videos, thanks a lot for sharing. I have a question, can you do the same thing but NOT specifing the name of the sheet but using its Code Name, for exmple: ThisWorkbook.Sheet1.Range("A1") ..... so even if someone changes the name of the sheet it still works? I've tried but it gives me an error if I don't specify the name of the sheet
I started using assignment instead of copy-paste ~6 months ago after learning about it in your “make your code run faster” lesson. It’s amazing ... & blazingly fast! I’ve been updating my workbooks to replace copy-paste as I come across them, & I still react with wonder at how faster - & simpler - the code is. Thanks!
@@Excelmacromastery I keep running into various issues when using assignment, because I don't think I knew about the resizing needed. If you use the codename of the worksheet can you forgo the workbook name?
I always use assign range values to another to copy data as you shown here at last, coz it runs faster than using copy and pastespecial. The new thing I learnt from this video that resize the range method, I used to find out last row of source range and using that variable to set that destination range, but now I can use resize mathod, and don't need to use that variable. Thank you so much sir 🙏 Sir, is there any way to store variables value in memory even my computer is shut down? Actually I've a large code that take approx 10hours to run, and I want to run that code seperate times, but want to run that code next time where I stopped it last time. Is there any solution for that? Thanks in advance 🙏
Thanks Sanjib. Glad you enjoyed the video. When the code stops running then the variables are no longer in memory. You have to store them in a file if you want to access them after the computer is shut down.
@@Excelmacromastery I'm also very glad to get your reply, thank you so much Sir 🙏 I always enjoy your videos, coz there is must be something new to learning. Recently I got the clear knowledge about 'byRef, byVal' by your video, very clear explanation with very simple example and also very simple english with very clear pronunciation make your video enjoyable and knowledgeable. I'm grateful to RUclips to give me suggestions that I found this channel. Love you from India ❤️🧡💛💚💙💜❤️🧡💛💚
I have created a Invoice sheet and what I am trying to do is have the customer information ie. name, address, city,state,ect. which are in different cells (not in the same columns or rolls) Saved to a second worksheet to create a Customer Database all within the same workbook. Could you help with this.
I would name the required fields on the invoice sheet, then in the VBA code, copy them one by one to the other sheet, practically to a table (ListObject). First, checking if it is there already. There is an issue, what to do when not all the data is matching for an existing customer. But I would do the reverse instead. Register the customers first, then on the invoice sheet, pull all the data, possibly using a customer ID. You don't even need VBA in this case.
Dear Paul, I am grateful to you to have learnt so many amazing features of Excel VBA from your videos. Just realized that its possible opening an excel file using GetObject() instead of Workbooks.Open() method. This way the file is not made visible on the screen but it appears in the Project window in the VBE. Written below some concise code to explain my experience. Do you have any suggestion/opinion in opening file using GetObject() in the present context? Set rgSource = GetObject("D:\MyFiles\otherfile.xlsx").Worksheets("Report").Range("A1:B15") ... rgSource.Parent.Parent.Close 'Closes otherfile.xlsx
Dear Kelly Hi! On my request, I am in receipt of a couple of emails from you. I sincerely appreciate your prompt reply. Actually I am unable to understand or work with it as I have not yet come across with XML concept of MS Excel. Please upload related Vid or advise on how to become familiar with XML. With regards, PM
thank you so much for a very very useful tutorials i learnt a lot , it is very timely for my office automation project using excel vba. I did a lot of systems in our company by just doing the research when i need some codes. Now i can do it by my own with your very clear explanations of copying data from different files and sources. now I am one of your followers. keep it up and thanks for sharing a lot of videos.
This is a high quality tutorial, thank you. Surely I will be your subscriber 😃 I ve done tiger spreadsheets stuff, very good for beginners, now time to może up to intermediate and advanced vba guy 🤗💪
Paul - Great stuff. Super informative. I have something I have been trying to figure out and this video addressed 1/2 of it, wondering if you could solve the other 1/2 also. I have sheet 1 "Buy or Sell " where I have a list of items. I want to be able to add or remove a number to a total on Sheet 2 (stock), but after I add or subtract, Clear the contents of the number bought or sold on sheet 1, without clearing the list item, just the numbers. SO on the Buys or Sell sheet Row 1 would have headers Items, Buy, Sell And Row 2 would have Oranges, Blank (or Zero), Blank (or Zero). If I buy 5 oranges and hit a button "submit" i want that 5 added to the total of the oranges on the Stock tab - BUT - then clear the purchase on the Buy-Sell tab - without reducing the number of the stock - and then be able to re-use and readd. so I could get 5, then reset and get 3 more, reset and remove 6, reset and add 10, and so forth. any help you could provide would be greatly appreciated.
Excellent again. However, I tried to use assignment instead of copy/paste to copy data from one Excel listobect to another. In this case the "resize" step gave me some trouble, and without resizing the assignment line produced an error saying the method is not supported.
Good morning, excellent, thanks for the video, and how would I change the code if I had to copy discontinuous ranges, for example data from one row, column A, column C and column E to the next sheet in row 1, columns A, B and C
Hi, i have a program where i need to make duplicates of 2 sheets at a time (because the links of 1 sheet point to the 2nd sheet, and if i do the duplication in 2 steps, the links will point to the original sheet). However, after about 20 duplications, i get out of memory errors from excel and the program crashes. Any tips on how to do this more efficiently? I need links to stay active, ie - i cant hardcode the values
Always like the clarity and completeness of your videos. However...(!) ... I have found the copy/paste fails with very large datasets. Is the assignment method more efficient? Also, it might be instructive to show how to hide any screen flicker, Also, as well, is it good practice to clear the clipboard after a copy/paste?
Great video and explanation. If I want to continue to add data from the source worksheet to a different destination workbook, how do I copy paste data from the source worksheet to the next blank row in the designation workbook? Thanks.
Hi Paul, I must say excellent contents and great way of explanation skills.... keep sharing knowledge it will definitely help people's like me. Another thoughts is it possible to creat video on how to download attachments from perticular subject mail or perticular attachment name. Also SQL videos are most welcome. Sanket
Hi Paul, first I would like to thank you for some of your videos that helped me to create my own VBA coded workbook. I'm now have one task that I would like to ask, could you please help me on how to format a database (table) into invoice format look like, with some of the items are consist of various items (can be one or more than one). Thank you in advance.
I love your channel! I keep returning to it again and again. Big thank you! Is it possible to use assignment to assign values from a closed workbook to the open workbook, but without opening the closed workbook (i.e. to avoid workbooks.open altogether)? Is there a way for excel to reach into the data stored in the workbook without opening that workbook? Thanks in advance!
Paul has already posted an *excellent* video about using the advanced filter in Excel with VBA. I highly recommend you to watch it on his RUclips channel.
Very useful! The assignment definitely works faster than copy and paste. There is one problem, though. I tried to transfer a range of data. In which, the value of some cells is "03"/"00". The value of these cells in the destination sheet became 3 and 0. Might I know how to transfer the original value?
Thanks for this so much. Just wondering if I needed to keep a track of an entire row in a worksheet that was built by input from another worksheet - like for reporting purposes - how could I do that?
Hey Paul - love the channel and demos! I am currently working on a project where I need to automate creating individual tabs for records Owners based their name. I used to have a functional solution but an update disabled it. It seem like some of your methods would yield faster results and simpler code. Is there a way to couple autoFilter dynamics with loop tab creation to accomplish this?
that's awesome, I really appreciate your efforts.. This approach solved most of my problems, I send my respect to you from Syria.. Is there a way to copy data from another workbook without opening and closing the file??
Great explanation!! Even do i don't use a lot of VBA codding I love to see your videos.. I like to see a lot of possibilities.. What about if you have a set of files? Is there a smart way to read and copy from all of them?👍
I have used the above, changing source and destination file names, and the data copies over to the new spread sheet. However, the column widths differ and I cannot see all of my data. How can I make sure that the column widths remain the same?
I'm learning alot from this channel. I'm currently making an invoice/POS/Database for our welding shop. I Have a question. What code can I use if, let say, Company in ColA is always updating?
Do you mean you want to copy when the column updates? If that is the case then you can use a worksheet event to trigger the copy(see my video on worksheet events here: ruclips.net/video/TYyPrqqFgVg/видео.html)
I wanted to keep this code simple as possible to illustrate the relationship between workbooks, worksheets and ranges. In the original code I used a worksheet variable but I removed it to increase the simplicity.
![Cody Johnson - I'm Gonna Love You (with Carrie Underwood) [Official Music Video]](http://i.ytimg.com/vi/yy9PuYMU29g/mqdefault.jpg)
You can download the source code from here: bit.ly/2uIlmeD
If you plan to copy data using VBA then this article has everything that you need:
excelmacromastery.com/excel-vba-copy/
Enjoy!
I need one excel vba code
User have Download rawdata from ERP and saved in c drive folder
Macro should open and copy paste it data in my active xlsm file. Here I need to find without open and edit rawdata, macro should give message box If user have to open raw data file or edit the file.
thanks Paul but the range is dynamic is so better than specific range when i add every time the data automatically transfer data without i have to change the range into code
Web server is returning an unknown error
You said no macro recorder required, yet the whole thing IS a macro. 🤦♂️
Hi Sir
i dont know the range values in some of my sheet.. how to select the values dynamically in range based on the number of rows and columns available in that particular sheet.
Thanks
Asrar
This does not work for me, I have been going around in circles doing everything in this tutorial and it just does not work. I get an error on the rgDestiantion.Pastespecial xlpastevalues. I looked at your examples and copied it exactly the same and still it will not work and gets hung up here. Any ideas what could be the issue? [SOLVED] For some reason when i changed to xlpasteall it worked.
BIG question what if my row count is not fixed. What if the rg.Source row was different each time i tried to do a copy and not fixed.
That's impressive.
In all the videos I always learn something new.
Even when I thought it was not going to happen.
Thanks very much Paul.
Your tutorials are always helpful.
It is easy to notice that you are very professional.. 🌟 🌟 🌟 🌟
Thanks for your compliment K D
Hi sir,
Really very very useful your video and code. Same order I will do one automation in excel workbook.. could you please help me. How to approach you.
Kindly advice
Your video is the best in explaining how to use copy/paste in vba. How can I modify the vba code to copy different ranges in different worksheets and are in different columns and rows? Thank you and keep up the good work!
I have wrote the code, and I am trying to copy from "COPY_FROM.xlsx" to other file. But getting error for Workbooks variable not defined..:
Help.
Code:
Option Explicit
Sub CopyData()
' file from which to copy
Dim filename As String
filename = ThisWorkbook.Path & Application.PathSeparator & "COPY_FROM.xlsx"
Dim wk As Workbook
Set wk = Woorkbooks.Open(filename, ReadOnly:=True)
Dim rgSource As Range, rgDestination As Range
Set rgSource = wk.Worksheets("Sheet1").Range("C1:E11")
Set rgDestination = ThisWorkbook.Worksheets("Sheet1").Range("G1")
rgSource.Copy
rgDestination.PasteSpecial xlPasteValues
wk.Close saveChanges:=False
End Sub
Please can you help me with this. I going round in circles and hitting the same 1004 error at the same point no matter what method I use. I love the source to destination technique as there is no need to make sheets active. Basically I am copying selected columns from one datatable on one sheet and pasting the selected columns to form a slimmer datatable on another sheet.
For HDR = 1 To LSTHDR
HDV = Cells(HDR, 1).Value
CPYC = Application.WorksheetFunction.Match(HDV, INP.Range("A1:Z1"), 0)
PSTC = PSTC + 1
Set RngSource = ThisWorkbook.Worksheets("Input").Range(Cells(1, CPYC), Cells(LDROW, CPYC))
Set RngDestination = ThisWorkbook.Worksheets("Output").Range(Cells(1, PSTC), Cells(LDROW, PSTC))
RngDestination.Value = RngSource.Value
Next HDR
The error line is Set RngSource =ThisWorkbook...
All of the variables exist in the local window at this point ie CPYC (Copy column) and LDROW (last data row), so I know its' not the variables, but I can't see anyting else.
I have the whole code below so you can see that my variables are set correctly. I have been stuck on this for over a week now.
Sub FlexiCopy()
Dim OUT As Worksheet, INP As Worksheet, CON As Worksheet
Set OUT = ThisWorkbook.Worksheets("Output")
Set INP = ThisWorkbook.Worksheets("Input")
Set CON = ThisWorkbook.Worksheets("Control Sheet")
Dim HDRCNT As Integer, LSTHDR As Integer
Dim HDR As Integer
Dim HDV As String
Dim CPYC As Integer, PSTC As Integer
Dim LDROW As Long
Dim RngSource As Range, RngDestination As Range
LDROW = INP.Cells(Rows.Count, 1).End(xlUp).Row
PSTC = 0
'XXXX CHECK FOR GAPS IN HEADER LIST XXXX
HDRCNT = Application.WorksheetFunction.CountA(Range("A1:A10"))
LSTHDR = Range("a11").End(xlUp).Row
If LSTHDR HDRCNT Then
MsgBox ("Check for gaps in Header Column")
Exit Sub
End If
'XXXX CHECK FOR EXISTING & UNIQUE COLUMN HEADERS XXXX
For HDR = 1 To LSTHDR
HDV = Cells(HDR, 1).Value
If Application.WorksheetFunction.CountIf(INP.Range("A1:Z1"), HDV) = 1 Then
GoTo HDVOK
End If
If Application.WorksheetFunction.CountIf(INP.Range("A1:Z1"), HDV) > 1 Then
MsgBox ("Header Column: " & HDV & " is duplicated")
Exit Sub
End If
MsgBox ("Header Column: " & HDV & " does not exist")
Exit Sub
HDVOK:
Next HDR
'XXXX COPY & PASTE REQUIRED COLUMNS XXXXX
For HDR = 1 To LSTHDR
HDV = Cells(HDR, 1).Value
CPYC = Application.WorksheetFunction.Match(HDV, INP.Range("A1:Z1"), 0)
PSTC = PSTC + 1
Set RngSource = ThisWorkbook.Worksheets("Input").Range(Cells(1, CPYC), Cells(LDROW, CPYC))
Set RngDestination = ThisWorkbook.Worksheets("Output").Range(Cells(1, PSTC), Cells(LDROW, PSTC))
RngDestination.Value = RngSource.Value
Next HDR
Application.CutCopyMode = False
CON.Activate
Range("A11").Select
End Sub
What if I want to copy specific cell where answer was selected from a questionnaire with Q1, Q2, and so forth. The answers are from 1 to 5. So question Q1 will be on A2 row; Q2 on A3row, The answer 1 will be on B2column, answer 2 on B3, answer 3 on B4, answer 4 on B5, answer 5 on B6. So I want the selected answers 1, 2, 3, 4, 5 to be copied and pasted in invisible Answers sheet. The answers have check box "Agree" and # 1 near the box, check box "Strongly Agree" and # 2 near and so forth. I wonder should I label/name the check box as 1 for answer 1 "Agree"; check box as 2 for answer "Strongly disagree" 2 and so forth. So should I say something in the code something like: If checkbox 1 is selected, then place the value 1 in the Answer sheet. So when the responder finishes all the 50 questions, when the survey person opens the invisible Answer sheet, the answers are there with all the calculation sum or count, also I want to put on the answer sheet what is the answer besides the #.
Example: "Agree" on A2, Q1 on B2; check box 1 on column B3.
Just finding this video trying to figure out VBA. Can this be used to "Cut" data into another worksheet instead of "Copy" the data? Thanks.
This is awesome, only problem is I'm not getting the CODE when i click on your link provided. i only get a zip file with data in the workbook, but no VBA code.
Can you explain how to copy and paste with multiple column ranges. eg. A:D, F, AW, AI, AE. I am not able use your code and I also want dynamic without need to put range to last rows. Thank you.
FIFO Method or Function is Required to Calculate Invoice Age and Cost Purpose
Hi, resolved my below query the workbook had a space after the last letter, this was causing he runtime error. all working great and so much simpler than other options. Can you advise if you have a tutorial on how to select next empty row that can be integrated into the VBA. I am a real VBA novice and your videos are really helping me develop my skills
Is it possible to write a code that catches a growing source table? For example, each week I extract a new table from our software. And it gains new lines each week. Can I write a code that encompasses the new lines by itself?
Thanks for the video!
Hey...great video. Am trying this out & finding that I am copying the formulas from one sheet to another instead of just the values?
Such a useful video and so easy to follow along. Thank you!!
Thanks. Glad you like it.
How would I do this but copying the row only if the date = Today() and only after a certain time, paste as values in same row
I am running this code but getting error as object doesn't support this property or method
what if i do have multiple workbooks as source but only one destination
Could you save the screenshot, of a table, to save as a PNG or JPG, and save it in a folder?
How to copy paste with source formatting if we do it randomly based on conditional loop?
How we can copy specific data like some specific month from one workbook to another.
Thank you very much....great video!
Hi! How can i set the macro to copy data every specific time? Let´s say, every 5 min it copies the data
Hi, can a row be copied to another worksheet based on criteria from source worksheet?
i love the simplicity of the code vs other online tutorials, however the code wont run past the Set rgsource = ThisWorkbook.Worksheets("Hen").Range("A12:BA35") stage, due to a run time error! what am i doing wrong?
Hey, I'm writing a VBA code and I'm trying to use variable inside Range("A11":"A" & variable). This syntax is giving error
Can you suggest me what's wrong and what should I do
"A11:A" & variable
I love your videos, thanks a lot for sharing. I have a question, can you do the same thing but NOT specifing the name of the sheet but using its Code Name, for exmple: ThisWorkbook.Sheet1.Range("A1") ..... so even if someone changes the name of the sheet it still works? I've tried but it gives me an error if I don't specify the name of the sheet
Yes, you can use the code name like this codename.Range("a1")
But the sheet must be in the same workbook as the code.
I started using assignment instead of copy-paste ~6 months ago after learning about it in your “make your code run faster” lesson. It’s amazing ... & blazingly fast! I’ve been updating my workbooks to replace copy-paste as I come across them, & I still react with wonder at how faster - & simpler - the code is. Thanks!
Thanks for the feedback Jim. Glad you like it.
@@Excelmacromastery I keep running into various issues when using assignment, because I don't think I knew about the resizing needed.
If you use the codename of the worksheet can you forgo the workbook name?
how do you do similar for noncontiguous ranges as the source?
Is it possible to not hardcode the filename and pass it in runtime ?
thanks for the guide.love from god's own country.
Can I copy with this method whole sheet from one xlxs to another?
I always use assign range values to another to copy data as you shown here at last, coz it runs faster than using copy and pastespecial. The new thing I learnt from this video that resize the range method, I used to find out last row of source range and using that variable to set that destination range, but now I can use resize mathod, and don't need to use that variable. Thank you so much sir 🙏
Sir, is there any way to store variables value in memory even my computer is shut down? Actually I've a large code that take approx 10hours to run, and I want to run that code seperate times, but want to run that code next time where I stopped it last time. Is there any solution for that? Thanks in advance 🙏
Thanks Sanjib. Glad you enjoyed the video.
When the code stops running then the variables are no longer in memory. You have to store them in a file if you want to access them after the computer is shut down.
@@Excelmacromastery I'm also very glad to get your reply, thank you so much Sir 🙏
I always enjoy your videos, coz there is must be something new to learning. Recently I got the clear knowledge about 'byRef, byVal' by your video, very clear explanation with very simple example and also very simple english with very clear pronunciation make your video enjoyable and knowledgeable. I'm grateful to RUclips to give me suggestions that I found this channel. Love you from India
❤️🧡💛💚💙💜❤️🧡💛💚
It's great knowledge, thanks!
I have created a Invoice sheet and what I am trying to do is have the customer information ie. name, address, city,state,ect. which are in different cells (not in the same columns or rolls) Saved to a second worksheet to create a Customer Database all within the same workbook. Could you help with this.
I would name the required fields on the invoice sheet, then in the VBA code, copy them one by one to the other sheet, practically to a table (ListObject). First, checking if it is there already.
There is an issue, what to do when not all the data is matching for an existing customer.
But I would do the reverse instead. Register the customers first, then on the invoice sheet, pull all the data, possibly using a customer ID. You don't even need VBA in this case.
hai. may i now what type of font you are using in this micro?
Dear Sir
How are you?
can you help me sir? I want to become your student to learn VB. Sir i watch your video regularly.
Regards
TANVEER MIRAN
Dear Paul, I am grateful to you to have learnt so many amazing features of Excel VBA from your videos. Just realized that its possible opening an excel file using GetObject() instead of Workbooks.Open() method. This way the file is not made visible on the screen but it appears in the Project window in the VBE. Written below some concise code to explain my experience. Do you have any suggestion/opinion in opening file using GetObject() in the present context?
Set rgSource = GetObject("D:\MyFiles\otherfile.xlsx").Worksheets("Report").Range("A1:B15")
...
rgSource.Parent.Parent.Close 'Closes otherfile.xlsx
Dear Kelly
Hi!
On my request, I am in receipt of a couple of emails from you. I sincerely appreciate your prompt reply.
Actually I am unable to understand or work with it as I have not yet come across with XML concept of MS Excel.
Please upload related Vid or advise on how to become familiar with XML.
With regards,
PM
thank you so much for a very very useful tutorials i learnt a lot , it is very timely for my office automation project using excel vba. I did a lot of systems in our company by just doing the research when i need some codes. Now i can do it by my own with your very clear explanations of copying data from different files and sources. now I am one of your followers. keep it up and thanks for sharing a lot of videos.
This is a high quality tutorial, thank you. Surely I will be your subscriber 😃 I ve done tiger spreadsheets stuff, very good for beginners, now time to może up to intermediate and advanced vba guy 🤗💪
Again Paul this is a very good example, of a common problem everyone experience some or other time during the learning period, brilliant 👏👏👏
Thanks Frik
Thank you 🙏 it easy to understand
Great video thnx alot
i'm wondering is it possible to replace the Excel sheet name with the VBA sheet name ?
You mean the codename? Absolutely possible, even more recommended!
Paul - Great stuff. Super informative. I have something I have been trying to figure out and this video addressed 1/2 of it, wondering if you could solve the other 1/2 also. I have sheet 1 "Buy or Sell " where I have a list of items. I want to be able to add or remove a number to a total on Sheet 2 (stock), but after I add or subtract, Clear the contents of the number bought or sold on sheet 1, without clearing the list item, just the numbers. SO on the Buys or Sell sheet Row 1 would have headers Items, Buy, Sell And Row 2 would have Oranges, Blank (or Zero), Blank (or Zero). If I buy 5 oranges and hit a button "submit" i want that 5 added to the total of the oranges on the Stock tab - BUT - then clear the purchase on the Buy-Sell tab - without reducing the number of the stock - and then be able to re-use and readd. so I could get 5, then reset and get 3 more, reset and remove 6, reset and add 10, and so forth. any help you could provide would be greatly appreciated.
Very short and simple
Excellent again. However, I tried to use assignment instead of copy/paste to copy data from one Excel listobect to another. In this case the "resize" step gave me some trouble, and without resizing the assignment line produced an error saying the method is not supported.
This came at a great time, just as I was trying to figure out the best way. Thanks so much!
how to you "run the code" exactly?
Good morning, excellent, thanks for the video, and how would I change the code if I had to copy discontinuous ranges, for example data from one row, column A, column C and column E to the next sheet in row 1, columns A, B and C
Hi, i have a program where i need to make duplicates of 2 sheets at a time (because the links of 1 sheet point to the 2nd sheet, and if i do the duplication in 2 steps, the links will point to the original sheet). However, after about 20 duplications, i get out of memory errors from excel and the program crashes. Any tips on how to do this more efficiently? I need links to stay active, ie - i cant hardcode the values
Always like the clarity and completeness of your videos. However...(!) ... I have found the copy/paste fails with very large datasets. Is the assignment method more efficient? Also, it might be instructive to show how to hide any screen flicker, Also, as well, is it good practice to clear the clipboard after a copy/paste?
Great video and explanation. If I want to continue to add data from the source worksheet to a different destination workbook, how do I copy paste data from the source worksheet to the next blank row in the designation workbook? Thanks.
Hi Paul,
I must say excellent contents and great way of explanation skills.... keep sharing knowledge it will definitely help people's like me.
Another thoughts is it possible to creat video on how to download attachments from perticular subject mail or perticular attachment name.
Also SQL videos are most welcome.
Sanket
Hi Paul, first I would like to thank you for some of your videos that helped me to create my own VBA coded workbook. I'm now have one task that I would like to ask, could you please help me on how to format a database (table) into invoice format look like, with some of the items are consist of various items (can be one or more than one). Thank you in advance.
Seems very simple code compare to your other videos :( But keep posting, U're the best ! :)
Yes, but it's good repetition for beginners. To be honest I watch Pauls's video for fun. I just like to watch him programming :)
Thanks Benjamin. I deliberately kept the code simple to illustrate accessing different worksheets.
That's exactly what I was looking for! Thank you so much!
I love your channel! I keep returning to it again and again. Big thank you!
Is it possible to use assignment to assign values from a closed workbook to the open workbook, but without opening the closed workbook (i.e. to avoid workbooks.open altogether)?
Is there a way for excel to reach into the data stored in the workbook without opening that workbook?
Thanks in advance!
It's Amazingly all copying needs in one place who have the bid knowledge of coding. You emphasize it a great manner. thank you!
I am trying to move rows from one sheet to another, but based on a criteria(i.e. starts with letter H). How can you add that criteria?
🇭🇳💯👍
Clear and concise explanation, very useful and can be applied immediately without any questions. Looking forward to other videos. Thanks!
CTRL+C, CTRL+V.
Next!
❤
In VBA: advanced filter.
Paul has already posted an *excellent* video about using the advanced filter in Excel with VBA. I highly recommend you to watch it on his RUclips channel.
Advanced Filter is great. It's still important to understand assignment and copy\paste as they are useful in some cases and may be in legacy code.
Great explanation - there's such a large community on here and you're all such a wealth of knowledge!
Thanks for all
Very useful! The assignment definitely works faster than copy and paste. There is one problem, though. I tried to transfer a range of data. In which, the value of some cells is "03"/"00". The value of these cells in the destination sheet became 3 and 0. Might I know how to transfer the original value?
If you have any video on how to use offset criteria, it would be very useful
Thanks for this so much. Just wondering if I needed to keep a track of an entire row in a worksheet that was built by input from another worksheet - like for reporting purposes - how could I do that?
You're AWESOME!! Found this VERY HELPFUL!! Thank you!!🙏🏾💯
You're welcome Michelle.
I didn't think that the by assignment method would convert formulas to values but.....it does. Thanks.
You're welcome Jonathan.
Hey Paul - love the channel and demos! I am currently working on a project where I need to automate creating individual tabs for records Owners based their name. I used to have a functional solution but an update disabled it. It seem like some of your methods would yield faster results and simpler code.
Is there a way to couple autoFilter dynamics with loop tab creation to accomplish this?
Perfect VBA. Keep us posted
Thanks Amar.
that's awesome, I really appreciate your efforts..
This approach solved most of my problems, I send my respect to you from Syria..
Is there a way to copy data from another workbook without opening and closing the file??
Glad it helped! There's a few ways to to do it. Check out my video on ADO to see how I used this to do it.
Great explanation!! Even do i don't use a lot of VBA codding I love to see your videos.. I like to see a lot of possibilities..
What about if you have a set of files? Is there a smart way to read and copy from all of them?👍
Sir your tutorials are very useful and also helpful.
Another great video Paul. One question though how do you copy data including columnwidths and formatting eg conditional formatting?
You have to use the PasteSpecial function of Range.
fantastic allway get great tips from you.
Your video are so well done. Subscribed.
Hey Paul, you have the technics which i think i can never find anywhere else..you are really awesome.
Thank you..u r really great👍
I appreciate that!
No insanity pure knowledge... Thank you.
I have used the above, changing source and destination file names, and the data copies over to the new spread sheet. However, the column widths differ and I cannot see all of my data. How can I make sure that the column widths remain the same?
You have to either manually set them or write code to set them. You can also use PasteSpecial to copy column widths.
very easy and useful sir. thank you.
As always, extremely well explained and set out. Thanks very much.
More efficiency / design pattern videos please.
Very nicely explained
Keep sharing your expertise and keep doing good work
Kudos
Thank you, I will
Awesome lesson Paul! Great run through of a variety of methods and examples. Easy to follow.. great for learning! Thanks for sharing :)) Thumbs up!!
Glad you liked it Wayne!
Is the a way to copy from a range in another file without opening the file or using ADO?
Hi Jarrett. If you are using VBA you have to open the file unless you're using ADO.
I'm learning alot from this channel. I'm currently making an invoice/POS/Database for our welding shop.
I Have a question. What code can I use if, let say, Company in ColA is always updating?
Do you mean you want to copy when the column updates? If that is the case then you can use a worksheet event to trigger the copy(see my video on worksheet events here: ruclips.net/video/TYyPrqqFgVg/видео.html)
@@Excelmacromastery The link doesn't work though.
Thank you very much for sharing! I am looking forward to more simple but practical examples for beginners like myself. :)
Glad you like it Tina.
Why not define dim for the worksheets or workbooks. That's what I do when working with worksheets and workbooks.
I wanted to keep this code simple as possible to illustrate the relationship between workbooks, worksheets and ranges. In the original code I used a worksheet variable but I removed it to increase the simplicity.
Thanks Paul, you made copying data very easy to understand! Appreciate it!
Great to hear!
Paul is a genius
希望能有中譯版
Love these videos!! I always learn something new!!
Thanks Conrad.