Select Relevant Text till not exist. First Script.

Please Login or Register  to view this content.

Simple script, I think it should work, but I keep getting an error (Variable not defined), I know what it means, but I didn't think I actually had a variable.

Can someone help me please? Sorry if I seem to be missing the basics, but unthankfully work throws me into situations I am not comfortable being in, but unthankfully work is exactly that and the job market still sucks.

Cyberpawz,

I'm not sure what the macro is trying to do. It is always checking the same cell, G2, to see if it is empty. If G2 is not empty, the macro doesn't do anything. If G2 is empty, the macro goes into an infinite loop. All it seems to do is select a row, then go down to the next cell in column A, over and over again.

What's the end goal for this macro?

Your post does not comply with Rule 1 of our Forum RULES. Your post title should accurately and concisely describe your problem, not your anticipated solution. Use terms appropriate to a Google search. Poor thread titles, like Please Help, Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will be addressed according to the OP's experience in the forum: If you have less than 10 posts, expect (and respond to) a request to change your thread title. If you have 10 or more posts, expect your post to be locked, so you can start a new thread with an appropriate title.
To change a Title on your post, click EDIT then Go Advanced and change your title, if 2 days have passed ask a moderator to do it for you.

Cyberpawz,

Something like this?

Please Login or Register  to view this content.

Thanks, sorry about not giving more info at first I was under the gun and I was able to get more time to actually learn VBA, but not enough to learn what I need in such a short amount of time, but here is what I need...boss just changed what was required, for the setup here a third tab is going to have to be inputted, called archive.


This is what I am attempting to do.
I have one book with three tabs.

JobsList.xlsx is the book. JobsList and BackOrder and Archive are the tabs.

BackOrder is the imported export file from a program called Fishbowl and will be changing weekly. (Never use this program if you have a choice).


On A1 and A2 I want to create a button that does the steps below automatically.

1. In the BackOrder tab, On B6 there is an alphanumeric name, I want that row to be selected.
   
2. Go down one row, if there is text there select that row.
   
3. Continue till text is no longer prevalent.
   
4. Copy selected rows.
   
5. Paste into a new tab, move columns G to F, L to I, and N to J.
   
6. Copy all text.
   
7. In the Jobs List tab, Move all rows that don’t have “SAME” in column “N” to archive tab, then organize all remaining so that they are all at the top of the list, if there are any left in the first place.
   
8. Paste into tab JobsList, after last row with text in it. paste the data from the temporary tab.
   
9. Delete just created tab, and end script.

What I'd also like to see if possible, but I don't know how to word it, is that the script in M &N will be duplicated as the rows continue down, and stop when the rows of data end... these columns will be hidden to the end user on purpose. They don't need to see this part.

That is it, yes I know I make it sound simple, but this is what I need, or my bosses need…
I am learning Excel VBA to make this happen, but I have a feeling I am far away from making this happen.

Thanks for your help, this is greatly appreciated.

Cyberpawz,

Attached is a modified version of your recent workbook. It contains the following macro which should accomplish what you're looking for:

Please Login or Register  to view this content.

I changed formula in column M from a sumproduct (ineffecient and slow due to whole column use) to countifs. I also copied the formats and formulas to row 1 columns M:X of the JobsList sheet and hid those columns. They are used in the macro to provide formatting and formulas to the new data.

I'm not sure what you mean, I can't find where you're getting those numbers.

In the example file you posted, Jobs List v2.xlsx‎:

• In the Jobs List tab, there are 63 records that do not have "Same" in column N, so those were the records moved to the Archive worksheet.
• In the Jobs List tab, there are 9 records that have "Same" in column N so those records were left alone
• In the BackOrder tab, there are 15 records (B6:B20) that you want to import to the Jobs List tab
• After the macro is run, those 15 records are imported to the Jobs List tab, so 9+15 = 24 records in there now.

