Conditionally copy the named Range

[luvbite38]

Hi Guys,

I hope I can get some help from someone.

Situation:

I have a dropdown list (containing 109 diffirent values) in cell A1 of sheet named "working" and in another sheets named DataSheet the drop values are present in Column A.


Desire:

If I select a value from the dropdown box (lets say A), I want macro to go to DataSheet and find the A (which will always be Column A) and copy the entire row except the first column (let say if it found the A in row 1, it should copy range from 1:1 minus the cell A1) and paste as speacial (value, format and tranpose to be true) in sheet called Target.

Hope it makes sense?

Thanks in advance.

Regards,

 

[taurean]

Try the following code:

Public Sub CopyData()
Dim rFind As Range
Dim lRow As Long
Application.ScreenUpdating = False
With Sheets("DataSheet")
    Set rFind = .Range("A1:A" & Rows.Count).Find(What:=Sheets("working").Range("A1"), _
    LookIn:=xlValues, Lookat:=xlWhole)
    '***************************************************************************
    'You are probably using Data Validation
    'Still, if it can't find the entry you choose then!
    '***************************************************************************
    If rFind Is Nothing Then MsgBox "Selected Entry Not Found!": Exit Sub
    lRow = rFind.Row
    .Range(.Cells(lRow, 2), .Cells(lRow, Columns.Count)).Copy
    '***************************************************************************
    'I have assumed that the data needs to be pasted on rightmost empty column
    'Change it to suit
    '***************************************************************************
    Sheets("Target").Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).PasteSpecial xlPasteAll, _
    xlPasteSpecialOperationNone, False, True
    Application.CutCopyMode = False
End With
Application.ScreenUpdating = True
End Sub

 

[luvbite38]

Man, you're a super star......

Thanks a MillioN

 

[taurean]

Man, you're a super star......

Thanks a MillioN

You are not serious, are you ? Thanks for the feedback.

 

[luvbite38]

Hi Man,

Could you possibly help me further modify the case I stated above??


This is for another purpuse but on slightly upper level then earlier,


Similar to previous, if I select Boxing in Cell. I copy the entire row - first cell and paste tranpose in target sheet but this time only copy across the names and values with 3 in front of them.

The ranks will always be between 0-3

Thanks mate.......

I'll really appreciate if you can crack this for me.

Regards,

<TABLE style="WIDTH: 240pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=320 border=0><COLGROUP><COL style="WIDTH: 48pt" span=5 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: white; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 48pt; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; HEIGHT: 15pt; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none" width=64 height=20> </TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Rob</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Bobby</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Dillon</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext; WIDTH: 48pt; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent" width=64>Billy</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Swimming</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #c00000; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">0</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #ffc000; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">2</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #95b3d7; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">1</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #c00000; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Boxing</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #92d050; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">3</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #ffc000; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">2</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #92d050; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">3</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #92d050; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Kung Fu</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #92d050; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">3</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #95b3d7; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">1</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #c00000; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">0</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #c00000; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">0</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>XBox </TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #c00000; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">0</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #92d050; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">3</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #c00000; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">0</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #ffc000; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">2</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>VBA Geek</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #95b3d7; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">1</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #92d050; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">3</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #95b3d7; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">1</TD><TD class=xl64 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #ffc000; BORDER-LEFT: windowtext 0.5pt solid; COLOR: black; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">2</TD></TR></TBODY></TABLE>

 

[taurean]

I have some doubts:
1. Do you want to copy the values (0-3) or The names at the top row(Rob, Bobby) or both?

2. How do you plan to handle values other than 3? There should be blanks in the transposed data or all values containing "3" shall be pasted adjacent to each other?

If you could post, a sample result (boxing) then it would clear the whole picture!!

 

[luvbite38]

Thanks mate,

The sample box result is as follow:

<TABLE style="WIDTH: 169pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=225 border=0><COLGROUP><COL style="WIDTH: 115pt; mso-width-source: userset; mso-width-alt: 5595" width=153><COL style="WIDTH: 54pt; mso-width-source: userset; mso-width-alt: 2633" width=72><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl67 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 169pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: #0065a4" width=225 colSpan=2 height=20>Boxing</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Rob</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #92d050; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">3</TD></TR><TR style="HEIGHT: 15pt" height=20><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Dillon</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #92d050; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">3</TD></TR>

<TR style="HEIGHT: 15pt" height=20><TD class=xl66 id=td_post_2887661 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" height=20>Billy</TD><TD class=xl66 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; FONT-WEIGHT: 400; FONT-SIZE: 10pt; BACKGROUND: #92d050; BORDER-LEFT: windowtext 0.5pt solid; COLOR: white; BORDER-BOTTOM: windowtext 0.5pt solid; FONT-FAMILY: Calibri; TEXT-DECORATION: none; text-underline-style: none; text-line-through: none; mso-pattern: black none">3</TD></TR></TBODY></TABLE>

 

[taurean]

The following code should work if you retain the previous layout as it is:
3 Sheets "working", "DataSheet", "Target"

Your Table will be in "DataSheet". The Data will be pasted in "Target". The search item (boxing) will be in "working" cell A1 like before.

Here's the revised code:

Public Sub CopyData()
Dim rFind As Range, r As Range, rMatch As Range
Dim lRow As Long, lCol As Long
Application.ScreenUpdating = False
With Sheets("DataSheet")
    Set rFind = .Range("A1:A" & Rows.Count).Find(What:=Sheets("working").Range("A1"), _
    LookIn:=xlValues, Lookat:=xlWhole)
    '***************************************************************************
    'You are probably using Data Validation
    'Still, if it can't find the entry you choose then!
    '***************************************************************************
    If rFind Is Nothing Then MsgBox "Selected Entry Not Found!": Exit Sub
    
    lRow = rFind.Row
    lCol = Sheets("Target").Cells(2, Columns.Count).End(xlToLeft).Offset(, 1).Column
    If lCol = 2 Then lCol = 1
    
    Sheets("working").Range("A1").Copy Sheets("Target").Cells(1, lCol)
    Set rMatch = .Range(.Cells(lRow, 2), .Cells(lRow, Columns.Count))
        For Each r In rMatch
            If r.Value = 3 Then
            .Cells(1, r.Column).Copy _
            Sheets("Target").Cells(Rows.Count, lCol).End(xlUp).Offset(1, 0)
            r.Copy Sheets("Target").Cells(Rows.Count, lCol).End(xlUp).Offset(0, 1)
            End If
        Next r
End With
Application.ScreenUpdating = True
End Sub

 

[luvbite38]

Idk if someone eva told tht u're a super duper star.......

Buddy, you're the be$$$t and I really can't thank u enough for all your assistance and help.....