populate cells on current sheet depending on data on another sheet

Posted on 2014-08-21
Last Modified: 2014-08-21
would appreciate an expert providing me with VBA code that will populate cells on Sheet2 based on the number of rows in Sheet1.

i.e. Sheet 1 contains data in cells A2-P2. [Row 1 is the header] The number of rows will change daiy so today there could be 50 rows tomorrow 250 next day 100 and so on.

depending on the number of rows each day I will clear the data from my sheet 2 and then I want to add a formula based on the same number of rows.

So if there are 50 rows today I need to populate cell B2 in sheet 2 with =Sheet1!A2&Sheet1!D2&Sheet1!E2&Sheet1!F2&Sheet1!C2 and copy this down to cover the same number of rows that are in sheet 1.

I also have formulas in C2,D2 and E2 but I presume I can replicate the code that an expert provides for these.

Thanks in advance
Question by:Jagwarman
    LVL 47

    Accepted Solution


    pls try

    Sub macro()
    Set OrigSheet = ActiveWorkbook.Sheets("Sheet1")
    Set DestSheet = ActiveWorkbook.Sheets("Sheet2")
    NrOfRows = OrigSheet.Range("A" & Rows.Count).End(xlUp).Row - 1
    Range(Range("A2"), Range("A" & Rows.Count).End(xlUp)).EntireRow.Delete
    DestSheet.Range("A2").Formula = "=Sheet1!A2&Sheet1!D2&Sheet1!E2&Sheet1!F2&Sheet1!C2"
    DestSheet.Range("A2").Copy Destination:=DestSheet.Range("A2").Resize(NrOfRows)
    End Sub

    Open in new window


    Author Closing Comment

    perfect as usual Rgonzo many thanks

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Find Ransomware Secrets With All-Source Analysis

    Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

    A little background as to how I came to I design this code: Around 5 years ago I designed an add-in that formatted Excel files to a corporate standard, applying different cell colours and font type depending on whether the cells contained inputs,…
    Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
    This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

    759 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    14 Experts available now in Live!

    Get 1:1 Help Now