Move Data from one sheet to the other

excelnoobhere

Board Regular
Joined
Mar 11, 2019
Messages
52
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
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

JLGWhiz

Well-known Member
Joined
Feb 7, 2012
Messages
11,789
Office Version
2013
Platform
Windows
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

Board Regular
Joined
Mar 11, 2019
Messages
52
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

Well-known Member
Joined
Feb 7, 2012
Messages
11,789
Office Version
2013
Platform
Windows
It won't let you create a sheet in the same workbook with the same name.

Regards, JLG
 
Last edited:

excelnoobhere

Board Regular
Joined
Mar 11, 2019
Messages
52
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

Well-known Member
Joined
Feb 7, 2012
Messages
11,789
Office Version
2013
Platform
Windows
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?")
    [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

Board Regular
Joined
Mar 11, 2019
Messages
52
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?")
    [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

Well-known Member
Joined
Feb 7, 2012
Messages
11,789
Office Version
2013
Platform
Windows
I am sorry, but I have no idea what you are trying to do, so I will just drop off this thread.
Regards, JLG
 

Watch MrExcel Video

Forum statistics

Threads
1,096,141
Messages
5,448,571
Members
405,520
Latest member
youzukk

This Week's Hot Topics

Top