VBA : Copy forn another worksheet only the new information.

[Katolux182]

Hi,

I am new into de forum and kind of new into VBA. I have written this code(with some support of youtube videos etc) to copy a specific Sheet from one worksheet to another.

Sub CopytoWorkbook()

Dim x As Workbook, y As Workbook
Dim ws1 As Worksheet, ws2 As Worksheet
Dim LastRow As Long
Dim rngToCopy As Range
Dim NewEnd As Long
Dim NewEnd2 As Long

Set x = Workbooks.Open("C:\Users\agmzj\OneDrive\Escritorio\2023-08-28_Seriennummern_KOPIE.xlsx") 'Direction of database
Set y = Workbooks.Open("C:\Users\agmzj\OneDrive\Escritorio\Control Program.xlsm") 'Direction of program

Set ws1 = x.Sheets("Schlägerübersicht")
Set ws2 = y.Sheets("Master sheet")

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

ws1.Cells.Copy ws2.Cells

x.Close

End Sub

This code works, although I guess you guys have a better way of writting it. My issue is that I would like to copy specific cells from this worksheet and I am not being able to make it work.
I have tried looping it looking for the header, doing

.copy

, or include the rng for the specific cells but I am not being able integrate it into the code instead of

ws1.Cells.Copy ws2.Cells

that is copying the whole sheet/tab. The cells would be E, F ,J ,I ,K ,L.

As I am asking for help, I will like to know if someone can also share some knowledge with me. I will like to only copy the new information that is not already in the "y" location of the program

My idea is to integrate this into a bottom, so once clicked and accepted, information for a Master Sheet can be copied into my "program"

Thank you for any help you guys can give me. I feel like a dinosaur

 

[kevin9999]

Hi @Katolux182 and welcome to the forum.
You might need to explain this a bit more:

only copy the new information that is not already in the "y" location

But in the meantime, try replacing this line:

ws1.Cells.Copy ws2.Cells

with this:

Dim r As Range
Set r = Intersect(ws1.UsedRange.SpecialCells(xlCellTypeConstants), _
Union(ws1.Range("E:F"), ws1.Range("I:L"))).Offset(1)
r.Copy ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1)

and test it on copies of your workbooks.

 

[Katolux182]

Hi @Katolux182 and welcome to the forum.
You might need to explain this a bit more:

But in the meantime, try replacing this line:

ws1.Cells.Copy ws2.Cells

with this:

Dim r As Range
Set r = Intersect(ws1.UsedRange.SpecialCells(xlCellTypeConstants), _
Union(ws1.Range("E:F"), ws1.Range("I:L"))).Offset(1)
r.Copy ws2.Cells(Rows.Count, 1).End(xlUp).Offset(1)

and test it on copies of your workbooks.

Hi @kevin9999

Thank you very much for your answer.

You might need to explain this a bit more:
only copy the new information that is not already in the "y" location

I want to copy the specific information from one data base to the other. I want to do this in a way that I only copy the new information on the datasheet. That way, if I do changes on the data base in my program, I wont loose them everytime I update the data base.

I am creating this program for the sales and production department in a really small company I work in as a side job (I am the only technician in my company).
I want to retrieve certain information for a mother excell book (Cell K,J etc) to my program, so I can edit it depending on the online and physical orders received, process of that order, moment it get sold etc.
The program is going to have diferent tabs that are going to interact with this data base, being able to edit certain cells (date of delivery on the sales tab, moment of assembly on the Production tab etc. I am bulding this with Forms.

I want to be able to bring new information from the mother data base, fixing it to a botton on my program that I will link the code too, without this modifying the already existing information on my data base.
I was thinking to fix the logic on a search on "SerialNumber" and only bring the new information on the cells mentioned before if the SerialNumber is not present in the sheet already, but I am unsure how to it.

I am still learning, reading a lot and looking at several videos, and any help will be amazing.

 

[Katolux182]

It also tells me that this code will not work on multiple select.

r.Copy ws1.Cells(Rows.Count, 1).End(xlUp).Offset(1)

This is also going to sound stupid but with this code I have a doubt

Set r = Intersect(ws1.UsedRange.SpecialCells(xlCellTypeConstants), _
Union(ws1.Range("E:F"), ws1.Range("I:L"))).Offset(1)

I understan that with Range you mark the range of the cells. If the cells are not one after the other. Lets say I want to copy A-C-F-L-M, would that also work this way? I would have to declare the following?

Union(ws1.Range("A:A), ws1.Range("C:C)...

 

[kevin9999]

Hi @Katolux182

I was thinking to fix the logic on a search on "SerialNumber" and only bring the new information on the cells mentioned before if the SerialNumber is not present in the sheet already, but I am unsure how to it.

This is certainly achievable. Please start a new thread and include copies of your 'source' and 'destination' sheets preferably using the XL2BB add in, or alternatively share your file(s) via Google Drive, Dropbox or similar file sharing platform. You can easily disguise any sensitive information.
In the new thread, explain which columns you want copied from your 'mother' excel book worksheet, to which columns in your destination worksheet (at the bottom of the existing data presumably) and under what circumstances - by which I mean, for example, only copy if the "SerielNumber" doesn't already exist on the destination worksheet.

 

[kevin9999]

It also tells me that this code will not work on multiple select.

It works for me on the sheet I created to test the code - another reason why it's best that we see your actual data.

I understan that with Range you mark the range of the cells. If the cells are not one after the other. Lets say I want to copy A-C-F-L-M, would that also work this way? I would have to declare the following?

Yes, you are correct.

 

[Katolux182]

Ok, will open a new thread and see if I can get the help need. Thank you very much.