This line is where it filters for items that do not have Same in column N of the Jobs List tab (before importing from the BackOrder tab):

Please Login or Register  to view this content.

Basically, I don't understand what should happen vs what is happening. From what I can see, the macro performs as expected, unless I am misunderstaning a requirement?

Originally Posted by tigeravatar

I'm not sure what you mean, I can't find where you're getting those numbers.

In the example file you posted, Jobs List v2.xlsx‎:

• In the Jobs List tab, there are 63 records that do not have "Same" in column N, so those were the records moved to the Archive worksheet.
• In the Jobs List tab, there are 9 records that have "Same" in column N so those records were left alone
• In the BackOrder tab, there are 15 records (B6:B20) that you want to import to the Jobs List tab
• After the macro is run, those 15 records are imported to the Jobs List tab, so 9+15 = 24 records in there now.

This line is where it filters for items that do not have Same in column N of the Jobs List tab (before importing from the BackOrder tab):

Please Login or Register  to view this content.

Basically, I don't understand what should happen vs what is happening. From what I can see, the macro performs as expected, unless I am misunderstaning a requirement?

Ok, here is what I mean, out of those 73, only 9 of them have the word "Same" on column 'N'. So when the script runs, all but 9 should be deleted. If you go to the BackOrder tab you'll see there are 15 rows, now if 9 of them are duplicates only 5 should be transferred over. That means the 5 that should be transferred over are new, and the others should be ignored.

Sorry if I wasn't making sense before. Is there a way to make it so if "Same" shows up on Jobs List to ignore said line in BackOrder?, so that only those with "Same" in the row isn't transferred over, but everything else is? Sorry I didn't make as much sense as I thought I did. Sometimes your own mind is your own worst enemy.

Ooh, I see. In that case, it would be easier to delete everything in the Jobs List sheet and then import the items from the BackOrder.

Change this section:

Please Login or Register  to view this content.

To this instead:

Please Login or Register  to view this content.

Ok, I have been tweaking the code, sorry but got bored and boss was looking at me asking what I was doing....

So I've done HTML, and XHTM coding and looking at the code, thinking that I can solve the issue myself, I've solved 90% of it, it does almost exactly what I want, but it is copying data that shouldn't be copied from BackOrder, namely the Shortage list and below.

Also, I've yet to figure out how to copy the script so that the code can be run a second time from the hidden rows. I can't just paste the code all the way down or it will always copy over the shortage summary list... which I don't want to do.

Thanks for looking, and getting me this far.

Please Login or Register  to view this content.

Originally Posted by Cyberpawz

Ok, I have been reading your script, the original and I am getting a little confused. Can you explain to me what is going on?

Please Login or Register  to view this content.

No Idea.

You are correct on everything you mentioned. That section copies the formatting and formulas that are hidden in M1:X1 and applies it to all items in the Jobs List worksheet after the import, so that it looks consistent and the formulas are already populated.

Took a look at the changes and requirements, give this a shot, should do the trick:

Please Login or Register  to view this content.

Cyberpawz,

This line copies the formulas in Jobs List M & N down:

Please Login or Register  to view this content.

As for BackOrder O & P, that data is imported from fishbowl. The macro could add a line towards the beginning to put in the formulas for those columns i suppose. I'd recommend putting those formulas in the Jobs List sheet in cells Y1 and Z1. And then you could use that to copy and paste the formula to column O and P in the backorder sheet, similar to how the above line operates

Changed my mind. Because its on a different sheet, it'd probably be easiest to input the formula directly, just before filtering for "<>Different", like this;

Please Login or Register  to view this content.

Full code with the change:

Please Login or Register  to view this content.

The formula the script is adding in is this formula in the BackOrder worksheet, column P:
=IF(OR(B1="",D1=""),"",IF(COUNTIFS('Jobs List'!B:B,B1,'Jobs List'!D:D,D1),"","Different"))

That formula combines the two formulas so that you don't have to use two columns, you only need to use one. No changes were made to the formulas on the Jobs List sheet.

