VBA CODE TO COPY FROM ONE WORKBOOK TO ANOTHER WORKBOOK

Craig_Moore

New Member
Joined
Dec 12, 2018
Messages
23
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
 

Attachments

  • paste location.png
    paste location.png
    100.3 KB · Views: 7
  • copy location.png
    copy location.png
    104.3 KB · Views: 7

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result

mumps

Well-known Member
Joined
Apr 11, 2012
Messages
10,311
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

New Member
Joined
Dec 12, 2018
Messages
23
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

Well-known Member
Joined
Apr 11, 2012
Messages
10,311
Try:
VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,130,179
Messages
5,640,625
Members
417,159
Latest member
Mayozero

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
Top