Loading
Ozgrid Excel Help & Best Practices Forums

Excel Training / Excel Dashboards Reports



Results 1 to 5 of 5

Thread: Copy/Special Paste Data Within A Range To Next Empty Cell Then Mark the Copied Area

  1. #1
    Join Date
    29th August 2008
    Posts
    86

    Copy/Special Paste Data Within A Range To Next Empty Cell Then Mark the Copied Area

    First off this is my first post; sorry if there are any problems with it. I will fix promptly if there are. I've gained a ton of information from this forum already so thank you to everyone.

    I'm working with MS Excel 2003 and the workbook will be shared and protected. I've also enclosed a sample of it. This workbook will be used and constantly updated 24/7 with new data.

    Short Story: I'm trying to find a macro that will copy data from the areas of B120:E179 and I120:K179 for example (linked to another worksheet within the workbook) and special paste (Values Only and skipping blank cells) it to the next available open cell up top where basic data entry will be taking place B10:E29 and I10:K29. I need it to only copy/paste the rows with data (skipping all cells/rows with no data) and once it is finished coping I will need it to place an "X" in column M next to the row that it copied data from. I would also need it to reference the data in each row from B to E and if there is an entry say on B14 to E14 that matches it but if I10 to E29 are blank then paste that information on row 14. If it does not match or if those columns are full then paste on next available line.

    I hope I'm making sense here. This is for a vehicle tracking log between checkpoints. Each driver and info will be listed on each row. Columns B through E will contain information for each driver: name, badge, #passengers, and vehicle #. The log lists location, time, and destination for outgoing travelers in columns F to H. Incoming info is listed on Columns I to K. I'm trying to find a macro or some way to carry over the driver info and times from one checkpoint to another and fill it in properly on the log while at the same time not corrupting the conditional formats and that. Since this log will be constantly updating and information being added, I set up the referenced data in B120 and below that if an X is placed in column M then that data will disappear preventing the macro from reading it and duplicating it over and over on the log.

    I understand this is a lot and possibly confusing so I've enclosed a sample of the log and you can see what I'm trying to achieve. If I left out any crucial information or if you need to know more just ask and I will reply with what you need. I did several searches and found some examples however they did not fully answer or help me out. Thank you to anyone willing to give me a hand on this one

    Edit: I am adding this to hopefully make it easier to explain what I'm trying to do.

    Ranges:
    B10:E29 and I10:K29
    B44:E63 and I44:K63
    B78:B97 and I78:K97 reference the incoming travelers on the current Sheet2

    B120:E179 and I120:K179 reference the outgoing travelers from Sheet1
    B182:E241 and I182:K241 reference the outgoing travelers from Sheet3

    Order of Events:

    1) Check B120:E179 and B182:E241 for any data. (Called B120 & Below)

    If there is data

    2) Reference each row of data individually with the data from each row from B10:E29 and B44:E63 and B78:E97 (Called B10:E97)

    If a Row of data (from B120:E179 and B182:E241) is the same data that already exists within B10:E29 and B44:E63 and B78:E97 and columns I, J, K are empty for that same row then

    3a) Copy that same row of data from columns I, J, K (B120 & Below) and Special paste (Values Only) to columns I, J, K in the B10:E97 Rows.

    If the Row of data matches but the I, J, K columns from B10:E97 are not empty then

    3b) Copy the full row of data from columns B through E and I through K and Special paste (Values Only) to the next empty Row in the range B10:K97. Special Pasting Columns B to E and I to K (B120 & Below) to Columns B to E and I to K (B10:K97) and so on.

    For each Row of data that does not match up with data from the B10:E97 range.

    4) Copy the row of data from columns B to E and I to K (B120 & below) and Special Paste (Values Only) to the next empty Row in the range of B10:K97. The data from one Column (B120 & Below) will be special pasted to the same column (B10:K97) within the Row.

    Once all the data has been Copied and Special Pasted or after completing each row

    5) Place an X in Column M for each row that had data copied and special pasted (B120 & Below). This X in Column M will make that data disappear.

    Now the Area of B120 & Below should be all copied and pasted to above log and should now be empty with Xs in column M from all the rows that previously had data.

    It is important that the empty rows of B120 and Below be ignored through out this process as they may receive data later on to have this macro run again on them.

    Also: that the Special Pasting pastes to the next available empty row, so that there are no gaps in the log. Unless of course, it was able to paste into a row of identical data as mentioned in numbers 2 and 3.

    If anyone has an Easier/More effect way to update the other sheets outgoing traveler info into this sheets incoming info Im open to any ideas. This workbook will be updating with new travelers throughout the day. Again thanks to all that help.
    Last edited by Madhart; August 29th, 2008 at 18:48.

    Excel Video Tutorials / Excel Dashboards Reports


  2. #2
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Copy/special Paste Data Within A Range To Next Empty Cell Then Mark Copied Area

    Hi Madhart,

    Welcome to board!!

    Your attachment is missing!

  3. #3
    Join Date
    29th August 2008
    Posts
    86

    Re: Copy/Paste Data Within A Range To Next Empty Cell Then Mark Copied Area

    Sorry, I thought I posted the Attachment but It didn't take. It should be attached below. Thanks
    Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros. Attached Files. REMINDER! OzGrid accepts no responsibility for ANY adverse effects as a result from downloading attached files. ALWAYS run an up-to-date virus scan and disable macros.

    Excel Video Tutorials / Excel Dashboards Reports


  4. #4
    Join Date
    18th November 2004
    Location
    God's Own Country
    Posts
    4,678

    Re: Copy/Special Paste Data Within A Range To Next Empty Cell Then Mark the Copied Area

    Hi,

    Try,

    VB:
    Option Explicit 
    Sub kTest() 
        Dim a, b, x, leRow  As Long, n  As Long 
        Dim i  As Long, j   As Long, c  As Long, Flg As Boolean, txt As String 
        Dim sRow1   As Long, eRow1  As Long, sRow2  As Long 
         
        Const cFmla As String = "=RC[-51]&"";""&RC[-50]&"";""&RC[-49]&"";""&RC[-48]" 
        Application.ScreenUpdating = 0 
        Columns(53).Insert 
        Range("ba10:ba241").FormulaR1C1 = cFmla 
        a = Range("ba10:ba97") 
        b = Range("ba120:ba241") 
         
        sRow1 = 10: eRow1 = 97: sRow2 = 120 
        Flg = False 
        For n = sRow1 To eRow1 
            Select Case n 
            Case 30 To 43, 64 To 77 
            Case Else 
                If IsEmpty(Cells(n, 2)) Then 
                    leRow = n 
                    Exit For 
                End If 
            End Select 
        Next 
        For i = 1 To UBound(b, 1) 
            If Not i > 60 And i < 63 Then 
                If Len(Cells(i + sRow2 - 1, 2)) > 0 Then 
                    x = Application.Match(b(i, 1), a, 0) 
                    If Not IsError(x) Then 
                        For c = 9 To 11 
                            If IsEmpty(Cells(x + sRow1 - 1, c)) Then Flg = True: Exit For 
                        Next 
                        If Flg Then 
                            Range(Cells(x + sRow1 - 1, 9), Cells(x + sRow1 - 1, 11)) = _ 
                            Range(Cells(i + sRow2 - 1, 9), Cells(i + sRow2 - 1, 11)).Value 
                            txt = txt & "," & "M" & i + sRow2 - 1: Flg = False 
                        Else 
                            If leRow = 0 Then 
                                MsgBox "There is no empty row to paste the data", vbInformation 
                                Goto Xit 
                            End If 
                            Range(Cells(leRow, 2), Cells(leRow, 5)) = _ 
                            Range(Cells(i + sRow2 - 1, 2), Cells(i + sRow2 - 1, 5)).Value 
                            Range(Cells(leRow, 9), Cells(leRow, 11)) = _ 
                            Range(Cells(i + sRow2 - 1, 9), Cells(i + sRow2 - 1, 11)).Value 
                            leRow = leRow + 1 
                            txt = txt & "," & "M" & i + sRow2 - 1 
                        End If 
                    Else 
                        If leRow = 0 Then 
                            MsgBox "There is no empty row to paste the data", vbInformation 
                            Goto Xit 
                        End If 
                        Range(Cells(leRow, 2), Cells(leRow, 5)) = _ 
                        Range(Cells(i + sRow2 - 1, 2), Cells(i + sRow2 - 1, 5)).Value 
                        Range(Cells(leRow, 9), Cells(leRow, 11)) = _ 
                        Range(Cells(i + sRow2 - 1, 9), Cells(i + sRow2 - 1, 11)).Value 
                        leRow = leRow + 1 
                        txt = txt & "," & "M" & i + sRow2 - 1 
                    End If 
                End If 
            End If 
        Next 
        If Len(txt) > 1 Then 
            txt = Mid$(txt, 2) 
            Range(txt).Value = "X" 
        End If 
    Xit: 
        Columns(53).Delete 
        Application.ScreenUpdating = 1 
    End Sub 
    
    
    HTH

  5. #5
    Join Date
    29th August 2008
    Posts
    86

    Re: Copy/Special Paste Data Within A Range To Next Empty Cell Then Mark the Copied Area

    WOW! You Rock like KISS!!! so far it works great. I will test it out for a few days and post if there are any problems or issues. I did start a new thread regarding merging 2 rows. http://www.ozgrid.com/forum/showthread.php?t=104226 I don't know if that would work for this or need to be done seperately. Either way Thank You!

    Excel Video Tutorials / Excel Dashboards Reports


Thread Information

Users Browsing this Thread

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

Possible Answers

  1. Copy And Paste To The Next Empty Cell
    By dkidd07 in forum Excel General
    Replies: 1
    Last Post: January 30th, 2008, 10:56
  2. Replies: 3
    Last Post: January 29th, 2007, 15:54
  3. find first empty cell in range and copy data
    By rachitm in forum Excel General
    Replies: 3
    Last Post: January 28th, 2007, 19:26
  4. Copy and paste special shifting cell down
    By bbromley in forum Excel General
    Replies: 5
    Last Post: October 31st, 2005, 02:54
  5. Macros that paste copied data from the next empty cell
    By Bharathan in forum Excel General
    Replies: 2
    Last Post: July 8th, 2003, 14:36

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
  •  
porno