Copy cell values, add a new row on table and paste them at the bottom of a table

ApexDmac · Apr 23rd 2019

I have been trying to make sheet take all the information highlighted in yellow on the "check sheet" and moving it to the "log". On this page I want it to add a new row and paste in the data in yellow.

I tried doing a test of the macro that I would use but it does not add an additional row when I use it

Code

Range("B2:D2").Select
Selection.Copy
Sheets("Log").Select
Range("Log[[#Headers],").Select
    Selection.End(xlDown).Select
    Range("A7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("B10").Select
    Sheets("Item Code").Select
    Range("B3:C3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Log").Select
    Range("Log[[#Headers],[Descripition]]").Select
    Selection.End(xlDown).Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("C7").Select
    Sheets("Item Code").Select
    Range("D3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Log").Select
    Range("Log[[#Headers],[VRLA AGM]]").Select
    Selection.End(xlDown).Select
    ActiveSheet.Paste
    Selection.ClearContents
    Sheets("Item Code").Select
    Selection.Copy
    Sheets("Log").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Item Code").Select

Display More

I just haven't been able to work out how to do this, any help would be appreciated

I believe I may be going about this the wrong way

ApexDmac · Apr 24th 2019

I ended up having a crack and making it work, if anyone could help me to refine it that would be appreciated

Code

Range("A35:D40").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlTop
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.unmerge
    Range("C42:D42").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.unmerge
    Range("B2:D6").Select
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    With Selection
        .HorizontalAlignment = xlGeneral
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.unmerge
    Sheets("Log").Select
    Rows("3:3").Select
    Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatOriginConstant
    Sheets("Item Code").Select
    Range("B2").Select
    Selection.Copy
    Sheets("Log").Select
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Item Code").Select
    Range("B3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Log").Select
    Range("B3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Item Code").Select
    Range("D3").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Log").Select
    Range("C3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Item Code").Select
    Range("B4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Log").Select
    Range("D3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Item Code").Select
    Range("B5").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Log").Select
    Range("E3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Item Code").Select
    Range("B6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Log").Select
    Range("F3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Item Code").Select
    Range("D9:D12").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Log").Select
    Range("G3").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Sheets("Item Code").Select
    Range("D21:D24").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Log").Select
    Range("K3").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Sheets("Item Code").Select
    Range("D27:D33").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Log").Select
    Range("O3").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Sheets("Item Code").Select
    Range("A35").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Log").Select
    Range("V3").Select
    ActiveSheet.Paste
    Sheets("Item Code").Select
    Range("A43").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Log").Select
    Range("W3").Select
    ActiveSheet.Paste
    Sheets("Item Code").Select
    Range("C43").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Log").Select
    Range("X3").Select
    ActiveSheet.Paste
    Sheets("Item Code").Select
    Range("B2:D2").Select
    Application.CutCopyMode = False
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Range("B3:C3").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Range("B4:D4").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Range("B5:D5").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Range("B6:D6").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
        Range("A35:D40").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlTop
        .WrapText = True
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    Range("C42:D42").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    'testing formatting on log page
    Sheets("Log").Select
    Range("Z4:AT4").Select
    Selection.AutoFill Destination:=Range("Z3:AT4"), Type:=xlFillDefault
    Range("Z3:AT4").Select
    Sheets("Item Code").Select
    Range("M6").Select
    Range("M5").Select
End Sub

Display More

Copy cell values, add a new row on table and paste them at the bottom of a table

Participate now!

Share

Users Viewing This Thread