1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Macros data from Row to Col required

Discussion in 'VBA Macros' started by Techtrend, Jun 11, 2018.

  1. Techtrend

    Techtrend Member

    Messages:
    227
    Sheet 3 Has
    SYMBOL / OPEN / HIGH / LOW / PRICE
    End of the day i copy the data to this Sheet 3
    The same has to be Copied in List sheet one below the other.

    For ex

    SYMBOL OPEN HIGH LOW PRICE
    ACC 1312.05 1343 1312.05 1334.05

    Has to be copied to List sheet 15 Col under b,C,D,E COl

    next

    SYMBOL OPEN HIGH LOW PRICE

    ADANIENT 127.4 132.9 126.75 129.2

    and next are formulas for every script

    Has to be copied to List sheet 15 Col under Q,R,S,T COl

    The next days data will go under 16 Col Vice versa

    New to macros,Can u pl help me on the same

    Attached Files:

  2. AlanSidman

    AlanSidman Active Member

    Messages:
    363
    This should work for you so long as your companies are listed in the same order in both sheets. Failure to have the listings exactly the same will produce erroneous results. I did notice in your sample that not all companies are listed on both sheets. Therefore the caution.

    Code (vb):
    Option Explicit

    Sub transfer()
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        Dim s1 As Worksheet, s2 As Worksheet
        Set s1 = Sheets("LIST")
        Set s2 = Sheets("Sheet3")
        Dim i As Long, lr As Long, lc As Long, j As Long, lr2 As Long
        lr = s1.Range("B" & Rows.Count).End(xlUp).Row + 1
        lr2 = s2.Range("A" & Rows.Count).End(xlUp).Row
        lc = s1.Cells(1, Columns.Count).End(xlToLeft).Column
        j = 1
        For i = 2 To lr2
            s1.Cells(lr, j) = Date
            s1.Cells(lr, j + 1) = s2.Range("E" & i)
            s2.Range("B" & i & ":D" & i).Copy s1.Cells(lr, j + 2)
            s1.Range(Cells(lr - 1, j + 5), Cells(lr - 1, j + 13)).Copy s1.Cells(lr, j + 5)
            j = j + 15
        Next i
        Application.CutCopyMode = False
        Application.Calculation = xlCalculationAutomatic
        Application.ScreenUpdating = True
        MsgBox "complete"



    End Sub
     
  3. Techtrend

    Techtrend Member

    Messages:
    227
    Hello Alan

    Yes alan i deleted lots of data As i had space issue for uploading a excel attachment,Can i have your email id will send u the full file,

    The code i added give errors might be bcaz of this
    The file is also located in https://www.dropbox.com/s/t256jzpc2k0e0zn/VOLSTRETCH-DAILY.rar?dl=0

    Looking for a freelancer for similar kid of jobs can u please give a costing
    Thanks in advance
    Narendra Kumar
  4. AlanSidman

    AlanSidman Active Member

    Messages:
    363
    Well, first of all,I am not a mind reader so I cannot diagnose your issue as you have not provided the code you added that is causing you issues. Additionally, when you get error on the code, what is the error message and when you get the error message and click on debug, which line of code is highlighted. Without this information, I cannot help.

    Secondly, when I try to load your file, it crashes my computer. Done with that! We don't need the entire file, only a sample representative of the original. However, having said that, it must be laid out exactly as the original for any code provided to work.

    And finally, I no longer do freelance work and only help on forums but I do not take on projects. Suggest you look in your community for a skilled programmer that can sit with you and understand your needs. It will be less expensive. When I did freelance, my billing rate was $85/hour US to give you an understanding of what a project may cost.

    If you are willing to provide the necessary information in this thread as outlined above, as time is available, I will try to help.

Share This Page