Copy Data Based on Cell Contents

F

FooYay

Board Regular
Joined
Feb 4, 2009
Messages
53
I've written a macro to format a report of raw data that I get and it works great with one exception. The code below does everything correctly, I've removed the copy and paste portion that I had in it because it didn't seem to copy correctly.

Basically what I need is this, I've color coded the fields based on the value in column V, but I also need to copy the amount to the correct column.

Ie, for every row that contains DPLY in column V, IF there is a value in column I (not a blank), I need to copy the value from column I to column L.

Same goes for HOME, from column I to column J, and "LBE" or "PTDE" copy from column I to column K.

Is this something I can do with a macro or do I need to keep this part manual? I tried just recording the sort and filter, copy and paste in the macro recorder, but it would paste the values incorrectly (ignoring the filter).

Thanks in advance for any help!

Here is what I'm using now without copying the data, to format it etc.


Code: 
Sub PBUSEFormat()
'
' PBUSEFormat Macro
' Format PBUSE Data
'
'
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("G:I").Select
    Selection.Cut
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Columns("G:G").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("K:K").Select
    Selection.Cut
    Columns("H:H").Select
    Selection.Insert Shift:=xlToRight
    Columns("L:M").Select
    Selection.Cut
    Columns("I:I").Select
    Selection.Insert Shift:=xlToRight
    Columns("J:J").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("K:K").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("N:N").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 7
    Columns("S:S").Select
    Selection.Cut
    Columns("P:P").Select
    Selection.Insert Shift:=xlToRight
    Columns("R:R").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 17
    Columns("U:W").Select
    Selection.Cut
    Columns("S:S").Select
    Selection.Insert Shift:=xlToRight
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 26
    Columns("AF:AF").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "UNIT"
    Range("B1").Select
    ActiveCell.FormulaR1C1 = "UIC"
    Range("C1").Select
    ActiveCell.FormulaR1C1 = "PARA"
    Range("D1").Select
    ActiveCell.FormulaR1C1 = "LIN"
    Range("E1").Select
    ActiveCell.FormulaR1C1 = "SUB LIN"
    Range("F1").Select
    ActiveCell.FormulaR1C1 = "PURE LIN"
    Range("G1").Select
    ActiveCell.FormulaR1C1 = "ERC"
    Range("H1").Select
    ActiveCell.FormulaR1C1 = "AUTH"
    Range("I1").Select
    ActiveCell.FormulaR1C1 = "TOTAL OH"
    Range("J1").Select
    ActiveCell.FormulaR1C1 = "REAR OH"
    Range("K1").Select
    ActiveCell.FormulaR1C1 = "LBE OH"
    Range("L1").Select
    ActiveCell.FormulaR1C1 = "FWD OH"
    Range("M1").Select
    ActiveCell.FormulaR1C1 = "DI"
    Range("N1").Select
    ActiveCell.FormulaR1C1 = "L/T GAIN"
    Range("O1").Select
    ActiveCell.FormulaR1C1 = "L/T LOSS"
    Range("P1").Select
    ActiveCell.FormulaR1C1 = "NOMEN"
    Range("Q1").Select
    ActiveCell.FormulaR1C1 = "BDE"
    Range("R1").Select
    ActiveCell.FormulaR1C1 = "EDATE"
    Range("AF1").Select
    ActiveCell.FormulaR1C1 = "L/T G/L-BDE"
    Range("AG1").Select
    ActiveCell.FormulaR1C1 = "L/T G/L-UIC"
    Range("AH1").Select
    ActiveCell.FormulaR1C1 = "L/T SUSP"
    Range("AI1").Select
    ActiveCell.FormulaR1C1 = "L/T DIR#"
    Range("AJ1").Select
    ActiveCell.FormulaR1C1 = "L/T FRAGO#"
    Range("AK1").Select
    ActiveCell.FormulaR1C1 = "AMC"
    Range("AL1").Select
    ActiveCell.FormulaR1C1 = "SB 700-20"
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Font.Bold = True
    With Selection
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Rows("1:1").Select
    Selection.RowHeight = 28
    Columns("I:I").ColumnWidth = 5.57
    Columns("F:F").ColumnWidth = 8.14
    ActiveWindow.LargeScroll ToRight:=1
    Columns("S:S").ColumnWidth = 4.43
    Columns("W:W").ColumnWidth = 6.57
    Columns("Z:Z").ColumnWidth = 4.43
    ActiveWindow.LargeScroll ToRight:=1
    Columns("AF:AF").ColumnWidth = 5.43
    Columns("AG:AG").ColumnWidth = 6.29
    Columns("AH:AH").ColumnWidth = 5.86
    Columns("AI:AI").ColumnWidth = 4.57
    Columns("AJ:AJ").ColumnWidth = 6.86
    Columns("AL:AL").ColumnWidth = 5.57
    Columns("AL:AL").ColumnWidth = 8.43
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("N1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlSolid
        .Color = 5287936
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Range("O1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlSolid
        .Color = 255
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    ActiveWindow.LargeScroll ToRight:=2
    Range("AF1:AJ1").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlSolid
        .Color = 49407
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    ActiveWindow.ScrollColumn = 26
    ActiveWindow.ScrollColumn = 25
    ActiveWindow.ScrollColumn = 24
    ActiveWindow.ScrollColumn = 23
    ActiveWindow.ScrollColumn = 22
    ActiveWindow.ScrollColumn = 21
    ActiveWindow.ScrollColumn = 20
    ActiveWindow.ScrollColumn = 19
    ActiveWindow.ScrollColumn = 18
    ActiveWindow.ScrollColumn = 17
    ActiveWindow.ScrollColumn = 16
    ActiveWindow.ScrollColumn = 15
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 6
    ActiveWindow.ScrollColumn = 5
    ActiveWindow.ScrollColumn = 4
    ActiveWindow.ScrollColumn = 3
    ActiveWindow.ScrollColumn = 2
    ActiveWindow.ScrollColumn = 1
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Range("A1").Select
    ActiveWorkbook.Worksheets("3ID & EAB DATA").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("3ID & EAB DATA").Sort.SortFields.Add Key:=Range( _
        "V:V"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("3ID & EAB DATA").Sort.SortFields.Add Key:=Range( _
        "I:I"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    ActiveWorkbook.Worksheets("3ID & EAB DATA").Sort.SortFields.Add Key:=Range( _
        "M:M"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("3ID & EAB DATA").Sort
        .SetRange Range("A:AL")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    Selection.AutoFilter
    ActiveWindow.LargeScroll ToRight:=1
    ActiveSheet.Range("A:AL").AutoFilter Field:=22, Criteria1:="DPLY"
    ActiveWindow.LargeScroll ToRight:=-1
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A:AL").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Font
        .Color = -6750208
        .TintAndShade = 0
    End With
    ActiveSheet.Range("A:AL").AutoFilter Field:=22, Criteria1:="HOME"
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A:AL").Select
    Application.CutCopyMode = False
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Font
        .Color = -16777063
        .TintAndShade = 0
    End With
    ActiveSheet.Range("A:AL").AutoFilter Field:=22, Criteria1:="=LBE" _
        , Operator:=xlOr, Criteria2:="=PTDE"
    Range("A1").Select
    Range(Selection, Selection.End(xlDown)).Select
    Range("A:AL").Select
    Application.CutCopyMode = False
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Font
        .Color = -16751104
        .TintAndShade = 0
    End With
    Selection.AutoFilter
    Range("A1").Select
End Sub

I should also point out that a good portion of this was created using the macro recorder with some pieces edited in by me.
 
Last edited:
Sort by date Sort by votes
Also, I was going to ask, the ActiveWindow.ScrollColumn = ## lines were put in by the macro recorder, are these needed at all or can I delete those? It looks to me like it's just recording my movements around the sheet.

Thanks!
 
Upvote 0
Great thanks, any ideas on the big question on being able to copy the data based on another cell value?
 
Upvote 0
You must log in or register to reply here.

Similar threads

J
  • Question Question
Issue with Macros and renaming files
Replies
6
Views
1K
kevin9999
K
H
  • Question Question
Create automation VBA code with Power query to load data another workbook
Replies
3
Views
98
hakita
H
K
  • Solved
formating question
Replies
3
Views
356
Micron
M
L
  • Locked
  • Question Question
Help Cleaning Up Code
Replies
2
Views
682
RoryA
RoryA
C
  • Solved
Automation error The Object invoked has disconnected from its clients.
Replies
7
Views
2K
cstuder
C

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top