Results 1 to 2 of 2

VBA code to copy cell values to the next blank row in another sheet (all the same row)

Threaded View

rarias VBA code to copy cell values... 05-10-2013, 04:49 PM
HaHoBe Re: VBA code to copy cell... 05-11-2013, 03:08 AM
  1. #1
    Registered User
    Join Date
    04-18-2013
    Location
    Mississauga, Ontario Canada
    MS-Off Ver
    Excel 2010
    Posts
    2

    Talking VBA code to copy cell values to the next blank row in another sheet (all the same row)

    I don't really know how to write VBA codes. I just look for them in the forums such as this then apply them.
    I have searched for the solution to my problem but couldn't find the specific answer to my issue.
    I created a macro to copy cell values from an input sheet for a purchase order (PO) to paste to a PO log sheet. I tweaked the code so that it copies to the next blank row in the log sheet. however, some cells in the input sheet may not always be filled by the user. So the issue is that, when a cell value finally exists for a particular cell, instead of it pasting in the same row as the other records for that same PO., it gets pasted on the next blank row for that field column.
    For example:
    What it should do:
    Entry 1
    Input sheet: Copy B2 = May 1, C2 = TEST, D2 = blank, B3 = 100
    Log sheet: Paste B2 = May 1, C2 = TEST, E2 = blank and G2 = 100
    Entry 2
    IInput sheet: Copy B2 = May 3, C2 = TEST 1, D2 = 200, B3 = 50
    Log sheet: Paste B3 = May 3, C3 = TEST 1, E3 = 200 and G3 = 50

    What is happening for entry 2:
    IInput sheet: Copy B2 = May 3, C2 = TEST 1, D2 = 200, B3 = 50
    Log sheet: Paste B3 = May 3, C3 = TEST 1,E2 = 200 and G3 = 50

    YOUR HELP IS MUCH APPRECIATED.

    Here is the code (I had to delete some lines because it was too long):



    Sub CopytoLog()
    '
    ' CopytoLog Macro
    ' Copy from data input sheet to PO log
    '
    ' Keyboard Shortcut: Ctrl+l
    '
        Sheets("PO data input sheet").Select
        Range("D5").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("PO log").Select
        Range("B" & ActiveSheet.Rows.Count). _
    End(xlUp).Offset(1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("PO data input sheet").Select
        Range("B6").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("PO log").Select
        Range("C" & ActiveSheet.Rows.Count). _
    End(xlUp).Offset(1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("PO data input sheet").Select
        Range("D6").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("PO log").Select
        Range("D" & ActiveSheet.Rows.Count). _
    End(xlUp).Offset(1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("PO data input sheet").Select
        Range("B7").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("PO log").Select
        Range("E" & ActiveSheet.Rows.Count). _
    End(xlUp).Offset(1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("PO data input sheet").Select
        Range("D7").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("PO log").Select
        Range("F" & ActiveSheet.Rows.Count). _
    End(xlUp).Offset(1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("PO data input sheet").Select
        Range("B8").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("PO log").Select
        Range("G" & ActiveSheet.Rows.Count). _
    End(xlUp).Offset(1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("PO data input sheet").Select
        Range("D8").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("PO log").Select
        Range("H" & ActiveSheet.Rows.Count). _
    End(xlUp).Offset(1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("PO data input sheet").Select
        Range("B10").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("PO log").Select
        Range("I" & ActiveSheet.Rows.Count). _
    End(xlUp).Offset(1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("PO data input sheet").Select
        Range("D10").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("PO log").Select
        Range("J" & ActiveSheet.Rows.Count). _
    End(xlUp).Offset(1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            Sheets("PO data input sheet").Select
        ActiveWindow.SmallScroll Down:=12
        Range("B15").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("PO log").Select
        Range("U" & ActiveSheet.Rows.Count). _
    End(xlUp).Offset(1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("PO data input sheet").Select
        Range("D15").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("PO log").Select
        Range("V" & ActiveSheet.Rows.Count). _
    End(xlUp).Offset(1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("PO data input sheet").Select
        Range("F15").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("PO log").Select
        Range("W" & ActiveSheet.Rows.Count). _
    End(xlUp).Offset(1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("PO data input sheet").Select
        Range("H15").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("PO log").Select
        Range("X" & ActiveSheet.Rows.Count). _
    End(xlUp).Offset(1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("PO data input sheet").Select
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 1
        Range("B16").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("PO log").Select
        Range("Y" & ActiveSheet.Rows.Count). _
    End(xlUp).Offset(1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("PO data input sheet").Select
        Range("D16").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("PO log").Select
        Range("Z" & ActiveSheet.Rows.Count). _
    End(xlUp).Offset(1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("PO data input sheet").Select
        Range("F16").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("PO log").Select
        Range("AA" & ActiveSheet.Rows.Count). _
    End(xlUp).Offset(1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("PO data input sheet").Select
        Range("H16").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("PO log").Select
        Range("AB" & ActiveSheet.Rows.Count). _
    End(xlUp).Offset(1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
        Sheets("PO data input sheet").Select
        ActiveWindow.ScrollColumn = 3
        ActiveWindow.ScrollColumn = 2
        ActiveWindow.ScrollColumn = 1
        Range("A21").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("PO log").Select
        ActiveWindow.SmallScroll ToRight:=11
        Range("AC" & ActiveSheet.Rows.Count). _
    End(xlUp).Offset(1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            Sheets("PO data input sheet").Select
        Range("L22").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("PO log").Select
        Range("AU" & ActiveSheet.Rows.Count). _
    End(xlUp).Offset(1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
            ActiveWindow.LargeScroll ToRight:=-2
        ActiveWindow.SmallScroll Down:=15
        Sheets("PO data input sheet").Select
        ActiveWindow.SmallScroll Down:=12
        ActiveWindow.LargeScroll ToRight:=-1
        Range("A32").Select
        Application.CutCopyMode = False
        Selection.Copy
        Sheets("PO log").Select
        ActiveWindow.SmallScroll Down:=-15
        ActiveWindow.LargeScroll ToRight:=5
        Range("DP" & ActiveSheet.Rows.Count). _
    End(xlUp).Offset(1).Select
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
            :=False, Transpose:=False
    End Sub
    Last edited by rarias; 05-16-2013 at 05:07 PM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1