Make VBA code shorter

**juanrmz24** · 01-19-2013, 01:31 PM

I'm not very experienced in VBA, and I have a code I created that does exactly what I need, however it's kinda slow. I've tried making changes but every time I try, it stops working, so I have to revert every time.

I know it may be lengthy but I appreciate anyone's help!!

Basically what I need is this:

I have some sort of nth occurrence that gives me the surveys each person gets.
We have some surveys that we get, they are labeled with the employee's name, date, and scores. However, sometimes the surveys have two names(divided with a space, slash, space[" / "]
But those surveys are not detected by the nth occurrence, so I figured a way to split them would be with the LEFT and RIGHT function in a separate sheet, then copy them "as values" in a separate sheet and then copy both(left and right)sheets to the main data sheet where the surveys are. And then the Nth Occurrence gets the surveys from there.

I currently have the following sheets
Employee(where the nth_occurence resides)
Data(where they paste surveys from source)
Data2(Where the nth_occurence gets data from)(Just need data pasted as values in this sheet)
LEFT(Where the LEFT function resides. It's basically =LEFT([whatever...]) using cell references from the Data sheet. Makes the file very heavy as there are over 9000 cells.
RIGHT(Same as LEFT but with RIGHT)
LEFT2(Where date from LEFT is pasted as values so it may be pasted into Data, and eventually into Data2
RGHT2(Same as above with with RIGHT)

This is what I have:

Please Login or Register  to view this content.

**JBeaucaire** · 01-19-2013, 08:22 PM

A coupe of things, you're woring from recorded macros which have recorded your human actions. So "Selecting" gets recorded, and since you "selected" it records you copying the "selection", and sheets get activated so you can manually paste. Note that VBA has no need to select and activate things for things like copying and pasting. You can copy and paste directly by merging those commands at the selections to get a single instruction that simply "does it" without ever changing the onscreen view, VBA doesn't care what's onscreen for things like this.

So, your first macro:

Please Login or Register  to view this content.

Notice, no selections?

Another tip, if you first test for and store the position of the "last row of data", then your copy commands can be formatted to use that stored information. I use a LR variable to store the last row of data by looking UP column A. LR stores that number, then I use it in the the copy command. Also notice we don't even need to "unhide" a hidden sheet to copy to it?

Please Login or Register  to view this content.

**JBeaucaire** · 01-21-2013, 11:40 AM

Thanks for the feedback. If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

**juanrmz24** · 01-22-2013, 06:07 PM

Ok, I've been trying to make the code work, but when I put it in, all it would do was just flash the screen a couple of time and no surveys were pasted. I took the code appart and tested each part. The problem seems to be here:

Please Login or Register  to view this content.

For some reason it doesn't paste any data to the data worksheet. I appreciate your help

**JBeaucaire** · 01-23-2013, 04:30 PM

That code looks fine. I can look at your workbook if you wish. Click GO ADVANCED and use the paperclip icon to post up a copy of your workbook.

**juanrmz24** · 01-25-2013, 01:53 AM

Thanks, that'd be great.

**JBeaucaire** · 01-25-2013, 12:11 PM

If I run the macro, on the LEFT copy from A1:L10000, there's no data, so I'm not sure what you expect to happen. Same for the RIGHT sheet?

So, I added data to LEFT and ran that code and it "appears" nothing happened, but it did. You've added formatting tricks on your data page and hidden rows. The formatting tricks are making Excel think there is data where there is none, and the "paste" occurred into the hidden rows.

The code snippet I provided is basic, it jumps UP from the bottom of the worksheet until it encouonters a used cell, then offsets back down one row... then pastes. You should get all your code and functionality in place before you start playing with extended formatting, then you'll see exactly when you add something that complicates things.

It's working correctly in the attached DATA sheet.