Microsoft Recorder Needs to go on a Diet!

[Cyberpawz]

I have been attempting to get code to copy and paste cells, then move them to another sheet, and delete the original file that was copied.

I did this through Record Macro, and I want to cry...

If Microsoft thought about anything they didn't think about how to record macros in their own language. There is so much fat in it that I am having a hard time to figure it out what is actually needed.

Here is the code.

Please Login or Register  to view this content.

Ultimately I will be wanting the pasting to be done via the last row, but for now this will work... if I can figure out what in god's green earth Microsoft actually put together for me...

Can someone shed some light on what is going on? I'm about to re-write this code to actually make sense to a human being...

[JosephP]

actually for the macro recorder that's pretty good. no scrolling and so on. it basically just recorded exactly what you did as you did it. do you have a specific issue?

[Mordred]

I would suggest re-recording your steps but stop and start the recorder after each copy/paste. That way, you can skip a lot of the offsets (I think) as well as any selections you make by mistake. Doing so will make analysing your code much easier to do by you or anyone else.

The macro recorder works really well imo but as JosephP stated, it will record almost every click and action you make.

[Cyberpawz]

actually for the macro recorder that's pretty good. no scrolling and so on. it basically just recorded exactly what you did as you did it. do you have a specific issue?

The reason there is no scrolling is because I have everything viewed at 50% actual size so everything works as it is meant to.

Actually yes. I the cells are grouped, I need to only the first cell copied, then pasted, then continue down the path till it's all in a single row, then that is copied over to another sheet, and a numeric ID is given to said row.

The cells in question are B11, B14, B17, B20, B23, Q11, Q14, Q17, Q20, Q25, V23, V25, V27, B32, B36, B40, B44, B49, L49, V49.

They have to be in that order because this is part of a form, which will be pasted into a row, which will then be moved to a database style sheet, with a identifier at the beginning of said row. This row though will have to be at the end of all data.

So as you can see the program did what it was meant to, but it never would work the way that it needs to.

[JosephP]

So as you can see the program did what it was meant to, but it never would work the way that it needs to.

that sounds like you're blaming the recorder for doing what it was meant to instead of something else that you hoped it might magically do. I don't see the connection to dieting.

[Cyberpawz]

that sounds like you're blaming the recorder for doing what it was meant to instead of something else that you hoped it might magically do. I don't see the connection to dieting.

Please Login or Register  to view this content.

You are telling me you couldn't write a better line of code to do the same thing?

[Mordred]

Perhaps if you upload a mock workbook with mock data (nothing personal or sensitive) then maybe someone can write better code.

[royUK]

The macro recoder does excatly as it is designed to do - record your actions into VBA code. It's up to you to streamline the code. I would only use the recorder to get correct syntax, but it was designed to help novice users.

[JosephP]

Please Login or Register  to view this content.

You are telling me you couldn't write a better line of code to do the same thing?

I'm pretty sure I didn't tell you that but that's not the point. I know what the purpose is, the recorder doesn't. you're basically blaming a cassette recorder for recording you going "erm" instead of just playing the Gettysburg address like you meant.

[Cyberpawz]

I'm pretty sure I didn't tell you that but that's not the point. I know what the purpose is, the recorder doesn't. you're basically blaming a cassette recorder for recording you going "erm" instead of just playing the Gettysburg address like you meant.

Actually, I am expecting software that is meant to record something to do so without so much fluff. I remember recording scripts in AppleScript, and it was actually understandable... this is not.

[Cyberpawz]

I'm pretty sure I didn't tell you that but that's not the point. I know what the purpose is, the recorder doesn't. you're basically blaming a cassette recorder for recording you going "erm" instead of just playing the Gettysburg address like you meant.

Actually, I am expecting software that is meant to record something to do so without so much fluff. I remember recording scripts in AppleScript, and it was actually understandable... this is not.

[JosephP]

It records what you do. You select a cell, it records that. You scroll, it records that. It has no way of knowing what your ultimate purpose is so it simply records every step you take. The fact that you have it set to record relative references makes it a little more convoluted than need be but it ain't that bad really. obviously you disagree.

[Mordred]

