VBA Move data from one sheet to multiple sheets

Share

Facebook
Twitter
LinkedIn

This is a guest post by Vijay, our in-house VBA Expert.

Suresh sent an email with interesting problem.

There is one data entry sheet where all the data needs will be entered, however once done we want the data to be stored separately in multiple sheets designated by the Employee code.

In this article we will learn how to use VBA to help in resolving the problem Suresh was facing at work.

We will do this using VBA and the below procedure illustrates how this was done.

Sub copyPasteData()
Dim strSourceSheet As String
Dim strDestinationSheet As String
Dim lastRow As Long

strSourceSheet = "Data entry"

Sheets(strSourceSheet).Visible = True
Sheets(strSourceSheet).Select

Range("C2").Select
Do While ActiveCell.Value <> ""
strDestinationSheet = ActiveCell.Value
ActiveCell.Offset(0, -2).Resize(1, ActiveCell.CurrentRegion.Columns.Count).Select
Selection.Copy
Sheets(strDestinationSheet).Visible = True
Sheets(strDestinationSheet).Select
lastRow = LastRowInOneColumn("A")
Cells(lastRow + 1, 1).Select
Selection.PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheets(strSourceSheet).Select
ActiveCell.Offset(0, 2).Select
ActiveCell.Offset(1, 0).Select
Loop
End Sub

Let’s understand the code

We start by defining 2 variables which will hold the Source and destinations sheet names, source will be one sheet only however there will be multiple destinations sheets.

A simple Do While loop is utilized to complete this data transfer from one sheet to the others.

However the most interesting line in the code is :

ActiveCell.Offset(0, -2).Resize(1, ActiveCell.CurrentRegion.Columns.Count).Select

Since we start our work on Column C in the loop, we need to ensure the data from Column A and B is also selected to be copied and pasted on the relevant sheets, this is accomplished using the above line.

Once we have selected the entire data set on the current row, we copy the same and select the relevant sheet and then paste the data as values on the same.

At the end of the code once we switch back to the Source sheet, we need to ensure that we select Column C again. The reason for that is, when we select the entire data set we activate the first column and we need to come back to column C for our loop and the rest of the code to work.

 

Download Excel File

Click here to download the file & save it on your system and use it to understand this technique.

More on VBA & Macros

If you want to learn more about using VBA to automate reporting & email tasks, read these:

Join our VBA Classes

If you want to learn how to develop applications like these and more, please consider joining our VBA Classes. It is a step-by-step program designed to teach you all concepts of VBA so that you can automate & simplify your work.

Click here to learn more about VBA Classes & join us.

Facebook
Twitter
LinkedIn

Share this tip with your colleagues

Excel and Power BI tips - Chandoo.org Newsletter

Get FREE Excel + Power BI Tips

Simple, fun and useful emails, once per week.

Learn & be awesome.

Welcome to Chandoo.org

Thank you so much for visiting. My aim is to make you awesome in Excel & Power BI. I do this by sharing videos, tips, examples and downloads on this website. There are more than 1,000 pages with all things Excel, Power BI, Dashboards & VBA here. Go ahead and spend few minutes to be AWESOME.

Read my storyFREE Excel tips book

Overall I learned a lot and I thought you did a great job of explaining how to do things. This will definitely elevate my reporting in the future.
Rebekah S
Reporting Analyst
Excel formula list - 100+ examples and howto guide for you

From simple to complex, there is a formula for every occasion. Check out the list now.

Calendars, invoices, trackers and much more. All free, fun and fantastic.

Advanced Pivot Table tricks

Power Query, Data model, DAX, Filters, Slicers, Conditional formats and beautiful charts. It's all here.

Still on fence about Power BI? In this getting started guide, learn what is Power BI, how to get it and how to create your first report from scratch.

9 Responses to “Show forecast values in a different color with this simple trick [charting]”

  1. Jake says:

    While this works in a pinch, it clearly "lightens" the colors of the entire chart. Depending on where you use this, it will be blatantly obvious that you don't know what you are doing and present a poor looking graph.

    Why not separate the data into different segments when charting and have as many colors as you have data points? You might have to create a new legend and/or repeat the chart in "invisible ink", but it would be cleaner and more consistent when new or updated data becomes available.

    • Andy F says:

      While I think I agree that doing it "properly" via a second series is preferable, I don't necessarily agree that making the entirety of the "future" (data, gridlines, and even the axis) semi-transparent is "poor looking". I think it could be seen as adding more emphasis to the "future-ness" of the forecast data.

      In short, it's another tool for the toolbox, even if it's never needed.

  2. Kiev says:

    Quick & effective, cool. thanks.

  3. dan l says:

    I always use the dummy series.

  4. Peter Stratton says:

    Nice little trick, thanks very much!

  5. excel says:

    Two sets of data better. Control is much better.
    You can use the same chart next month to see what is actual and what is forecast.

    To use this trick, I think grid lines has to be removed, that will make the graphic much more sharp.

  6. gossip_boi says:

    to be honest, i dont understand why there is needed to do this way... in this case horizontal lines will be pale as well. then why a just can't change the color of the line partly???

  7. Great tutorial. Thanks for the tutorial!

Leave a Reply