+ Reply to Thread
Results 1 to 3 of 3

specific stock list for sheet1 to sheet2

Hybrid View

  1. 02-26-2011, 08:46 AM #1
    begoodbetter
    begoodbetter is offline
    Registered User
    Join Date
    01-27-2011
    Location
    india
    MS-Off Ver
    Excel 2003
    Posts
    2

    specific stock list for sheet1 to sheet2

    please see attached files i have specific list of stock to copy from sheet1 to sheet2. i am uable to get results.thank u
    Sub Copydatatosheet2()
'Copy cells of cols A to M from rows containing "SYMBOL AND EQ" in
'col D of the active worksheet (source sheet) to cols
'A to M of Sheet2 (destination sheet)
Dim DestSheet As Worksheet
Set DestSheet = Worksheets("Sheet2")

Dim sRow As Long 'row index on source worksheet
Dim dRow As Long 'row index on destination worksheet
Dim sCount As Long
sCount = 0
dRow = 1

For sRow = 1 To Range("A65536").End(xlUp).Row
'use pattern matching to find "Significant" anywhere in cell
If Cells(sRow, "A") Like "SYMBOL" Then
If Cells(sRow, "B") Like "SERIES" Then
sCount = sCount + 1
dRow = dRow + 1
'copy cols A,B,C,D,E,F,G,H,I,J & K
DestSheet.Cells(dRow, "A") = Cells(sRow, "A")
DestSheet.Cells(dRow, "B") = Cells(sRow, "B")
DestSheet.Cells(dRow, "C") = Cells(sRow, "K")
DestSheet.Cells(dRow, "D") = Cells(sRow, "H")
DestSheet.Cells(dRow, "E") = Cells(sRow, "C")
DestSheet.Cells(dRow, "F") = Cells(sRow, "D")
DestSheet.Cells(dRow, "G") = Cells(sRow, "E")
DestSheet.Cells(dRow, "H") = Cells(sRow, "G")
DestSheet.Cells(dRow, "I") = Cells(sRow, "F")
DestSheet.Cells(dRow, "J") = Cells(sRow, "I")
DestSheet.Cells(dRow, "K") = Cells(sRow, "J")
End If
End If
If Cells(sRow, "A") Like "ACC" Then
If Cells(sRow, "B") Like "EQ" Then
sCount = sCount + 1
dRow = dRow + 1
'copy cols A,B,C,D,E,F,G,H,I,J & K
DestSheet.Cells(dRow, "A") = Cells(sRow, "A")
DestSheet.Cells(dRow, "B") = Cells(sRow, "B")
DestSheet.Cells(dRow, "C") = Cells(sRow, "K")
DestSheet.Cells(dRow, "D") = Cells(sRow, "H")
DestSheet.Cells(dRow, "E") = Cells(sRow, "C")
DestSheet.Cells(dRow, "F") = Cells(sRow, "D")
DestSheet.Cells(dRow, "G") = Cells(sRow, "E")
DestSheet.Cells(dRow, "H") = Cells(sRow, "G")
DestSheet.Cells(dRow, "I") = Cells(sRow, "F")
DestSheet.Cells(dRow, "J") = Cells(sRow, "I")
DestSheet.Cells(dRow, "K") = Cells(sRow, "J")
End If
End If
If Cells(sRow, "A") Like "AMBUJACEM" Then
If Cells(sRow, "B") Like "EQ" Then
sCount = sCount + 1
dRow = dRow + 1
'copy cols A,B,C,D,E,F,G,H,I,J & K
DestSheet.Cells(dRow, "A") = Cells(sRow, "A")
DestSheet.Cells(dRow, "B") = Cells(sRow, "B")
DestSheet.Cells(dRow, "C") = Cells(sRow, "K")
DestSheet.Cells(dRow, "D") = Cells(sRow, "H")
DestSheet.Cells(dRow, "E") = Cells(sRow, "C")
DestSheet.Cells(dRow, "F") = Cells(sRow, "D")
DestSheet.Cells(dRow, "G") = Cells(sRow, "E")
DestSheet.Cells(dRow, "H") = Cells(sRow, "G")
DestSheet.Cells(dRow, "I") = Cells(sRow, "F")
DestSheet.Cells(dRow, "J") = Cells(sRow, "I")
DestSheet.Cells(dRow, "K") = Cells(sRow, "J")
End If
End If
If Cells(sRow, "A") Like "AXISBANK" Then
If Cells(sRow, "B") Like "EQ" Then
sCount = sCount + 1
dRow = dRow + 1
'copy cols A,B,C,D,E,F,G,H,I,J & K
DestSheet.Cells(dRow, "A") = Cells(sRow, "A")
DestSheet.Cells(dRow, "B") = Cells(sRow, "B")
DestSheet.Cells(dRow, "C") = Cells(sRow, "K")
DestSheet.Cells(dRow, "D") = Cells(sRow, "H")
DestSheet.Cells(dRow, "E") = Cells(sRow, "C")
DestSheet.Cells(dRow, "F") = Cells(sRow, "D")
DestSheet.Cells(dRow, "G") = Cells(sRow, "E")
DestSheet.Cells(dRow, "H") = Cells(sRow, "G")
DestSheet.Cells(dRow, "I") = Cells(sRow, "F")
DestSheet.Cells(dRow, "J") = Cells(sRow, "I")
DestSheet.Cells(dRow, "K") = Cells(sRow, "J")
End If
End If
If Cells(sRow, "A") Like "BAJAJ-AUTO" Then
If Cells(sRow, "B") Like "EQ" Then
sCount = sCount + 1
dRow = dRow + 1
'copy cols A,B,C,D,E,F,G,H,I,J & K
DestSheet.Cells(dRow, "A") = Cells(sRow, "A")
DestSheet.Cells(dRow, "B") = Cells(sRow, "B")
DestSheet.Cells(dRow, "C") = Cells(sRow, "K")
DestSheet.Cells(dRow, "D") = Cells(sRow, "H")
DestSheet.Cells(dRow, "E") = Cells(sRow, "C")
DestSheet.Cells(dRow, "F") = Cells(sRow, "D")
DestSheet.Cells(dRow, "G") = Cells(sRow, "E")
DestSheet.Cells(dRow, "H") = Cells(sRow, "G")
DestSheet.Cells(dRow, "I") = Cells(sRow, "F")
DestSheet.Cells(dRow, "J") = Cells(sRow, "I")
DestSheet.Cells(dRow, "K") = Cells(sRow, "J")
End If
End If
If Cells(sRow, "A") Like "BHARTIARTL" Then
If Cells(sRow, "B") Like "EQ" Then
sCount = sCount + 1
dRow = dRow + 1
'copy cols A,B,C,D,E,F,G,H,I,J & K
DestSheet.Cells(dRow, "A") = Cells(sRow, "A")
DestSheet.Cells(dRow, "B") = Cells(sRow, "B")
DestSheet.Cells(dRow, "C") = Cells(sRow, "K")
DestSheet.Cells(dRow, "D") = Cells(sRow, "H")
DestSheet.Cells(dRow, "E") = Cells(sRow, "C")
DestSheet.Cells(dRow, "F") = Cells(sRow, "D")
DestSheet.Cells(dRow, "G") = Cells(sRow, "E")
DestSheet.Cells(dRow, "H") = Cells(sRow, "G")
DestSheet.Cells(dRow, "I") = Cells(sRow, "F")
DestSheet.Cells(dRow, "J") = Cells(sRow, "I")
DestSheet.Cells(dRow, "K") = Cells(sRow, "J")
End If
End If
If Cells(sRow, "A") Like "BHEL" Then
If Cells(sRow, "B") Like "EQ" Then
sCount = sCount + 1
dRow = dRow + 1
'copy cols A,B,C,D,E,F,G,H,I,J & K
DestSheet.Cells(dRow, "A") = Cells(sRow, "A")
DestSheet.Cells(dRow, "B") = Cells(sRow, "B")
DestSheet.Cells(dRow, "C") = Cells(sRow, "K")
DestSheet.Cells(dRow, "D") = Cells(sRow, "H")
DestSheet.Cells(dRow, "E") = Cells(sRow, "C")
DestSheet.Cells(dRow, "F") = Cells(sRow, "D")
DestSheet.Cells(dRow, "G") = Cells(sRow, "E")
DestSheet.Cells(dRow, "H") = Cells(sRow, "G")
DestSheet.Cells(dRow, "I") = Cells(sRow, "F")
DestSheet.Cells(dRow, "J") = Cells(sRow, "I")
DestSheet.Cells(dRow, "K") = Cells(sRow, "J")
End If
End If
If Cells(sRow, "A") Like "BPCL" Then
If Cells(sRow, "B") Like "EQ" Then
sCount = sCount + 1
dRow = dRow + 1
'copy cols A,B,C,D,E,F,G,H,I,J & K
DestSheet.Cells(dRow, "A") = Cells(sRow, "A")
DestSheet.Cells(dRow, "B") = Cells(sRow, "B")
DestSheet.Cells(dRow, "C") = Cells(sRow, "K")
DestSheet.Cells(dRow, "D") = Cells(sRow, "H")
DestSheet.Cells(dRow, "E") = Cells(sRow, "C")
DestSheet.Cells(dRow, "F") = Cells(sRow, "D")
DestSheet.Cells(dRow, "G") = Cells(sRow, "E")
DestSheet.Cells(dRow, "H") = Cells(sRow, "G")
DestSheet.Cells(dRow, "I") = Cells(sRow, "F")
DestSheet.Cells(dRow, "J") = Cells(sRow, "I")
DestSheet.Cells(dRow, "K") = Cells(sRow, "J")
End If
End If