How is a recorder, which is meant to record your actions, know what you would consider to be fluff? If it wasn't for the macro recorder and this site (and other sites that I am not aloud to mention), I would still be useless regarding vba. Your code is understandable but you have recorded a lot of steps, which makes it troublesome to look at. Like I suggested in post #3, re-record smaller amounts of actions and you will have an easier time understanding what is going on. Imo, you have recorded so much at one time that even my eyes want to glaze over the procedure.

[shg]

IMO, the macro recorder is a godsend for people new to VBA -- it helps them learn Excel's enormous object model. It's far from perfect in divining users intent, but great for its own intent. I still use it from time to time rather than muddling through Help.

In addition, once you learn how, cleaning up the code it generates is itself an exercise in reinforcing efficient programming.

[Cyberpawz]

How is a recorder, which is meant to record your actions, know what you would consider to be fluff? If it wasn't for the macro recorder and this site (and other sites that I am not aloud to mention), I would still be useless regarding vba. Your code is understandable but you have recorded a lot of steps, which makes it troublesome to look at. Like I suggested in post #3, re-record smaller amounts of actions and you will have an easier time understanding what is going on. Imo, you have recorded so much at one time that even my eyes want to glaze over the procedure.

I did, and it still leaves a lot to like... Here is the code:

Please Login or Register  to view this content.

All I want to do is to copy a cell, and paste into a cell, but instead of copy and pasting the formula, I want to copy and paste the result.

[JosephP]

Please Login or Register  to view this content.

you can also just assign values from range to range if you resize the target to match the source.

[AlvaroSiza]

...Continuing from above, it could look something like this:

Please Login or Register  to view this content.

No copying, no pasting...

[JosephP]

you don't wanna use 0 in a resize call. :-)

[Cyberpawz]

...Continuing from above, it could look something like this:

Please Login or Register  to view this content.

No copying, no pasting...

Ok, but the point is that I want B11 to be the only thing copied over, Or is that what the re size does is take out all the other fields so it only copies over a single cell instead of multiple?

[JosephP]

Please Login or Register  to view this content.

[Cyberpawz]

Please Login or Register  to view this content.

I figured that, but the question comes two fold, 1 can I have it choose multiple cells that don't coincide? and the resize part, this makes it so it pastes only one cell not multiple correct?

[JosephP]

If you are only assigning to one cell, there is no need to resize. if you want to do several discontiguous cells you'll need a loop. where are the cell values supposed to go?

[AlvaroSiza]

...and the resize part, this makes it so it pastes only one cell not multiple correct?

Incorrect. In your original syntax (which you have since rebutted as being incorrect because you only wanted one cell, not a range), you provided a range extending 11 columns within Row 11 ("B11:L11"). You also provided a single cell location for where you wanted the data to go ("AO57"). Joseph was pointing out to you that under a .value = .value approach, it is necessary to Resize (by using the Resize property) your destination range be the same size as your source range. Otherwise, you will error out.

Quickie on resize...

Assume you select Range("A1"). Now assume that you want everything 2 rows to the right, or the ultimate range of Range("A1:C1"). The Resize property allows you resize a base range to whatever dimensions you choose. Thus, the example above would be expressed as:

Please Login or Register  to view this content.

I think you are learning a few things from this thread beyond just the syntax.

1. People are here to help as long as you will help yourself. Provide as much detail as possible. Provide a sample workbook when possible.
2. You need to do some research. It is not our job to help you, we do so because we learned (and are learning) here and want to give back. Your original tone was not conducive to a constructive thread (Excel was broke before you ever posted).

[Cyberpawz]

If you are only assigning to one cell, there is no need to resize. if you want to do several discontiguous cells you'll need a loop. where are the cell values supposed to go?

Ok, here is the document. Or an example of one, this is old data so it's not up to date, nor is it relevant.

In any case If you go to the Internal NCMR, you'll see the JOB# box, it is a pull down menu. Select a job number, from there you will see fields self populate.

Fill in the document, gibberish if you will, the Added By is a pull down menu.

From there, I want all these fields to go into NCMR Data.

If you look at NCMR data, there is a number at the beginning, there is also one at the end on V2. It is an attempt to create a ID system for the list. The concept is that when the script is run, it will copy V2 to the last row and paste it into A#, from there it will copy all the fields in Internal NCMR and paste them in their corresponding columns. Originally I had it where it would paste into a row under the NCMR and then select, delete, and paste into the last row of NCMR Data, but I never got past the point of delete and paste.

