Search results

SirJB7, Omg! Thank you so much. It finally works! All this time I've had dWS set to row1. Thanks for pointing that out. :)) This was a very good learning exercise. Also really appreciate your prompt responses.

I've made the change as you suggested but now nothing is copied. Very strange. Please find the files attached. Thanks

Hi SirJB7 Just wanted to confirm if you updated cell B1 (Source filepath) in the Monitor wkbook? I just ran the code again & the Locals window is showing DestWB is picking up the name "Monitorv3.0.xlsm". Please see screen shots below. I noticed "Central" appears if you are not active/haven't...

Hi XLPadawan Thanks for your response. My original dataset has over 30,000 rows and 100 columns & I think formulas will really slow it down?

Hello, Files uploaded. When you run the macro you'll see the number of cols pasted is dynamic & works. However the actual cols pasted are not correct & don't match the ones I want to extract. I purposely tweaked the macro so when it pastes the cols header names as well. This way you can...

Hi SirJB7 Q)May I ask you why in your code: J <=K? If J <= K Then rngSrcD.Columns(J).Copy rngTgt.Cells(2, I) Next I Below is just a section of my code. I've been using your code as guidance to write my own. I'm stuck on an sentence in the For Next Loop (highlighted in red). My...

5) What is the lone below doing in target section: If .Rows.Count > 1 Then Range(.Rows(2), .Rows(.Rows.Count)).ClearContents

3) How can I expand the header range (in the 'Monitor' wbook) to include more columns to extract? I can't see where & what the actual header range is defined as. 4) Did you use For Next Loop & Loop counter to create this? Or is array used? Thanks

Hi SirJB7, Thanks very much for your response. I'm reviewing the code and a have a few ques: 1) Is defining constants optional? Would the macro still work without it (provided I replace the constants with the full words/text)? 2) Why are all the end objects set to "nothing"? Can you please...

Hello everyone, Can anyone please help construct a code that will: 1) Read the specified header names in Workbook1 (Header name can change) 2) Search for them in Workbook2. 3) When matched/found - Copy the entire columns over to Workbook1. I've attached two sample files: Workbook1 = 'Data'...

Hi I found a macro on the net, please find attached. It is quite complicated to understand. However it works fine but would like to make a few tweaks. It currently pastes data in a new workbook from cell A1. It would be great if someone can help make a few tweaks: 1) Have it paste all the...

Hi Guys, Suppose my product list contains one type of bottle ("Plastic Bottles") but it has been entered in a slightly different way (in the main sheet ) e.g: Plastic red bottles 5ml PlasticRX red bottles PlasticRX R Bottles I want the search (formula) to pick up "plastic" in the main sheet...

Hi Faseeh, Thanks very much! However, suppose in sheet 1 (cell a3) the product was listed as "prod 1" instead of "product 1" . Can you add any wildcards to find an approx match so that row A3 in sheet 2 still highlights?

Hello, Sheet 1 – Main table (has several fields inc. Product Name & Sale date) Sheet 2 – criteria table (List of Product Name & corresponding Sale date ) Basically I am trying to search for my values in sheet 2 in the main sheet 1, which contains a lot of data. I would like to know if there...

Hi SirJB7 (Pablo), So the new file works :). However it only works when I go to >Tools>Marcos>Run. The row will only highlight when I 'run' the macro. Thats the case for every row and when I open it in Excel 2003 & 2010. Getting close - so what can I do so the macro runs automatically...

Hi Guys, I know I have asked for a no.of CF to happen & hence the code is complex. However I'm really keen to learn the basics. So can you please tell me what a simple CF code will ook like if I kust wanted to have: Collumn D= Decision When 'Accept' chosen = GREEN colour from A2 to D2 only...

Hi Narayan, I just tried what you said, but when i run the macro (i.e. tools>maro>run....i hope that what you meant?), a 'run time error/438' occurs. Also 'TintAndShade = 0' highlishts in yellow. Then i tried creating a new duplicate of the sheet and copied the code, but still can't get it to...

Hi SirJB7, I tried copying the code but it doesnt work. A 'compile error' message occurs and 'option explicit' in the code is highlighted. I tried deleting the previous code, but still doesnt work. Thanks, Serena

Hello again Narayan, Yes, thats completely fine. I look forward to it. Cheers! Serena

Hi, Thanks for the above, but the first link was beyond my understanding, so I used the code in the second link. I tried using the code below, however it only highlights one cell. How can I extend it to highlight a row as I have stated above. One more problem is that the code doesn't work if...

Hello, I am using Excel 2003 and conditional formatting (CF) only has a limit of 3. So I wanted to know if I can add a VBA code to add more CF to highlight rows based on a criteria/text in just one cell. E.g: Collumn D= Decision Collumn E= Request date Collumn F= Expiry date When 'n/a' is...

Hi Narayan, It's all working brilliantly. Many many thanks. -Serena

Hi Abhi, Many thanks for your explanation. I am going to try it out now. Serena