ExcelBanter

ExcelBanter (https://www.excelbanter.com/)
-   Excel Programming (https://www.excelbanter.com/excel-programming/)
-   -   Excel Merge (https://www.excelbanter.com/excel-programming/424175-excel-merge.html)

kiran
Excel Merge
 
Hi All,
I have 4 csv report file with the following file name as follows :-
Interspec_CH_EOD_Report.csv
CHReport.csv
Interspec_HD_EOD_Report.csv
HDPending.csv

my query is I want all HDPending.csv data to be copied in
Interspec_HD_EOD_Report.csv file at the end of the row of

Interspec_HD_EOD_Report.csv file deleting the heading row from HDPending.csv
like for CHReport.csv also to be copied

to Interspec_CH_EOD_Report.csv file.
TIA

joel
Excel Merge
 
Make copies of all files before trying this code.

Sub Copyfiles()

Folder = "C:\Temp\"

Set HDPending = Workbooks.Open( _
Filename:=Folder & "Interspec_HD_EOD_Report.csv")

Set HDPending_Sht = HDPending.Sheets(1)

HDPending_LastRow = HDPending_Sht.Range("A" & Rows.Count).End(xlUp).Row

Set Interspec_HD = Workbooks.Open( _
Filename:=Folder & "Interspec_HD_EOD_Report.csv")

Set Interspec_HD_Sht = Interspec_HD.Sheets(1)

Interspec_HD_LastRow = Interspec_HD_Sht.Range("A" & Rows.Count).End(xlUp).Row

Interspec_HD_Sht.Rows("2:" & Interspec_HD_LastRow).Copy _
Destination:=HDPending_Sht.Rows(Interspec_HD_LastR ow + 1)

HDPending.Close Savechanges:=False
Interspec_HD.Close Savechanges:=True

Set HDPending = Nothing
Set HDPending_Sht = Nothing
Set Interspec_HD = Nothing
Set Interspec_HD_Sht = Nothing


Set CHReport = Workbooks.Open( _
Filename:=Folder & "CHReport.csv")

Set CHReport_Sht = CHReport.Sheets(1)

CHReport_LastRow = CHReport_Sht.Range("A" & Rows.Count).End(xlUp).Row

Set Interspec_CH = Workbooks.Open( _
Filename:=Folder & "Interspec_CH_EOD_Report.csv")

Set Interspec_CH_Sht = Interspec_CH.Sheets(1)

Interspec_CH_LastRow = Interspec_CH_Sht.Range("A" & Rows.Count).End(xlUp).Row

Interspec_CH_Sht.Rows("2:" & Interspec_CH_LastRow).Copy _
Destination:=CHReport_Sht.Rows(Interspec_CH_LastRo w + 1)

CHReport.Close Savechanges:=False
Interspec_CH.Close Savechanges:=True

End Sub






"kiran" wrote:

Hi All,
I have 4 csv report file with the following file name as follows :-
Interspec_CH_EOD_Report.csv
CHReport.csv
Interspec_HD_EOD_Report.csv
HDPending.csv

my query is I want all HDPending.csv data to be copied in
Interspec_HD_EOD_Report.csv file at the end of the row of

Interspec_HD_EOD_Report.csv file deleting the heading row from HDPending.csv
like for CHReport.csv also to be copied

to Interspec_CH_EOD_Report.csv file.
TIA

kiran
Excel Merge
 
Thanks Joel for your prompt replay but I'm getting object required at
Interspec_HD_Sht.Rows("2:" & Interspec_HD_LastRow).Copy _
Destination:=HDPending_Sht.Rows(Interspec_HD_LastR ow + 1)
line can u pls revert on this.
TIA


"Joel" wrote:

Make copies of all files before trying this code.

Sub Copyfiles()

Folder = "C:\Temp\"

Set HDPending = Workbooks.Open( _
Filename:=Folder & "Interspec_HD_EOD_Report.csv")

Set HDPending_Sht = HDPending.Sheets(1)

HDPending_LastRow = HDPending_Sht.Range("A" & Rows.Count).End(xlUp).Row

Set Interspec_HD = Workbooks.Open( _
Filename:=Folder & "Interspec_HD_EOD_Report.csv")

Set Interspec_HD_Sht = Interspec_HD.Sheets(1)

Interspec_HD_LastRow = Interspec_HD_Sht.Range("A" & Rows.Count).End(xlUp).Row

Interspec_HD_Sht.Rows("2:" & Interspec_HD_LastRow).Copy _
Destination:=HDPending_Sht.Rows(Interspec_HD_LastR ow + 1)

HDPending.Close Savechanges:=False
Interspec_HD.Close Savechanges:=True

Set HDPending = Nothing
Set HDPending_Sht = Nothing
Set Interspec_HD = Nothing
Set Interspec_HD_Sht = Nothing


Set CHReport = Workbooks.Open( _
Filename:=Folder & "CHReport.csv")

Set CHReport_Sht = CHReport.Sheets(1)

CHReport_LastRow = CHReport_Sht.Range("A" & Rows.Count).End(xlUp).Row

Set Interspec_CH = Workbooks.Open( _
Filename:=Folder & "Interspec_CH_EOD_Report.csv")

Set Interspec_CH_Sht = Interspec_CH.Sheets(1)

Interspec_CH_LastRow = Interspec_CH_Sht.Range("A" & Rows.Count).End(xlUp).Row

Interspec_CH_Sht.Rows("2:" & Interspec_CH_LastRow).Copy _
Destination:=CHReport_Sht.Rows(Interspec_CH_LastRo w + 1)

CHReport.Close Savechanges:=False
Interspec_CH.Close Savechanges:=True

End Sub






"kiran" wrote:

Hi All,
I have 4 csv report file with the following file name as follows :-
Interspec_CH_EOD_Report.csv
CHReport.csv
Interspec_HD_EOD_Report.csv
HDPending.csv

my query is I want all HDPending.csv data to be copied in
Interspec_HD_EOD_Report.csv file at the end of the row of

Interspec_HD_EOD_Report.csv file deleting the heading row from HDPending.csv
like for CHReport.csv also to be copied

to Interspec_CH_EOD_Report.csv file.
TIA

joel
Excel Merge
 
Looking at the code I think I opened the same workbook twice. tTy this change

from

Set HDPending = Workbooks.Open( _
Filename:=Folder & "Interspec_HD_EOD_Report.csv")

to

Set HDPending = Workbooks.Open( _
Filename:=Folder & "HDPending.csv")

Make sure you change the line with HDPending. The object wasn't found
because excel probably didn't open the workbook because it was already opend.


"kiran" wrote:

Thanks Joel for your prompt replay but I'm getting object required at
Interspec_HD_Sht.Rows("2:" & Interspec_HD_LastRow).Copy _
Destination:=HDPending_Sht.Rows(Interspec_HD_LastR ow + 1)
line can u pls revert on this.
TIA


"Joel" wrote:

Make copies of all files before trying this code.

Sub Copyfiles()

Folder = "C:\Temp\"

Set HDPending = Workbooks.Open( _
Filename:=Folder & "Interspec_HD_EOD_Report.csv")

Set HDPending_Sht = HDPending.Sheets(1)

HDPending_LastRow = HDPending_Sht.Range("A" & Rows.Count).End(xlUp).Row

Set Interspec_HD = Workbooks.Open( _
Filename:=Folder & "Interspec_HD_EOD_Report.csv")

Set Interspec_HD_Sht = Interspec_HD.Sheets(1)

Interspec_HD_LastRow = Interspec_HD_Sht.Range("A" & Rows.Count).End(xlUp).Row

Interspec_HD_Sht.Rows("2:" & Interspec_HD_LastRow).Copy _
Destination:=HDPending_Sht.Rows(Interspec_HD_LastR ow + 1)

HDPending.Close Savechanges:=False
Interspec_HD.Close Savechanges:=True

Set HDPending = Nothing
Set HDPending_Sht = Nothing
Set Interspec_HD = Nothing
Set Interspec_HD_Sht = Nothing


Set CHReport = Workbooks.Open( _
Filename:=Folder & "CHReport.csv")

Set CHReport_Sht = CHReport.Sheets(1)

CHReport_LastRow = CHReport_Sht.Range("A" & Rows.Count).End(xlUp).Row

Set Interspec_CH = Workbooks.Open( _
Filename:=Folder & "Interspec_CH_EOD_Report.csv")

Set Interspec_CH_Sht = Interspec_CH.Sheets(1)

Interspec_CH_LastRow = Interspec_CH_Sht.Range("A" & Rows.Count).End(xlUp).Row

Interspec_CH_Sht.Rows("2:" & Interspec_CH_LastRow).Copy _
Destination:=CHReport_Sht.Rows(Interspec_CH_LastRo w + 1)

CHReport.Close Savechanges:=False
Interspec_CH.Close Savechanges:=True

End Sub






"kiran" wrote:

Hi All,
I have 4 csv report file with the following file name as follows :-
Interspec_CH_EOD_Report.csv
CHReport.csv
Interspec_HD_EOD_Report.csv
HDPending.csv

my query is I want all HDPending.csv data to be copied in
Interspec_HD_EOD_Report.csv file at the end of the row of

Interspec_HD_EOD_Report.csv file deleting the heading row from HDPending.csv
like for CHReport.csv also to be copied

to Interspec_CH_EOD_Report.csv file.
TIA

kiran
Excel Merge
 
Thanks Joel

"Joel" wrote:

Looking at the code I think I opened the same workbook twice. tTy this change

from

Set HDPending = Workbooks.Open( _
Filename:=Folder & "Interspec_HD_EOD_Report.csv")

to

Set HDPending = Workbooks.Open( _
Filename:=Folder & "HDPending.csv")

Make sure you change the line with HDPending. The object wasn't found
because excel probably didn't open the workbook because it was already opend.


"kiran" wrote:

Thanks Joel for your prompt replay but I'm getting object required at
Interspec_HD_Sht.Rows("2:" & Interspec_HD_LastRow).Copy _
Destination:=HDPending_Sht.Rows(Interspec_HD_LastR ow + 1)
line can u pls revert on this.
TIA


"Joel" wrote:

Make copies of all files before trying this code.

Sub Copyfiles()

Folder = "C:\Temp\"

Set HDPending = Workbooks.Open( _
Filename:=Folder & "Interspec_HD_EOD_Report.csv")

Set HDPending_Sht = HDPending.Sheets(1)

HDPending_LastRow = HDPending_Sht.Range("A" & Rows.Count).End(xlUp).Row

Set Interspec_HD = Workbooks.Open( _
Filename:=Folder & "Interspec_HD_EOD_Report.csv")

Set Interspec_HD_Sht = Interspec_HD.Sheets(1)

Interspec_HD_LastRow = Interspec_HD_Sht.Range("A" & Rows.Count).End(xlUp).Row

Interspec_HD_Sht.Rows("2:" & Interspec_HD_LastRow).Copy _
Destination:=HDPending_Sht.Rows(Interspec_HD_LastR ow + 1)

HDPending.Close Savechanges:=False
Interspec_HD.Close Savechanges:=True

Set HDPending = Nothing
Set HDPending_Sht = Nothing
Set Interspec_HD = Nothing
Set Interspec_HD_Sht = Nothing


Set CHReport = Workbooks.Open( _
Filename:=Folder & "CHReport.csv")

Set CHReport_Sht = CHReport.Sheets(1)

CHReport_LastRow = CHReport_Sht.Range("A" & Rows.Count).End(xlUp).Row

Set Interspec_CH = Workbooks.Open( _
Filename:=Folder & "Interspec_CH_EOD_Report.csv")

Set Interspec_CH_Sht = Interspec_CH.Sheets(1)

Interspec_CH_LastRow = Interspec_CH_Sht.Range("A" & Rows.Count).End(xlUp).Row

Interspec_CH_Sht.Rows("2:" & Interspec_CH_LastRow).Copy _
Destination:=CHReport_Sht.Rows(Interspec_CH_LastRo w + 1)

CHReport.Close Savechanges:=False
Interspec_CH.Close Savechanges:=True

End Sub






"kiran" wrote:

Hi All,
I have 4 csv report file with the following file name as follows :-
Interspec_CH_EOD_Report.csv
CHReport.csv
Interspec_HD_EOD_Report.csv
HDPending.csv

my query is I want all HDPending.csv data to be copied in
Interspec_HD_EOD_Report.csv file at the end of the row of

Interspec_HD_EOD_Report.csv file deleting the heading row from HDPending.csv
like for CHReport.csv also to be copied

to Interspec_CH_EOD_Report.csv file.
TIA


All times are GMT +1. The time now is 11:24 AM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com