If this make any sense to you, I'd appreciate some help.

[Cyberpawz]

Incorrect. In your original syntax (which you have since rebutted as being incorrect because you only wanted one cell, not a range), you provided a range extending 11 columns within Row 11 ("B11:L11"). You also provided a single cell location for where you wanted the data to go ("AO57"). Joseph was pointing out to you that under a .value = .value approach, it is necessary to Resize (by using the Resize property) your destination range be the same size as your source range. Otherwise, you will error out.

Quickie on resize...

Assume you select Range("A1"). Now assume that you want everything 2 rows to the right, or the ultimate range of Range("A1:C1"). The Resize property allows you resize a base range to whatever dimensions you choose. Thus, the example above would be expressed as:

Please Login or Register  to view this content.

I think you are learning a few things from this thread beyond just the syntax.

1. People are here to help as long as you will help yourself. Provide as much detail as possible. Provide a sample workbook when possible.
2. You need to do some research. It is not our job to help you, we do so because we learned (and are learning) here and want to give back. Your original tone was not conducive to a constructive thread (Excel was broke before you ever posted).

I didn't say Excel was broken, I said the Microsoft's attempt at automated script design is broken... big difference.

[AlvaroSiza]

I didn't say Excel was broken, I said the Microsoft's attempt at automated script design is broken... big difference.

I will buy you a beer if you can provide a few bullet points of items you think should be different about it that can withstand rebuttal. As everyone else has stated, there is a reason it is the way it is, you just happen to not like it, which is unfortunate.

That said, here is a script to accomplish what, as far as I can tell, you are after. I was not clear on your A(n) & V2 numbering system. This code copies the fields of the form (which are co-located in a horizontal and contiguous range below the form; Range("E101:X101") to the next available row within the Data worksheet.

Please Login or Register  to view this content.

[Cyberpawz]

I will buy you a beer if you can provide a few bullet points of items you think should be different about it that can withstand rebuttal. As everyone else has stated, there is a reason it is the way it is, you just happen to not like it, which is unfortunate.

That said, here is a script to accomplish what, as far as I can tell, you are after. I was not clear on your A(n) & V2 numbering system. This code copies the fields of the form (which are co-located in a horizontal and contiguous range below the form; Range("E101:X101") to the next available row within the Data worksheet.

Please Login or Register  to view this content.

Thanks, the numbering systems were the beginning of each of the grouped fields, where the data actually resides... so I was thinking copy the first of the grouped fields, and you will receive the data you are looking for. Again, I was looking for just the data, not the script under it that shows the data.

As for the beer...I prefer a dark lager... but here are a few bullet points.


Formatting has to be understandable to even a noob.

Please Login or Register  to view this content.

vs

Please Login or Register  to view this content.

Choose a way to code. There shouldn't be a "Relative Mode" or not... it should be uniform. There is a reason why MS has so many patches for their OS, it's not because of security holes for correctly written code, it is security patches for code written correctly in the views of the coder...

MS is acting the same way when it comes to using Relative References when recording macros. There may be multiple ways to get to the same answer, but when a code is being written by computer, it should be done one way or another... not either or.

I understand the 2nd line of code better than the first. I also can estimate what needs to be deleted because it is fluff.

Another point.

I've been told over and over and over again to write in as Option Explicit code.

MS Excel's Macro Recorder doesn't write in explicit code...


This is the code in question to copy a range of cells from one sheet to another that MS's program does...

Please Login or Register  to view this content.

This is what someone offered from here.

Please Login or Register  to view this content.

I am not asking for much, but if MS expects their code to be used correctly, don't you think they themselves should design a program that writes the code correctly as in making it write in truly Option Explicit instead of a faux version of it?

I can understand it's quick and dirty, but you know what... quick and dirty is not the way to learn how to code especially if you are a noob. I have the Excel VBA book for Dummies, and it explains or attempts to why to write in Option Explicit code. Yet when you read MS's own code it seems to ignore that.

Why?

I have read somewhere by someone that Excel VBA has is an arsenic nightmare wrapped in a sugar pill sold by MS as a language easy to learn. I am tending to lean to that viewpoint and agree with it. This code would be so much easier to learn if their own program coded correctly. Nothing against it but I prefer to learn by myself, and I hate asking for help, but VBA seems to be a language that is not easy to learn.

I learned Basic when I was 8, I learned LOGIC when I was 9, and I was learning Visual Basic in it's truest form back in the 90's... I've learned mostly HTML, XHTML, and I'm learning C as we speak... VBA when MS uses it, seems to like to follow the rules when it's own program writes for you, and you must follow the correct rules when you write for yourself.

[JosephP]

Choose a way to code. There shouldn't be a "Relative Mode" or not... it should be uniform.

why not? when recording a macro, you may want it to do the same things relative to wherever you start, or you may want it to do the same thing regardless of what you have selected.

I also can estimate what needs to be deleted because it is fluff.

already covered. you know what you are trying to do - the recorder doesn't so it just records whatever you do.

I've been told over and over and over again to write in as Option Explicit code.

MS Excel's Macro Recorder doesn't write in explicit code...


This is the code in question to copy a range of cells from one sheet to another that MS's program does...

Please Login or Register  to view this content.

This is what someone offered from here.

Please Login or Register  to view this content.

I am not asking for much, but if MS expects their code to be used correctly, don't you think they themselves should design a program that writes the code correctly as in making it write in truly Option Explicit instead of a faux version of it?

I don't think you know what Option Explicit means. It requires you to declare any variables you use - that's all. The first code doesn't use any.

I have read somewhere by someone that Excel VBA has is an arsenic nightmare wrapped in a sugar pill sold by MS as a language easy to learn.

imo that's just a ridiculous statement. (it's so easy even I learned it.)

I was learning Visual Basic in it's truest form back in the 90's... I've learned mostly HTML, XHTML, and I'm learning C as we speak...VBA seems to be a language that is not easy to learn.

VBA is a subset of VB6 so not that different. If you can use one, you should be able to use the other. it's considerably easier to learn for most people than a C language for instance.

[Cyberpawz]

why not? when recording a macro, you may want it to do the same things relative to wherever you start, or you may want it to do the same thing regardless of what you have selected.

You are assuming.

already covered. you know what you are trying to do - the recorder doesn't so it just records whatever you do.

Exactly, but in doing so it should also have the smarts to write the code in a way that people like me (a novice) understands with little to no difficulty. It should not be so hard from writing formulas in cells to go to VBA Excel but it is.

I don't think you know what Option Explicit means. It requires you to declare any variables you use - that's all. The first code doesn't use any.

Why not? Shouldn't because isn't that the right way to write code?

imo that's just a ridiculous statement. (it's so easy even I learned it.)

