VBA CODE TO COPY FROM ONE WORKBOOK TO ANOTHER WORKBOOK

[Craig_Moore]

Hi i am trying to copy a range of cells G4:G19 from one workbook sheet to another but when pasting in to the second workbook i need the cells to paste to C5 but every time the code runs it needs detect the next free empty colum

i have manage to source the below code but this is used on one work book and i have no idea on what i need to change to make it copy to the new sheet

any help is greatly received

thanks

Craig


Sub CopyPaste()

Application.ScreenUpdating = False

Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet = Worksheets("OVERVIEW")

Set pasteSheet = Worksheets("master downtime sheet")

copySheet.Range("G4:G19").Copy

pasteSheet.Cells(5, Columns.Count).End(xlToLeft).Offset(0, 1).PasteSpecial xlPasteValues

Application.CutCopyMode = False

Application.ScreenUpdating = True





End Sub

 

[mumps]

What are the full names of the two workbooks including the extension (xlsx, xlsm)? Your screen shot shows the current paste sheet is "April 20". Your code shows the paste sheet as "master downtime sheet" which I don't see in your screen shot. I think that there is some confusion as to sheet names versus workbook names. Please clarify which workbook contains the "OVERVIEW" sheet and which workbook contains the paste sheet. How do you determine into which month to paste the data?

 

[Craig_Moore]

the copy from workbook is MASTER PNB OEE SHEET.xlsm and the work sheet is called OVERVIEW
the paste to workbook is called MASTER DOWN TIME SHEET PNB.xlsm and the sheet is called YTD DOWNTIME

im after all the data that is copied to the YTD down time sheet and i will use a pivot table to pull information out for my meetings \ reports

 

[mumps]

Try:

Sub CopyPaste()
    Application.ScreenUpdating = False
    Dim copySheet As Worksheet, pasteSheet As Worksheet, lCol As Long
    Set copySheet = ThisWorkbook.Sheets("OVERVIEW")
    Set pasteSheet = Workbooks("Master Down Time Sheet PNB.xlsm").Sheets("TYD Downtime")
    lCol = pasteSheet.Cells(5, pasteSheet.Columns.Count).End(xlToLeft).Column + 1
    copySheet.Range("G4:G19").Copy
    pasteSheet.Cells(5, lCol).PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Place the macro in the MASTER PNB OEE SHEET.xlsm workbook and make sure that both workbooks are open.

 

[Craig_Moore]

Thank you for the help, This has worked perfectly for what i need.

Craig

 

[mumps]

You are very welcome.