Sub TransferData() Application.ScreenUpdating = False Dim sh As Worksheet Dim ws As Worksheet Dim lrow As Long Dim rng As Range Set ws = Sheets("Master ROAD LIST") lrow = ws.Cells(Rows.Count, 1).End(xlUp).Row For Each sh In Worksheets If sh.Name <> "Master ROAD LIST" And sh.Name <> "Cul-de Sac" And sh.Name <> "ss9" Then sh.UsedRange.Offset(1).ClearContents End If Next For Each rng In ws.Range("L3:L" & lrow) Text = Mid(rng.Value, 1) Select Case Text Case Is = "1" rng.EntireRow.Copy Sheets("SECTOR 1").Cells(Rows.Count, 1).End(3)(2).PasteSpecial xlPasteValues Case Is = "2" rng.EntireRow.Copy Sheets("SECTOR 2").Cells(Rows.Count, 1).End(3)(2).PasteSpecial xlPasteValues Case Is = "3" rng.EntireRow.Copy Sheets("SECTOR 3").Cells(Rows.Count, 1).End(3)(2).PasteSpecial xlPasteValues Case Is = "4" rng.EntireRow.Copy Sheets("SECTOR 4").Cells(Rows.Count, 1).End(3)(2).PasteSpecial xlPasteValues Case Is = "5" rng.EntireRow.Copy Sheets("SECTOR 5").Cells(Rows.Count, 1).End(3)(2).PasteSpecial xlPasteValues Case Is = "6" rng.EntireRow.Copy Sheets("SECTOR 6").Cells(Rows.Count, 1).End(3)(2).PasteSpecial xlPasteValues End Select Next For Each sh In Worksheets If sh.Name <> "Master ROAD LIST" Then sh.Columns.AutoFit End If Next MsgBox "Data Transfer complete!", vbExclamation Application.CutCopyMode = False Application.ScreenUpdating = True End Sub
Option Explicit Sub MoveStuff() Dim ar As Variant, i As Integer ar = [{"SECTOR 1","SECTOR 2","SECTOR 3","SECTOR 4","SECTOR 5","SECTOR 6";1,2,3,4,5,6}] Application.ScreenUpdating = False For i = 1 To UBound(ar, 2) Sheets(ar(1, i)).UsedRange.Offset(1).ClearContents With Sheet1 .AutoFilterMode = False With Range("L2", Range("L" & Rows.Count).End(xlUp)) .AutoFilter 1, ar(2, i) .Offset(1).EntireRow.Copy Sheets(ar(1, i)).Range("A" & Rows.Count).End(xlUp)(2).PasteSpecial xlPasteValues ActiveSheet.AutoFilterMode = False Sheets(ar(1, i)).Columns.AutoFit End With End With Next i Application.ScreenUpdating = True Application.CutCopyMode = False MsgBox "All done!", vbExclamation End Sub
DON'T MISS
Here is the link: http://speedy.sh/cCyST/DAT-drop-box-04-27-2016.xlsx
Basically what I need to happen is when I enter data on the master road list sheet, column "L" corresponds to one of the other sheets "sector 1,2,3" etc. I would like the data I enter from the master road sheet to populate on the corresponding sector sheet automatically.
I really appreciate you taking the time to look at my issue!
Many Thanks!
Matt 3502