Copy a row...

[niloc85]

Hi,

Im looking for some help as I dont use excel much!

I would like to have a formula/script to do the following...

I have a cell (C3) within sheet one which will contain a text value based upon a dropdown list. That text could be for example A, B or C. Based upon that text value of A, B or C within C3 I would like to copy a row from sheet two C20-30 to sheet one location C20-30.

Is this possible in a easy enough way?

Thanks if your able to help.

 

[niloc85]

So what will happen is...

1. User will select a value in cell B11 (Capacity).
2. Depending on what is selected in B11 (Capacity), the relevant product will be copied from B4:10-L4:10 (Devices)
3. The copied cells will appear in the same section (in this example) M11-W11.

Does this make sense?

 

[L. Howard]

With iPad in B11 Capacity

This is copied from Devices B4 - L4 to where, sheet and cells?

Apple iPad 802.11n 1 1 40 Both 72.2 72.2 135

Howard

 

[niloc85]

Sheet: Capcity
Cells: M11-W11

 

[L. Howard]

Aha, as I pan over I see the prompts you put in. Sorry, I missed that at first.

Howard

 

[L. Howard]

deleted by me

 

[L. Howard]

Try this workbook.

Where there are drop downs in B11 to B30 on Capacity.

Select a test data entry from any drop down and look at columns M to W of that row for phony test data returns.

Check ranges to verify if correct. You, of course, will use your real data in the correct ranges for your real set.

The message box display can be commented out quite easily.

The Blue highlight is the list for the drop downs.




https://www.dropbox.com/s/wigc0jt8qj7yt5h/Leys Calculator With Change Event.xlsm?dl=0

The code for those no interested in opening links.


Howard

Option Explicit


Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub

    Dim iRng As Range
    Set iRng = Range("B11:B30")
    
    If Not Application.Intersect(iRng, Range(Target.Address)) _
       Is Nothing Then
           
       Dim shtCap As Worksheet, shtDev As Worksheet
       Dim shtDevlRow As Long
       Dim rngFound As Range
       Dim myFnd As String
    
       Set shtCap = ThisWorkbook.Sheets("Capacity")
       Set shtDev = ThisWorkbook.Sheets("Devices")

       myFnd = Target.Value

       With shtDev
           shtDevlRow = .Range("C" & .Rows.Count).End(xlUp).Row
    
           With .Range("C4:C" & shtDevlRow)
              Set rngFound = .Find(What:=myFnd, _
                            LookIn:=xlValues, _
                            LookAt:=xlWhole, _
                            SearchOrder:=xlByRows, _
                            SearchDirection:=xlNext, _
                            MatchCase:=False)
                                               
               If Not rngFound Is Nothing Then
                   MsgBox rngFound.Address
                   rngFound.Offset(0, -1).Resize(1, 11).Copy Target.Offset(, 11)
                 Else
                   MsgBox "Not Found"
               End If
            
           End With
        
       End With
       
    End If
End Sub

 

[niloc85]

Thanks, this seems to work perfect, how do you remove the msg box?

 

[L. Howard]

Thanks, this seems to work perfect, how do you remove the msg box?

Delete the code in red text.

The other MsgBox "Not Found" should stay, as it is part of error checking should a value not be found on the list.

Howard

               If Not rngFound Is Nothing Then
                   [COLOR="#FF0000"]MsgBox rngFound.Address[/COLOR]
                   rngFound.Offset(0, -1).Resize(1, 11).Copy Target.Offset(, 11)
                 Else
                   MsgBox "Not Found"
               End If

 

[niloc85]

This works perfectly! I just need to do the same now for a few other cells in inputs and applications..... Trying to make sense of the code for a non coder is not easy!

Here is the sheet so far with add ons.

Can I just add to the existing macro?
https://www.dropbox.com/s/bnp46v738tvmzhn/Leys Calculator With Change Event_neil edit.xlsm?dl=0

 

[L. Howard]

This works perfectly! I just need to do the same now for a few other cells in inputs and applications..... Trying to make sense of the code for a non coder is not easy!

Here is the sheet so far with add ons.

Can I just add to the existing macro?
https://www.dropbox.com/s/bnp46v738tvmzhn/Leys Calculator With Change Event_neil edit.xlsm?dl=0

You don't say what you want to do.

What do you want to happen regarding sheets Inputs and Applications and the data on them?

Howard