K.I.S.S. is always best practice, the simpler the better. I chose to put in that script manually because you said the BackOrder sheet gets its data from being imported from a program called FishBowl. If that's the case, that sheet probably changes regularly and there's no guarantee that a formula would stay in column P. As such, I added to the macro to input the formula manually to guarantee that it will always be present.

You would need to put the code in before you perform the autofilter for "<>Different" so that the formula is in there for when you do the autofilter

Originally Posted by tigeravatar

You would need to put the code in before you perform the autofilter for "<>Different" so that the formula is in there for when you do the autofilter

So after

Please Login or Register  to view this content.

and before this?

Please Login or Register  to view this content.

So it would look like this?:

Please Login or Register  to view this content.

Almost. You should do the formula copy before copying wsB to Sheets.Add and you'll also have to define LastRow for wsB (otherwise it will use the last row for wsJ).

So it should look something like this:

Please Login or Register  to view this content.

LastRow will get updated for wsJ later in the code, so no need to worry about that.

Cyberpawz,

I'd have to see the code to tell you why its giving an error. In general, a range object's .Parent is the worksheet that the range is on. If you're using .Parent without a range object, it will give that error. You'll notice in the pseudo-code I provided that the first With statement defines a range. The second with statement uses the same range's .Parent, or its worksheet.

Cyberpawz,

Found the issue. After copy/pasting the formula in the BackOrder worksheet, it wasn't calculating because my script turned calculation off to increase speed. I put in the keyword Calculate after copy/pasting the formula and then the macro worked fin. What was causing the error, was after it deleted all items without "Different" (which was everything because the formula hadn't calculated yet), there was no range object and thus no .Parent to use because everything had been deleted.

This code ran successfully:

Please Login or Register  to view this content.

Where are you storing the formulas for the BackOrders columns P and Q? The above code assumes that they're in P5 and Q5:

Please Login or Register  to view this content.

If that's not the case, update it from "P5:Q5" to the correct location.

Cyberpawz,

I am unable to download the sheet you posted (I'm at work, and dropbox is blocked). If possible, please use the forum attachment feature. If the workbook is too big, create a dummy workbook.

Attached is your previously attached workbook. It includes the most recent code I posted and it runs successfully. 'With' is a keyword, not an object, so you do want the space.

I hate to bring this in from the grave, but after using this for a day or two, I have noticed that the script isn't functioning like it is meant to. For some reason it is importing duplicates, and not ignoring them like before, and not organizing the rows via date... or moving the old ones to the Archive if there are any.

Here is the latest version.

This is the latest sheet, it shows from line 125 down duplicates of what was imported. Three times...

Can you help please?

Here is the most up to date list. For some reason the BackOrder field is being copied over completely except how it was meant to be, which was only copy those over that are not duplicates, and the script is moving everything over to Archive from Jobs List, even though it is obviously not supposed to do so. If anyone can help me it be greatly appreciated.

Cyberpawz,

From what I can tell, the problem is the formulas in 'Jobs List'!M3 and down and also the formula in 'Jobs List'!X1. Both are off by 1 column.

'Jobs List'!M3 (and copied down) is:
=COUNTIFS(BackOrder!A:A,A3,BackOrder!C:C,C3)

But shouldn't is be...
=COUNTIFS(BackOrder!B:B,B3,BackOrder!D:D,D3)


Same for the formula in 'Jobs List'!X1. Move its columns from L and N to M and O

When I made those changes, it worked fine for me using your most recent workbook.

Cyberpawz,

Near the end of the code are these 2 lines:

Please Login or Register  to view this content.

The first line copies M1:T1 of the 'Jobs List' worksheet. The second line paste specials -> formats. It sounds like you need to extend what it copies to include 2 more columns (M1:V1). Just be sure to move formulas over 2 columns from U, V, X, and Y, and change their copy/paste lines also.