Next sRow

MsgBox sCount & " Significant rows copied", vbInformation, "Transfer Done"

End Sub
    Last edited by begoodbetter; 02-26-2011 at 09:50 AM.
    Reply With QuoteRegister To Reply
  2. 02-26-2011, 04:03 PM #2
    Charles
    Charles is offline
    Valued Forum Contributor Charles's Avatar
    Join Date
    02-10-2004
    Location
    Biloxi
    MS-Off Ver
    Windows 7, Excel 2003,2007 & Mac2011
    Posts
    845

    Re: specific stock list for sheet1 to sheet2

    HI,

    Welcome to the forum, perhaps a copy of your file would help in determining what your problem is, and what you expected to see.
    Charles

    There are other ways to do this, this is but 1 !
    Be Sure you thank those who helped.
    IF YOU'RE SATISFIED BY ANY MEMBERS RESPONSE TO YOUR ISSUE PLEASE USE THE STAR ICON AT THE BOTTOM LEFT OF THE POST UNDER THEIR NAME.
    Reply With QuoteRegister To Reply
  3. 02-26-2011, 04:16 PM #3
    Leith Ross
    Leith Ross is offline
    Forum Moderator Leith Ross's Avatar
    Join Date
    01-15-2005
    Location
    San Francisco, Ca
    MS-Off Ver
    2000, 2003, & 2010
    Posts
    23,259

    Re: specific stock list for sheet1 to sheet2

    Hello begoodbetter,

    Here is the updated version of your macro. Try it and let me know if anything needs to be changed.
    Sub Copydatatosheet2()

 'Copy cells of cols A to M from rows containing "SYMBOL AND EQ" in
 'col D of the active worksheet (source sheet) to cols
 'A to M of Sheet2 (destination sheet)

  Dim DestSheet As Worksheet
  Dim sData() As Variant
  Dim sRow As Long 'row index on source worksheet
  Dim dRow As Long 'row index on destination worksheet
  Dim sCount As Long

    sCount = 0
    dRow = 1
    
    ReDim sData(1 To 11)   'Number of columns to be copied
    
    Set DestSheet = Worksheets("Sheet2")
    
      For sRow = 1 To Range("A65536").End(xlUp).Row

        Select Case Cells(sRow, "B")
          Case Is = "SERIES", "EQ"
      
            Select Case Cells(sRow, "A")
              Case Is = "Symbol", "ACC", "AMBUJACEM", "AXISBANK", "BAJAJ-AUTO", _
                        "BHARTIARTL", "BHEL", "BPCL"
      
                sData(1) = Cells(sRow, "A")
                sData(2) = Cells(sRow, "B")
                sData(3) = Cells(sRow, "K")
                sData(4) = Cells(sRow, "H")
                sData(5) = Cells(sRow, "C")
                sData(6) = Cells(sRow, "D")
                sData(7) = Cells(sRow, "E")
                sData(8) = Cells(sRow, "G")
                sData(9) = Cells(sRow, "F")
                sData(10) = Cells(sRow, "I")
                sData(11) = Cells(sRow, "J")

                sCount = sCount + 1
                dRow = dRow + 1

                DestSheet.Cells(dRow, "A").Resize(1, UBound(sData)).Value = sData
            End Select
           
        End Select
    
      Next sRow

    MsgBox sCount & " Significant rows copied", vbInformation, "Transfer Done"

End Sub
    Sincerely,
    Leith Ross

    Remember To Do the Following....

    1. Use code tags. Place [CODE] before the first line of code and [/CODE] after the last line of code.
    2. Thank those who have helped you by clicking the Star below the post.
    3. Please mark your post [SOLVED] if it has been answered satisfactorily.


    Old Scottish Proverb...
    Luathaid gu deanamh maille! (Rushing causes delays!)
    Reply With QuoteRegister To Reply
+ Reply to Thread
« Previous Thread | Next Thread »

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Forum Rules


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1