[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

VBA Code to Search, Copy, Paste select columns

Posted on 2011-10-19
39
Medium Priority
?
286 Views
Last Modified: 2012-05-12
HI,

I have a drop down list value (Column is in Sheet1) and based on the selected value in the drop down list it will search for the value in column in Sheet1 and it will copy and past the select columns (C, D, F, J) to a new sheet starting at row (B4).  The drop down list value will go through all of the rows in Sheet1 to copy and past all that matchs the value from the drop down list and past all rows in the Sheet2.  

Now another person comes along and selects a new drop down list value and that process will occure for that value, however, the previous rows that were pasted should be removed.

Any way to rememer the Number of rows that were pasted on Sheet2 in last search and clear them in the next search.

I need help on creating a vba code for all of the above.  I have starting point but need expert help or if you have a code which does something similar.




0
Comment
Question by:Ankit_J_P
  • 23
  • 15
39 Comments
 
LVL 24

Expert Comment

by:StephenJR
ID: 36993843
I suggest you post an illustrative workbook.
0
 

Author Comment

by:Ankit_J_P
ID: 36993904
I have attached a workbook with two sheets.  In sheet a "Tracking#" is selected from drop down menu and the Row 8 populates the columns from Sheet1.  However, this will no longer work because The Tracking number can repeat multiple times in the column (Sheet1).  Also the rows that are copied from Sheet1 must be insereted in sheet2 so it expands the area and does not replace other rows on the sheet. Consolidated-Forms-101811.xls
0
 
LVL 31

Expert Comment

by:gowflow
ID: 36994216
sorry in the workbook you posted there is no Sheet1 and Sheet2 there is NSR LOG and NSRLO_
FORM I see no dropdown list did I miss something or maybe you did not post the cirrect workbook ?
gowflow
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:Ankit_J_P
ID: 36994247
Sheet1 = NSL Log
Sheet2 = NSRLOG_FORM

Dropdown list is in "D2" of NSRLOG_FORM
0
 
LVL 31

Expert Comment

by:gowflow
ID: 36994647
ok fine now I see what you want. 2 questions ?

1) Do you accept a VBA solution ? or you need your solution to be via formulas ?
2) What if say the sleected item in D2 corespond to 100 rows ? we shall insert 100 rows starting B4 and then will come the comments etc ... the rest of the form or ... you need to insert couple of rows then comments then on a new page the header + couple or rows + comments etc ... you see what I mean like you want a fix form that will be identical that will contain all rows or simply throw all the rows and then the comments and the rest of the form. Like you want an invoice type of form ?

based on your answers I can then design it accordingly. Obviously the Invoice type of form the fix layout is the most challenging and the one that is a bit more delicate.
gowflow
0
 

Author Comment

by:Ankit_J_P
ID: 36994751
So Far I have the following Code

Sub SearchForString()

    Dim LSearchRow As Integer
    Dim LCopyToRow As Integer
    Dim LSearchValue As String
   
    On Error GoTo Err_Execute
   
    'Start search in row 4 of NSR Log
    LSearchRow = 4
   
    'Start copying data to row 2 in Sheet2 (row counter variable)
    LCopyToRow = 8
    LSearchValue = Sheets("NSRLOG_FORM").Range("D2")
   
    'Sheets("NSR Log").Select
    While Len(Range("A" & CStr(LSearchRow)).Value) <> ""
       
        'If value in column A = NSRLOG_FORM (D2), copy entire row to
            If Range("A" & CStr(LSearchRow)).Value = LSearchValue Then
           
            'Select row in NSR Log to copy
            Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
            Selection.Copy
           
            'Paste row into Sheet2 in next row
            Sheets("NSRLOG_FORM").Select
            Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
            Selection.Insert
           
            'Move counter to next row
            LCopyToRow = LCopyToRow + 1
           
            'Go back to Sheet1 to continue searching
            Sheets("NSR Log").Select
           
        End If
       
        LSearchRow = LSearchRow + 1
       
    Wend
   
    'Position on cell A3
    Application.CutCopyMode = False
    Sheets(NSRLOG_FORM).Range("B8").Select
   
    MsgBox "All matching data has been copied."
   
    Exit Sub
   
Err_Execute:
    MsgBox "An error occurred."
   
End Sub

0
 
LVL 31

Expert Comment

by:gowflow
ID: 36994771
One more issue you hav ein sheet NSRLOG_FORM row 8 where you put you vlookup formula is a row that is much larger than the following 3 rows (just before comments is there a reason for this ? shall the remaining pasted row be like row 8 in height or they be like row 9 a normal row ?

Also you have columns on top in row 7
I J K L M C      F E P N Q B
Are these the columns position you want to paste in these cells ? like
Col I of NSR LOG in Col B of NSRLOG_FORM
Col J of NSR LOG in Col C of NSRLOG_FORM
Col K of NSR LOG in Col D of NSRLOG_FORM  
etc ... ??

gowflow
0
 
LVL 31

Expert Comment

by:gowflow
ID: 36994791
So Far I have the following Code

Sub SearchForString()
?? I don;t understand you have the solution ? if not what you need our help for pls be specific we cannot guess what you have unless you clearly put it down.
gowlow
0
 

Author Comment

by:Ankit_J_P
ID: 36994809
1) Do you accept a VBA solution ? or you need your solution to be via formulas ?

