Move Data from one sheet to the other

excelnoobhere

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

Excel Facts

Test for Multiple Conditions in IF?
Use AND(test, test, test, test) or OR(test, test, test, ...) as the logical_test argument of IF.
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.
 
Upvote 0
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.
 
Upvote 0
It won't let you create a sheet in the same workbook with the same name.

Regards, JLG
 
Last edited:
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
I am sorry, but I have no idea what you are trying to do, so I will just drop off this thread.
Regards, JLG
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top