• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 311
  • Last Modified:

VBA Code to Search, Copy, Paste select columns

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
Ankit_J_P
Asked:
Ankit_J_P
  • 23
  • 15
1 Solution
 
StephenJRCommented:
I suggest you post an illustrative workbook.
0
 
Ankit_J_PAuthor Commented:
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
 
gowflowCommented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
Ankit_J_PAuthor Commented:
Sheet1 = NSL Log
Sheet2 = NSRLOG_FORM

Dropdown list is in "D2" of NSRLOG_FORM
0
 
gowflowCommented:
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
 
Ankit_J_PAuthor Commented:
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
 
gowflowCommented:
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
 
gowflowCommented:
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
 
Ankit_J_PAuthor Commented:
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
 
Ankit_J_PAuthor Commented:
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
 
Ankit_J_PAuthor Commented:
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
 
Ankit_J_PAuthor Commented:
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
 
gowflowCommented:
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
 
gowflowCommented:
Did you have a chance to try the proposed solution ?
gowflow
0
 
Ankit_J_PAuthor Commented:
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
 
Ankit_J_PAuthor Commented:
Also How and where did you assign macro to the drop down list
0
 
Ankit_J_PAuthor Commented:
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
 
Ankit_J_PAuthor Commented:
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
 
Ankit_J_PAuthor Commented:
How are you pulling the correct columns over to NSRLOG_FORM from NSR Log?
0
 
gowflowCommented:
hehehe !!!
your into VBA I can explain but first did u try it is it ok ?
gowflow
0
 
Ankit_J_PAuthor Commented:
Yes.  I tried and it works!  Any way to change the column heading I, J, etc. on NSRLOG_FORM?
0
 
gowflowCommented:
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
 
Ankit_J_PAuthor Commented:
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
 
gowflowCommented:
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
 
Ankit_J_PAuthor Commented:
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
 
Ankit_J_PAuthor Commented:
Sorry, I wanted to add Borders around the each cell that is copied from NSR Log to NSRLOG_FORM.

0
 
gowflowCommented:
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
 
Ankit_J_PAuthor Commented:
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
 
gowflowCommented:
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
 
Ankit_J_PAuthor Commented:
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
 
gowflowCommented:
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
 
Ankit_J_PAuthor Commented:
Thank you so much for all of your support and help!

Gowflow, this code worked great!

Thanks!
0
 
Ankit_J_PAuthor Commented:
Gowlfow was a great help and provided the best solution possible to my request and help.
0
 
gowflowCommented:
Your welcome Ankit J P my pleasure to have been able to help you. Tks for the grade and the nice appreciation.
gowflow
0
 
Ankit_J_PAuthor Commented:
Any recommendation so I can learn VBA like you have learned over time?  Books, online help?
0
 
gowflowCommented:
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
 
Ankit_J_PAuthor Commented:
Thanks.  Any book you would recommend?
0
 
gowflowCommented:
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
 
Ankit_J_PAuthor Commented:
Thank You!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

  • 23
  • 15
Tackle projects and never again get stuck behind a technical roadblock.
Join Now