You may learn differently than other people, I am a visual learner, this form of language is problematic because for me the code doesn't explain itself as easy as others do.

VBA is a subset of VB6 so not that different. If you can use one, you should be able to use the other. it's considerably easier to learn for most people than a C language for instance.

That's an opinion, I am starting to understand C better than VBA because in C it works one way, there are multiple ways to get there, but the foundation is the same.

In Excel VBA you can ignore declaring variables, in XHTML and C you can't...

[JosephP]

You are assuming.

no I'm stating a fact. that's the only difference between relative and absolute recording.

Exactly, but in doing so it should also have the smarts to write the code in a way that people like me (a novice) understands with little to no difficulty. It should not be so hard from writing formulas in cells to go to VBA Excel but it is.

I disagree completely. the macro recorder is not there to teach it's there to record. the fact that many use it as a learning tool does not mean it was designed as one.

Why not? Shouldn't because isn't that the right way to write code?

there is no point using variables when they are not required-you're just wasting memory and time.

You may learn differently than other people, I am a visual learner, this form of language is problematic because for me the code doesn't explain itself as easy as others do.
...
That's an opinion, I am starting to understand C better than VBA because in C it works one way, there are multiple ways to get there, but the foundation is the same.

you said you already knew VB. it's not much of a jump to vba. for most people vb and vba are easier than c precisely because they work at a higher level. that's why they are RAD languages.

In Excel VBA you can ignore declaring variables, in XHTML and C you can't...

I don't see the relevance-all languages are different. not declaring variables ain't a symptom of needing a diet.

in any event I reckon your mind is made up so I'll not trouble you further.

[Cyberpawz]

Well here is the code after spending a day working on it, and getting a little help, I came up with this.

Please Login or Register  to view this content.

I will still say the recorder needs to go on a diet... from what I needed to what it did were two entirely different things.