Excel

Distribute Data from One Main Worksheet to Several Other Worksheets

Ease of Use

Easy

Version tested with

2002, 2003 

Submitted by:

Jacob Hilderbrand

Description:

This macro will take all the data from one worksheet and distribute it to other worksheets in the workbook. Column A of the main worksheet houses the name of the destination worksheet for that row of data. 

Discussion:

You may import a lot of data from another program and you need to break that data down into sections. This macro will take all the data and copy it to various worksheets throughout the workbook. The data will be moved to a specific worksheet based on the text in Column A of the main worksheet. If a worksheet does not exist you will receive an error message alerting you to the issue at the end of the macro. 

Code:

instructions for use

			

Option Explicit Sub DistributeData() Dim i As Long Dim LastRow As Long Dim ws As Worksheet Dim ErrorLog As String With Sheets("Main") LastRow = .Range("A65536").End(xlUp).Row For i = 2 To LastRow On Error Resume Next Set ws = Sheets(.Range("A" & i).Text) On Error GoTo 0 If ws Is Nothing Then ErrorLog = ErrorLog & vbNewLine & _ "Row:" & i & " Sheet Name: " & .Range("A" & i).Text Else .Range("A" & i).EntireRow.Copy _ Destination:=ws.Range("A65536").End(xlUp).Offset(1, 0) End If Set ws = Nothing Next i End With If ErrorLog <> "" Then ErrorLog = "The following worksheets could not be found " & _ "and the data was not transfered over." & vbNewLine & vbNewLine & ErrorLog MsgBox ErrorLog End If Set ws = Nothing End Sub

How to use:

  1. Open Excel.
  2. Alt + F11 to open the VBE.
  3. Insert | Module.
  4. Paste the code from above into the Code Window that opens up.
  5. Close the VBE (Alt + Q or press the X in the top-right corner).
 

Test the code:

  1. Tools | Macro | Macros...
  2. Select DistributeData and press Run.
  3. Refer to the attachment which also contains sample data.
 

Sample File:

DistributeData.ZIP 9.25KB 

Approved by mdmackillop


This entry has been viewed 354 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express