|
|
|
|
|
|
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:
|
- Open Excel.
- Alt + F11 to open the VBE.
- Insert | Module.
- Paste the code from above into the Code Window that opens up.
- Close the VBE (Alt + Q or press the X in the top-right corner).
|
Test the code:
|
- Tools | Macro | Macros...
- Select DistributeData and press Run.
- 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.
|
|