1A)VBA Solution

2) What if say the sleected item in D2 corespond to 100 rows ? we shall insert 100 rows starting B4 and then will come the comments etc ... the rest of the form or ... you need to insert couple of rows then comments then on a new page the header + couple or rows + comments etc ... you see what I mean like you want a fix form that will be identical that will contain all rows or simply throw all the rows and then the comments and the rest of the form. Like you want an invoice type of form ?

2A) The rows should not grow more than ~20 rows as far as I know based on feedback.
I want to keep the remaining form static and insert rows based on the search in the form.  Ofcourse like to remember how many rows were inserted and delete the rows upon exit except for one row to start the next search and print.  Also I don't want any formatting to be carried over from NSR Log to NSRLOG_FORM
0
 

Author Comment

by:Ankit_J_P
ID: 36994882
I need help with:

the code is has some bugs.  

The current code can search but need help with proper coding to copy and paste (insert in a new row) because paste will override current rows.  Also once we print the form, the rows that were pasted (inserted) upto Comment row should be deleted for the next search.  So the form should return to original the template for next search.

Additionally, the current code copies the entire row I only want to copy select column from that row, I am having hard time with that code.

And finally I don't want the paste (insert) commend to copy the formatting.

Thanks a lot
0
 

Author Comment

by:Ankit_J_P
ID: 36994908
gowflow

the columns i want to copy from NSR Log is listed on NSRLOG_FORM.  I.e. Column I, J, K, L, M, C, F, etc.,

Thanks,
Ankit

0
 

Author Comment

by:Ankit_J_P
ID: 36995562
I have made slight changes to code above but getting error message.  help!

Sub SearchForString()

    Dim LSearchRow As Integer
    Dim LCopyToRow As Integer
    Dim LSearchValue As String
   
    On Error GoTo Err_Execute
   
    'Start search in row 4 of NSR Log
    LSearchRow = 4
   
    'Start copying data to row 2 in Sheet2 (row counter variable)
    LCopyToRow = 8
    LSearchValue = Sheets("NSRLOG_FORM").Range("D2")
   
    Sheets("NSR Log").Select
    While Len(Range("A" & CStr(LSearchRow)).Value) <> ""
               
      'If value in column A = NSRLOG_FORM (D2), copy entire row to
        If Range("A" & CStr(LSearchRow)).Value = LSearchValue Then
           
            'Select row in NSR Log to copy
            Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
            Selection.Copy
           
            'Paste row into NSRLOG_FORM in next row
            Sheets("NSRLOG_FORM").Select
            Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
            Selection.Insert
           
            'Move counter to next row
            LCopyToRow = LCopyToRow + 1
           
            'Go back to NSR Log to continue searching
            Sheets("NSR Log").Select
           
        End If
                 
      LSearchRow = LSearchRow + 1
    Wend
   
    'Position on cell A3
    Application.CutCopyMode = False
    Sheets(NSRLOG_FORM).Select
   
    MsgBox "All matching data has been copied."
   
    Exit Sub
   
Err_Execute:
    MsgBox "An error occurred."
   
End Sub


0
 
LVL 31

Expert Comment

by:gowflow
ID: 36996254
Hi Ankit_J_P
Is this what your looking for ? PLs activate macros and select any value from D2 and watch the results. Sorry could not use your Sub as too way off.

Pls advise your comments and where you would like better adaptation.
gowflow
Consolidated-Forms-101811--1-.xls
0
 
LVL 31

Expert Comment

by:gowflow
ID: 36999797
Did you have a chance to try the proposed solution ?
gowflow
0
 

Author Comment

by:Ankit_J_P
ID: 37000188
Okay. It works but if I changed the column names on the NSRLOG_Form.  I had to modifiy the column names.  The form doesn't work and can explain your code so I understand what you ahve done.

Thanks Consolidated-Forms-102011-ap.xls
0
 

Author Comment

by:Ankit_J_P
ID: 37000219
Also How and where did you assign macro to the drop down list
0
 

Author Comment

by:Ankit_J_P
ID: 37000515
Also If you can recommend a good site or book I can learn VB for excel.  Let me know with the new column names this is possible.  I assum it is just might have to be change the code around?
0
 

Author Comment

by:Ankit_J_P
ID: 37000525
Additionally, I will  have to create more of this form for other sheets so please make it with comments so i can duplicate the effort.

Thank you so much for your assistance.

0
 

Author Comment

by:Ankit_J_P
ID: 37000875
How are you pulling the correct columns over to NSRLOG_FORM from NSR Log?
0
 
LVL 31

Expert Comment

by:gowflow
ID: 37001076
hehehe !!!
your into VBA I can explain but first did u try it is it ok ?
gowflow
0
 

Author Comment

