[Last Call] Learn how to a build a cloud-first strategyRegister Now


populate cells on current sheet depending on data on another sheet

Posted on 2014-08-21
Medium Priority
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 54

Accepted Solution

Rgonzo1971 earned 2000 total points
ID: 40275890

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

ID: 40276330
perfect as usual Rgonzo many thanks

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial demonstrates how to create Excel charts: column, area, line, bar, and scatter charts. Formatting tips are provided as well.

829 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