Results 1 to 8 of 8

Thread: Move Data from one sheet to the other

  1. #1
    New Member
    Join Date
    Mar 2019
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Move Data from one sheet to the other

    I have the following code that copies the rows from one my source sheet to the other and gets rid of empty rows that don't have anything in column C.
    I want to be able to run this so that if my target sheet has data to place it at the last empty row.

    if possible, I also want it to check every row and that if row and all columns match then it wont copy it over. basically copies only updates from that source sheet.



    Code:
    '--------------------------------------Button1-------------------------------------------------Sub button1()
    
    
        Dim sourceName As String
        Dim targetName As String
        
        Sheets("Master").Activate
        
        
       ' sourceName = Cells(13, "F").Value
         sourceName = Cells(12, "F").Value
        'sourceName = InputBox("What is the Project Number the we are pulling Data from?")
        targetName = InputBox("What is the desired name for the New Tab that will be generated?")
        
        
    
    
        Sheets.Add.Name = targetName
        Sheets(sourceName).Activate
            Sheets(sourceName).Columns(1).Copy Destination:=Sheets(targetName).Columns(1)
            Sheets(sourceName).Columns(3).Copy Destination:=Sheets(targetName).Columns(2)
            Sheets(sourceName).Columns(4).Copy Destination:=Sheets(targetName).Columns(3)
            Sheets(sourceName).Columns(5).Copy Destination:=Sheets(targetName).Columns(4)
            Sheets(sourceName).Columns(6).Copy Destination:=Sheets(targetName).Columns(5)
            Sheets(sourceName).Columns(7).Copy Destination:=Sheets(targetName).Columns(6)
            
            
            Sheets(targetName).Activate
        On Error Resume Next
        Columns("C").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    
    
    End Sub

  2. #2
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,899
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Move Data from one sheet to the other

    Since you are creating a new sheet for each copy action, The next empty row will always be row 1. There should be no data in your target sheet at runtime.
    What do you want the cells in the rows to match against? Since you are creating a new sheet for the data to be entered on, there would be no duplicates between the sheets. This needs explanation.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  3. #3
    New Member
    Join Date
    Mar 2019
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move Data from one sheet to the other

    Quote Originally Posted by JLGWhiz View Post
    Since you are creating a new sheet for each copy action, The next empty row will always be row 1. There should be no data in your target sheet at runtime.
    What do you want the cells in the rows to match against? Since you are creating a new sheet for the data to be entered on, there would be no duplicates between the sheets. This needs explanation.
    I just realized that
    It would need to check if the sheet is created with that title, if it is then just add to the data in the existing sheet if not, created a new one.

  4. #4
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,899
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Move Data from one sheet to the other

    It won't let you create a sheet in the same workbook with the same name.

    Regards, JLG
    Last edited by JLGWhiz; May 16th, 2019 at 05:02 PM.
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  5. #5
    New Member
    Join Date
    Mar 2019
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move Data from one sheet to the other

    Quote Originally Posted by JLGWhiz View Post
    It won't let you create a sheet in the same workbook with the same name.

    Regards, JLG

    well it will check for the same name sheet, if the same name exist then just update the existing sheet. If the name doesn't exist then create a new sheet

  6. #6
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,899
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Move Data from one sheet to the other

    You can try this modification to your code and see if that is what you want.

    Code:
    Dim sourceName As String
        Dim targetName As String
        Sheets("Master").Activate
       ' sourceName = Cells(13, "F").Value
         sourceName = Cells(12, "F").Value
        'sourceName = InputBox("What is the Project Number the we are pulling Data from?")
        targetName = InputBox("What is the desired name for the New Tab that will be generated?")
        On Error Resume Next
            If Sheets(targetName) Is Nothing Then
                If Err.Number = 0 Then GoTo SKIP:
                    On Error GoTo 0
                    Err.Clear
                    Sheets.Add.Name = targetName
            End If
    SKIP:
        Sheets(sourceName).Activate
        Application.DisplayAlerts = False
            Sheets(sourceName).Columns(1).Copy Destination:=Sheets(targetName).Columns(1)
            Sheets(sourceName).Columns(3).Copy Destination:=Sheets(targetName).Columns(2)
            Sheets(sourceName).Columns(4).Copy Destination:=Sheets(targetName).Columns(3)
            Sheets(sourceName).Columns(5).Copy Destination:=Sheets(targetName).Columns(4)
            Sheets(sourceName).Columns(6).Copy Destination:=Sheets(targetName).Columns(5)
            Sheets(sourceName).Columns(7).Copy Destination:=Sheets(targetName).Columns(6)
        Application.DisplayAlerts = True
            Sheets(targetName).Activate
        On Error Resume Next
        Columns("C").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End Sub
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

  7. #7
    New Member
    Join Date
    Mar 2019
    Posts
    50
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Move Data from one sheet to the other

    Quote Originally Posted by JLGWhiz View Post
    You can try this modification to your code and see if that is what you want.

    Code:
    Dim sourceName As String
        Dim targetName As String
        Sheets("Master").Activate
       ' sourceName = Cells(13, "F").Value
         sourceName = Cells(12, "F").Value
        'sourceName = InputBox("What is the Project Number the we are pulling Data from?")
        targetName = InputBox("What is the desired name for the New Tab that will be generated?")
        On Error Resume Next
            If Sheets(targetName) Is Nothing Then
                If Err.Number = 0 Then GoTo SKIP:
                    On Error GoTo 0
                    Err.Clear
                    Sheets.Add.Name = targetName
            End If
    SKIP:
        Sheets(sourceName).Activate
        Application.DisplayAlerts = False
            Sheets(sourceName).Columns(1).Copy Destination:=Sheets(targetName).Columns(1)
            Sheets(sourceName).Columns(3).Copy Destination:=Sheets(targetName).Columns(2)
            Sheets(sourceName).Columns(4).Copy Destination:=Sheets(targetName).Columns(3)
            Sheets(sourceName).Columns(5).Copy Destination:=Sheets(targetName).Columns(4)
            Sheets(sourceName).Columns(6).Copy Destination:=Sheets(targetName).Columns(5)
            Sheets(sourceName).Columns(7).Copy Destination:=Sheets(targetName).Columns(6)
        Application.DisplayAlerts = True
            Sheets(targetName).Activate
        On Error Resume Next
        Columns("C").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
    End Sub

    This doesn't check the old sheet and updates only new information by matching column A,B, and C

  8. #8
    Board Regular
    Join Date
    Feb 2012
    Location
    Florida, USA
    Posts
    10,899
    Post Thanks / Like
    Mentioned
    16 Post(s)
    Tagged
    2 Thread(s)

    Default Re: Move Data from one sheet to the other

    I am sorry, but I have no idea what you are trying to do, so I will just drop off this thread.
    Regards, JLG
    Using Windows 10, Excel 2013.
    Any code provided by me should be tested on a copy or a mock-up of your file before applying it to your original file. Some actions generated by VBA code cannot be reversed with the undo facility in Excel. To open the VB editor, press Alt + F11. To run code from the Excel window, press Alt + F8. Please do not attempt to learn everything about VBA in one thread, especially from me. See this link for attaching images: Attachments

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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