Move Data from one sheet to the other

[excelnoobhere]

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.

'--------------------------------------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

 

[JLGWhiz]

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.

 

[excelnoobhere]

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.

 

[JLGWhiz]

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

Regards, JLG

 

[excelnoobhere]

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

 

[JLGWhiz]

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

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?")
    [COLOR=#b22222]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:
[/COLOR]    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

 

[excelnoobhere]

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

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?")
    [COLOR=#b22222]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:
[/COLOR]    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

 

[JLGWhiz]

I am sorry, but I have no idea what you are trying to do, so I will just drop off this thread.
Regards, JLG