Need a macro to pick the details from Tab mentioned one after another
Hi, I am trying to work on a macro that will pick the details from each tab as requested in Inputs Tab. Pick the details & consolidate it in 1 output file (only the required fields). But no luck. Appreciate if someone can help me code this. Thanks a lot in advance.
Attaching the sample document for your reference.
Try:
Please Login or Register to view this content.
Last edited by Mumps1; 07-27-2020 at 12:55 PM.
You can say "THANK YOU" for help received by clicking the Star symbol at the bottom left of the helper's post.
Practice makes perfect. I'm very far from perfect so I'm still practising.
An alternative, using ADO/SQL can be found in the attached file.
Note:
1) Due to "Securi" I couldn't post the code.
2) Inputs are hard coded.
Last edited by Haluk; 07-27-2020 at 01:56 PM.
Hi Mumps, Thank you so much for your time in helping me on this.
I copy pasted the code in the excel workbook I shared as sample. am getting an error while I run this code...
at "With Sheets(rng.Value)". Am I doing something different.
A couple of addtions though. In output column, may be in the new column, Is there a chance to pick the tab name, from where the data is pulled from, as there are multiple Tabs.
Also based on the Qtr Selection drop down in Inputs, We need to pull only those 3 months of data into Input Tab.
Like if I select Q1,The Plan data should be from Jan to Mar & Actual Data is Jan to Mar.
Just to summarize:
1. I need only the specific columns as mentioned in the input. Eg, I Dont need Sales pull.
2. Also need specific Quarter pull for both Plan & Actual.Attaching the latest template.
3. Need a column in Out put to show the Tab name the record is pulled from.
Appreciate for your quick help on this to me.
Last edited by sai.krishnaa2006; 07-27-2020 at 06:57 PM.
Hi Haluk, Thanks for your time on this.
It runs perfectly, A little modified request though. Sorry for the last minute modification.
1. I need only the specific columns as mentioned in the input. Eg, I Dont need Sales pull.
2. Also need specific Quarter pull for both Plan & Actual.Attaching the latest template.
3. Need a column in Out put to show the Tab name the record is pulled from.
Hope this can be doable.
Also I am very much interested in learning ADO / SQL language. Please guide me on that. Thank you so much.
You're welcome...
As per the additional details given in your message #5; the columns "S.No, Parciculars, Size, Status and data of the 2nd quarter" are being imported into the sheet "Output" in the attached workbook, VBA-ADO-SQL.
For clarification:
Your comment in Inputs says: "Size is not required." However, in your last post you said: "I Don't need Sales pull." Please clarify.
Oh Yes, Thanks for correcting my statement.
I dont need Size.
Through VBA is that possible to achieve the below by any chance?
1. I need only the specific columns as mentioned in the input. Eg, I Dont need Size pull.
2. Also need specific Quarter pull for both Plan & Actual.Attaching the latest template.
3. Need a column in Out put to show the Tab name the record is pulled from.
Thank you so much in advance.
Hey Haluk, This helps me. However looking for a dynamic pull using Input as the source for
1. Information only specific Worksheets (Only mentioned Tab to be retrieved, as I will have another 10 tab which I would not need to pull),
2. Qtr. to Pull (If I select Q2, it has to be Apr to Jun, If Selection is Q3 the July to Sep like wise.)
3. In a column by any chance can we bring up the Tab Name to aligh to each record. so that I will know which record is pulled from which worksheet.
Thank you so much for your effort on this.
If the sheet names are not listed in the columns of the tables, ADO/SQL can not retrieve the sheet names with this type of SQL statement.
But, if we use a For-Next loop to make a query on each sheet, then sheet names can also be retrieved.
Last edited by Haluk; 07-28-2020 at 10:36 AM.
Okay. Anything should work, as long as I could showcase the worksheet names. Please can you send me that version with the requested changes, so I can carry them forward to the live version with all your help. Thank you
Try the attached file. I have made some minor changes in the source sheets. You no longer need the button. When you make a selection in cell G4, the data will be copied automatically.
Mumps1... Amazing, you made my day. Thank youu thank you thank you so much!!!!
I will now try to replicate this one in the live file.
Pls make me your student!!!!!
You are very welcome.
Hi Mumps1,
I have tried to incorporate the code in the live file and was able to troubleshoot most of it with my little knowledge. 1 place where I am failing is when my Input says only 1 worksheet in C4, then a error thrown at "with Sheets (rng.value)", in the file you have shared with me. When I place 2 or more, I am able to pull it without any error.
Now coming to live file, irrespective of any numbers of worksheets name I give, it always stops at that particicular line to troubleshoot "with Sheets (rng.value)".
Rest of the pull works file, just that it throws me that error. please help me to fix that. I would infact be happy to get a alert that "Pull is successful" once it completes.
P.s: If I exactly give range - For each rng in srcws.range("C4 to C6") it doesnot throw any error. it only throws when I have For Each rng In srcWS.Range("C4:C" & LastRow) as a code.
Last edited by sai.krishnaa2006; 07-29-2020 at 12:55 PM.
Try:
Please Login or Register to view this content.
Yes Mumps1. This one work fine now.
Thank you so much for your swift responses.
You are very welcome.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks