Craig_Moore
Board Regular
- Joined
- Dec 12, 2018
- Messages
- 64
- Office Version
- 2019
- Platform
- Windows
Hi i am currently using the below code to copy select ranges from one workbook to paste into another workbook, but when data is being updated by different departments and resent im getting duplicate data and i am having to go in to the document DOWN TIME SHEET PNB 2020 .xlsx to clear the duplicate data.
so i am after some modification to the below code that will check if there is already a column with the same date that is being copied over if there is, then a popup box asks do you want to update the existing data if yes the data over rights that column that has the existing data if no the data is copied to the next free column.
Thanks
Craig
so i am after some modification to the below code that will check if there is already a column with the same date that is being copied over if there is, then a popup box asks do you want to update the existing data if yes the data over rights that column that has the existing data if no the data is copied to the next free column.
Thanks
Craig
VBA Code:
Private Sub CommandButton2_Click()
Workbooks.Open "\\duerrsfile\SS\01 PNB Production\OEE SHEETS\02. DOWN TIME SHEET\DOWN TIME SHEET PNB 2020 .xlsx"
Application.ScreenUpdating = False
Dim copySheet As Worksheet, pasteSheet As Worksheet, lCol As Long
' LINE 3 COPY PASE CODE,
Set copySheet = ThisWorkbook.Sheets("OVERVIEW")
Set pasteSheet = Workbooks("DOWN TIME SHEET PNB 2020 .xlsx").Sheets("YTD DOWNTIME")
lCol = pasteSheet.Cells(4, pasteSheet.Columns.Count).End(xlToLeft).Column + 1 ' CHANGE THE 5 UP OR DOWN TO SELECT WHICH ROW TO START THE PASTE FROM
copySheet.Range("LINE_3_DT").Copy ' CHANGE THIS RANGE IF THE RANGE NEEDS TO BE MADE BIGGER OR SMALLER
pasteSheet.Cells(4, lCol).PasteSpecial xlPasteValues ' THE 5 VALUE MUST MATCH THE VALUE 2 LINES OF CODE UP
Application.CutCopyMode = False
Application.ScreenUpdating = True
Close Workbook
ActiveWorkbook.Close Savechanges:=True