by:Ankit_J_P
ID: 37001111
Yes.  I tried and it works!  Any way to change the column heading I, J, etc. on NSRLOG_FORM?
0
 
LVL 31

Expert Comment

by:gowflow
ID: 37001141
Well guess what !!!!! you change the column headings in NSRLOG_FORM in row 7 and what ever you put there it will pick the right column number !!! ex
if instead of
I J K L M C F E P N Q B
you have
A B D G H J L Z R T V W

then you will get the correct results. Try it
gowflow
0
 

Author Comment

by:Ankit_J_P
ID: 37001200
I copied and inserted your code and it is not working.  I changed to names column names to the following:

"Drug" "Concentration" "Diluent" "Fill volume"

I have attached my file.  Thank you for your help!

 
Consolidated-Forms-102011-AP.xls
0
 
LVL 31

Expert Comment

by:gowflow
ID: 37001212
for sure it will not work with name columns !!!! I took it that it was column heading I will need to modify it ! Yoiu should hv mentioned this in the beginning. Is there anything else hidden you haven't mentioned so we don't do hte job several times ?
gowflow
0
 

Author Comment

by:Ankit_J_P
ID: 37001270
No.  The file you have is how it will look.  I will use the same code for other forms to pull search results from other sheets as long as I can change the column headings.

Thanks.
0
 

Author Comment

by:Ankit_J_P
ID: 37001485
Sorry, I wanted to add Borders around the each cell that is copied from NSR Log to NSRLOG_FORM.

0
 
LVL 31

Expert Comment

by:gowflow
ID: 37001716
ok here it is
This version will work with either
1) Real Column labels like A, B, C ... AM, DV .... etc ...
2) or formulas linked to labels in sheet NFR Log

In the sheet NSRLOG_FORM on row 7 I have replaced the typed labels that you have with their linked refrences in NSR Log and also you can see Col K as well both will get you the correct values.

Pls check iut out and let me know.

The reason why the whole macro did not work is becose you forgot the main line in worksheet NSRLOG_FORM  whcih is
-----------
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$D$2" Then LookupValues
End Sub
-----------

Without this the macro will not be trigered.

gowflow
Consolidated-Forms-102011-AP.xls
0
 

Author Comment

by:Ankit_J_P
ID: 37001752
Some minor things, ToRow not declared error and when I choose a blank cell from the drop down menu the "comments:" is removed.  Any way to further filter the drop down menu so it only shows all NSR numbers?
0
 
LVL 31

Accepted Solution

by:
gowflow earned 2000 total points
ID: 37002734
Well here it is for the 'minor' that are not as minor as you think especially for the validation range
For your info I had to create a Named Range Called Tracking that I affected a formula that is dynamic so it will display in the dropdown always all the items that there are in sheet NFR Log.
Check it out.
gowflow
Consolidated-Forms-102011-AP.xls
0
 

Author Comment

by:Ankit_J_P
ID: 37002815
Thank You!  

For Some Reason when there is a blank cell in the drop down cell and it is selected the B11 "Comments:"  cell is empy after mecro has been executed.  

Let me know if you have a solution for blanks in the Tracking # NSR Log.  Once again Thank You Gowflow!
0
 
LVL 31

Expert Comment

by:gowflow
ID: 37003484
Well if we agree that a blank cell will result in NO result then here is the revised solution.
If you need to blank cell to be concidered as a value and report all the blank cells info then I will have to work on fixing it to this extent.

Pls let me know what is your decision.
gowflow
Consolidated-Forms-102011-AP.xls
0
 

Author Comment

by:Ankit_J_P
ID: 37008814
Thank you so much for all of your support and help!

Gowflow, this code worked great!

Thanks!
0
 

Author Closing Comment

by:Ankit_J_P
ID: 37008829
Gowlfow was a great help and provided the best solution possible to my request and help.
0
 
LVL 31

Expert Comment

by:gowflow
ID: 37008842
Your welcome Ankit J P my pleasure to have been able to help you. Tks for the grade and the nice appreciation.
gowflow
0
 

Author Comment

by:Ankit_J_P
ID: 37008852
Any recommendation so I can learn VBA like you have learned over time?  Books, online help?
0
 
LVL 31

Expert Comment

by:gowflow
ID: 37010789
well experience time book and more importanttly trying to fix other people's issues like EE try to answer questions is the best way to learn. Good lunk and pls feel free to post here a link for an other question if you have one now I will be glad to help you with.
gowflow
0
 

Author Comment

by:Ankit_J_P
ID: 37012070
Thanks.  Any book you would recommend?
0
 
LVL 31

Expert Comment

by:gowflow
ID: 37021607
Sorry but never used any book for VBA !!! all learned thru help and the net and mainly EE. Had books to program VB as this is my main strength I dev software for businesses databases etc ... good luck in your learning process if you need anything on my profile there is a button Hire Me if you click I get a message only way I ca ncommunicate here.

gowflow
0
 

Author Comment

by:Ankit_J_P
ID: 37024271
Thank You!
0

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
This article describes how to use a set of graphical playing cards to create a Draw Poker game in Excel or VB6.
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.

867 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question