ell_
03-14-2018, 05:28 PM
Hi, all
I have these 2 Excel files, one with raw data in sheet All_DTE and the file path: Z:\RSE-ROC-Public\RSE-OPM\COE Monthly Reporting\RSE COE Report\MID File.xslx & another one is the workbook where this code will run from, called Report, in DTE_Raw sheet.
In this raw file, there are a lot of data but I only want data from specific columns to be copied and pasted to Report file, in DTE_Raw sheet and the sequences of columns are as below:
column N to column A
column AE to column B
column AM to column C
column AN to column D
column BE to column E
column CP to column F
column CV to column G
The data that will be pasted may overwrite the previous data in the DTE_Raw sheet.
Here's the code that I have created but not working.
Option Explicit
Sub GenerateBook()
On Error GoTo OpenWorkBook:
Dim BookName As String
BookName = "RSE MID"
Workbooks("RSE MID Input File 2018").Activate
GenerateReport2
Exit Sub
OpenWorkBook:
If Err.Number = 9 Then
'change file path here
Workbooks.Open Filename:="Z:\RSE-ROC-Public\RSE-OPM\COE Monthly Reporting\RSE COE Report\RSE MID Input File 2018.xlsx"
Resume
End If
GenerateReport2
End Sub
Sub GenerateReport2()
Application.ScreenUpdating = False
Workbooks("RSE MID Input File 2018").Sheets("Raw data (DTE)").Select
Range("N1:N2000,AE1:AE2000,AM1:AM2000,AN1:AN2000,BE1:BE2000,CP1:CP2000,CV1:CV2000").Select
Selection.Copy
Sheets("DTE_Raw").Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Can anyone assist me in this? I have attached Excel files for reference. Thanks in advance for your help!
I have these 2 Excel files, one with raw data in sheet All_DTE and the file path: Z:\RSE-ROC-Public\RSE-OPM\COE Monthly Reporting\RSE COE Report\MID File.xslx & another one is the workbook where this code will run from, called Report, in DTE_Raw sheet.
In this raw file, there are a lot of data but I only want data from specific columns to be copied and pasted to Report file, in DTE_Raw sheet and the sequences of columns are as below:
column N to column A
column AE to column B
column AM to column C
column AN to column D
column BE to column E
column CP to column F
column CV to column G
The data that will be pasted may overwrite the previous data in the DTE_Raw sheet.
Here's the code that I have created but not working.
Option Explicit
Sub GenerateBook()
On Error GoTo OpenWorkBook:
Dim BookName As String
BookName = "RSE MID"
Workbooks("RSE MID Input File 2018").Activate
GenerateReport2
Exit Sub
OpenWorkBook:
If Err.Number = 9 Then
'change file path here
Workbooks.Open Filename:="Z:\RSE-ROC-Public\RSE-OPM\COE Monthly Reporting\RSE COE Report\RSE MID Input File 2018.xlsx"
Resume
End If
GenerateReport2
End Sub
Sub GenerateReport2()
Application.ScreenUpdating = False
Workbooks("RSE MID Input File 2018").Sheets("Raw data (DTE)").Select
Range("N1:N2000,AE1:AE2000,AM1:AM2000,AN1:AN2000,BE1:BE2000,CP1:CP2000,CV1:CV2000").Select
Selection.Copy
Sheets("DTE_Raw").Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub
Can anyone assist me in this? I have attached Excel files for reference. Thanks in advance for your help!