VBA Copy and Paste between workbooks

%3CLINGO-SUB%20id%3D%22lingo-sub-1933740%22%20slang%3D%22en-US%22%3EVBA%20Copy%20and%20Paste%20between%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1933740%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20am%20trying%20to%20write%20a%20VBA%20to%20embed%20into%20a%20source%20workbook%20(this%20gets%20renamed%20for%20each%20job)%2C%20which%20copies%20the%20same%20row%20each%20time%20(Row%205%20on%20the%20Data%20Worksheet)%20and%20adds%20it%20into%20the%20next%20available%20row%20on%20the%20Machine%20Schedule%20workbook.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESo%20far%20this%20code%20works%20the%20first%20time%20that%20you%20run%20it.%20But%20when%20you%20run%20it%20a%20second%20time%2C%20it%20re-writes%20row%205%2C%20and%20adds%20other%20information%20on%20the%20next%20available%20row%20down%2C%20pulled%20from%20different%20sheets%20on%20the%20source%20workbook.%20They're%20not%20at%20all%20mentioned%20in%20the%20code%2C%20so%20I%20can't%20understand%20why%20it%20would%20do%20this.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22MegWH_0-1606389837540.png%22%20style%3D%22width%3A%20579px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F236253iBA05B6063D5B7DE2%2Fimage-dimensions%2F579x420%3Fv%3D1.0%22%20width%3D%22579%22%20height%3D%22420%22%20role%3D%22button%22%20title%3D%22MegWH_0-1606389837540.png%22%20alt%3D%22MegWH_0-1606389837540.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3EAny%20help%20would%20be%20greatly%20appreciated.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1933740%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1934145%22%20slang%3D%22de-DE%22%3ESubject%3A%20VBA%20Copy%20and%20Paste%20between%20workbooks%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1934145%22%20slang%3D%22de-DE%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F882447%22%20target%3D%22_blank%22%3E%40MegWH%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHere%20something%20on%20the%20almost%20untested%20(but%20should%20work)%3A%3C%2FP%3E%3CP%3ESub%20copy()%3C%2FP%3E%3CP%3EWith%20Sheets(%221%22)%3C%2FP%3E%3CP%3E.Range(.Cells(6%2C%202)%2C%20.Cells(Rows.Count%2C%205)%3C%2FP%3E%3CP%3E.End(xlUp)).copy%20End%20With%20Sheets(%222%22)%3C%2FP%3E%3CP%3E.Activate%20lastrow%20%3D%20Cells(Rows.Count%2C%202)%3C%2FP%3E%3CP%3E.End(xlUp).Row%20Range(%22B%22%20%26amp%3B%20lastrow%20%2B%201)%3C%2FP%3E%3CP%3E.Select%20Selection.PasteSpecial%20xlPasteValues%20%3D%20xlPasteValues%3C%2FP%3E%3CP%3EEnd%20Sub%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20can%20probably%20find%20what%20you're%20looking%20for%20here%3A%3C%2FP%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Fwww.rondebruin.nl%2Fwin%2Fs9%2Fwin005.htm%22%20target%3D%22_blank%22%20rel%3D%22nofollow%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fwww.rondebruin.nl%2Fwin%2Fs9%2Fwin005.htm%3C%2FA%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20would%20be%20happy%20to%20know%20if%20I%20could%20help.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENikolino%3C%2FP%3E%3CP%3EI%20know%20I%20don't%20know%20anything%20(Socrates)%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E*%20Kindly%20Mark%20and%20Vote%20any%20reply%20if%20it%20helps%20please%2C%20as%20it%20will%20be%20beneficial%20to%20more%20community%20members%20reading%20here.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
New Contributor

Hi,

 

I am trying to write a VBA to embed into a source workbook (this gets renamed for each job), which copies the same row each time (Row 5 on the Data Worksheet) and adds it into the next available row on the Machine Schedule workbook.

 

So far this code works the first time that you run it. But when you run it a second time, it re-writes row 5, and adds other information on the next available row down, pulled from different sheets on the source workbook. They're not at all mentioned in the code, so I can't understand why it would do this.

 

MegWH_0-1606389837540.png

Any help would be greatly appreciated.

 

3 Replies

@MegWH 

 

Here something on the fast untested (but should work):

Sub copy()

With Sheets("1")

.Range(.Cells(6, 2), .Cells(Rows.Count, 5)

.End(xlUp)).copy End With Sheets("2")

.Activate lastrow = Cells(Rows.Count, 2)

.End(xlUp).Row Range("B" & lastrow + 1)

.Select Selection.PasteSpecial xlPasteValues = xlPasteValues

End Sub

 

You can probably find what you're looking for here:

https://www.rondebruin.nl/win/s9/win005.htm

 

I would be happy to know if I could help.

 

Nikolino

I know I don't know anything (Socrates)

 

* Kindly Mark and Vote any reply if it helps please, as it will be beneficial to more Community members reading here.

 

 

@Nikolino 

 

Thank you so much for your response. I have just managed to get it working by specifying the PasteSpecial as PasteValues, as you have mentioned below. It turns out that it was quite an easy fix in the end! 

Thanks again.

@MegWH 

 

You are welcome

 

It has give me joy to be able to help you

 

Wish you a nice day.

 

Nikolino

I know I don't know anything (Socrates)