I am looking for code that can do the following task.
I have a downloaded file named "SalesReport.csv" contains source data about
900 rows.Data in the file looks as below.
Col A-----Col B-------Col C---------Col D--------Col E
1 name ----CityCode-- Date ---------UnitsSold-- SaleValue
2.Smith --- MB -------12-Feb-07--- 52 ---- 30052
3.Philips -- BE --------- 12-Feb-07 --- 41 ------ 24065
4.Ravi ---- MB ---------12-Feb-07 ---- 39 ------- 22100
5.Carol ---MB --------12-Feb-07 ----- 50 ------- 28600
I have a w/book contains about 150 w/sheets,all renamed as "SalesReport.csv"
Col A:A names.
Sheet 1 renamed as 'Carol',Sheet2 renamed as "Ravi',Sheet 3 renamed as
"Smith' like this.
Data in the sheet looks as below.
Col A---------Col B ---------Col C
1 Date ---------UnitsSold ---SaleValue
2.10-Feb-07 -- 48 ------- 27645
3.11-Feb-07 --- 47 ------- 27102
My task is If Sheet1 name exists in "SalesReport.csv" Col A:A range and
CityCode=MB,copythat row's data(C:E) and paste these values next to last row
of Sheet1.If Sheet1 name does not exists or CityCode does not matches do
nothing.Loop through all other sheets of W/book and do the same task.
As per my sample data,if I run the maco,Sheet1 name(Carol) exists in Col A:A
of SalesReport.csv
and CityCode is also "MB',so the data values of that row (12-Feb-07,50,28600)
are to be copied and pasted at row 4 in 'Carol' sheet.Then goto next sheet.
I shall be thankful if anybody helps me .

--
Message posted via http://www.officekb.com

I will help. You need two diffferent filenames if both worksheets are opened
at the same time.

One file should be SalesReport.csv and the other SalesmanReport.csv. Let me
know the names of the two files.

"tkraju via OfficeKB.com" wrote:

> I am looking for code that can do the following task.
> I have a downloaded file named "SalesReport.csv" contains source data about
> 900 rows.Data in the file looks as below.
> Col A-----Col B-------Col C---------Col D--------Col E
> 1 name ----CityCode-- Date ---------UnitsSold-- SaleValue
> 2.Smith --- MB -------12-Feb-07--- 52 ---- 30052
> 3.Philips -- BE --------- 12-Feb-07 --- 41 ------ 24065
> 4.Ravi ---- MB ---------12-Feb-07 ---- 39 ------- 22100
> 5.Carol ---MB --------12-Feb-07 ----- 50 ------- 28600
> I have a w/book contains about 150 w/sheets,all renamed as "SalesReport.csv"
> Col A:A names.
> Sheet 1 renamed as 'Carol',Sheet2 renamed as "Ravi',Sheet 3 renamed as
> "Smith' like this.
> Data in the sheet looks as below.
> Col A---------Col B ---------Col C
> 1 Date ---------UnitsSold ---SaleValue
> 2.10-Feb-07 -- 48 ------- 27645
> 3.11-Feb-07 --- 47 ------- 27102
> My task is If Sheet1 name exists in "SalesReport.csv" Col A:A range and
> CityCode=MB,copythat row's data(C:E) and paste these values next to last row
> of Sheet1.If Sheet1 name does not exists or CityCode does not matches do
> nothing.Loop through all other sheets of W/book and do the same task.
> As per my sample data,if I run the maco,Sheet1 name(Carol) exists in Col A:A
> of SalesReport.csv
> and CityCode is also "MB',so the data values of that row (12-Feb-07,50,28600)
> are to be copied and pasted at row 4 in 'Carol' sheet.Then goto next sheet.
> I shall be thankful if anybody helps me .
>
> --
> Message posted via http://www.officekb.com
>
>

Sub copydata()
Dim sh as Worksheet, sh1 as Worksheet
Dim bk as Workbook, cell as Range
Dim rng1 as Range
set sh = WorkBooks("SalesReport.csv").Worksheets(1)
set rng1 = sh.Range(sh.Cells(2,1),sh.cells(rows.count,1).End(xlup))
set bk = Workbooks("Data.xls")
for each cell in rng1
if cell.offset(0,1).Value = "MB" then
set sh1 = nothing
on Error Resume Next
set sh1 = bk.worksheets(cell.Value)
on Error goto 0
if not sh1 is nothing then
cell.offset(0,2).Resize(1,3).copy _
sh1.cells(rows.count,1).end(xlup)(2)
end if
end if
Next
End Sub

--
Regards,
Tom Ogilvy


"tkraju via OfficeKB.com" wrote:

> I am looking for code that can do the following task.
> I have a downloaded file named "SalesReport.csv" contains source data about
> 900 rows.Data in the file looks as below.
> Col A-----Col B-------Col C---------Col D--------Col E
> 1 name ----CityCode-- Date ---------UnitsSold-- SaleValue
> 2.Smith --- MB -------12-Feb-07--- 52 ---- 30052
> 3.Philips -- BE --------- 12-Feb-07 --- 41 ------ 24065
> 4.Ravi ---- MB ---------12-Feb-07 ---- 39 ------- 22100
> 5.Carol ---MB --------12-Feb-07 ----- 50 ------- 28600
> I have a w/book contains about 150 w/sheets,all renamed as "SalesReport.csv"
> Col A:A names.
> Sheet 1 renamed as 'Carol',Sheet2 renamed as "Ravi',Sheet 3 renamed as
> "Smith' like this.
> Data in the sheet looks as below.
> Col A---------Col B ---------Col C
> 1 Date ---------UnitsSold ---SaleValue
> 2.10-Feb-07 -- 48 ------- 27645
> 3.11-Feb-07 --- 47 ------- 27102
> My task is If Sheet1 name exists in "SalesReport.csv" Col A:A range and
> CityCode=MB,copythat row's data(C:E) and paste these values next to last row
> of Sheet1.If Sheet1 name does not exists or CityCode does not matches do
> nothing.Loop through all other sheets of W/book and do the same task.
> As per my sample data,if I run the maco,Sheet1 name(Carol) exists in Col A:A
> of SalesReport.csv
> and CityCode is also "MB',so the data values of that row (12-Feb-07,50,28600)
> are to be copied and pasted at row 4 in 'Carol' sheet.Then goto next sheet.
> I shall be thankful if anybody helps me .
>
> --
> Message posted via http://www.officekb.com
>
>

Tom: What is the last (2) in the copy statement. I never saw any
documentation on this feature. Is it part of the End or is it part of the
cells?

cell.offset(0,2).Resize(1,3).copy _
sh1.cells(rows.count,1).end(xlup)(2)


"Tom Ogilvy" wrote:

> Sub copydata()
> Dim sh as Worksheet, sh1 as Worksheet
> Dim bk as Workbook, cell as Range
> Dim rng1 as Range
> set sh = WorkBooks("SalesReport.csv").Worksheets(1)
> set rng1 = sh.Range(sh.Cells(2,1),sh.cells(rows.count,1).End(xlup))
> set bk = Workbooks("Data.xls")
> for each cell in rng1
> if cell.offset(0,1).Value = "MB" then
> set sh1 = nothing
> on Error Resume Next
> set sh1 = bk.worksheets(cell.Value)
> on Error goto 0
> if not sh1 is nothing then
> cell.offset(0,2).Resize(1,3).copy _
> sh1.cells(rows.count,1).end(xlup)(2)
> end if
> end if
> Next
> End Sub
>
> --
> Regards,
> Tom Ogilvy
>
>
> "tkraju via OfficeKB.com" wrote:
>
> > I am looking for code that can do the following task.
> > I have a downloaded file named "SalesReport.csv" contains source data about
> > 900 rows.Data in the file looks as below.
> > Col A-----Col B-------Col C---------Col D--------Col E
> > 1 name ----CityCode-- Date ---------UnitsSold-- SaleValue
> > 2.Smith --- MB -------12-Feb-07--- 52 ---- 30052
> > 3.Philips -- BE --------- 12-Feb-07 --- 41 ------ 24065
> > 4.Ravi ---- MB ---------12-Feb-07 ---- 39 ------- 22100
> > 5.Carol ---MB --------12-Feb-07 ----- 50 ------- 28600
> > I have a w/book contains about 150 w/sheets,all renamed as "SalesReport.csv"
> > Col A:A names.
> > Sheet 1 renamed as 'Carol',Sheet2 renamed as "Ravi',Sheet 3 renamed as
> > "Smith' like this.
> > Data in the sheet looks as below.
> > Col A---------Col B ---------Col C
> > 1 Date ---------UnitsSold ---SaleValue
> > 2.10-Feb-07 -- 48 ------- 27645
> > 3.11-Feb-07 --- 47 ------- 27102
> > My task is If Sheet1 name exists in "SalesReport.csv" Col A:A range and
> > CityCode=MB,copythat row's data(C:E) and paste these values next to last row
> > of Sheet1.If Sheet1 name does not exists or CityCode does not matches do
> > nothing.Loop through all other sheets of W/book and do the same task.
> > As per my sample data,if I run the maco,Sheet1 name(Carol) exists in Col A:A
> > of SalesReport.csv
> > and CityCode is also "MB',so the data values of that row (12-Feb-07,50,28600)
> > are to be copied and pasted at row 4 in 'Carol' sheet.Then goto next sheet.
> > I shall be thankful if anybody helps me .
> >
> > --
> > Message posted via http://www.officekb.com
> >
> >

Thanks,bou it didn't give me the desired results I need.Your code updated
only sheet1 of my w/book.It has not looped through all sheets of my w/book to
do the same task.My sheet2 and Sheet3 names also exists in "SalesReport.csv"
ColA:A range.Please reread my question carefully.

Tom Ogilvy wrote:
>Sub copydata()
>Dim sh as Worksheet, sh1 as Worksheet
>Dim bk as Workbook, cell as Range
>Dim rng1 as Range
>set sh = WorkBooks("SalesReport.csv").Worksheets(1)
>set rng1 = sh.Range(sh.Cells(2,1),sh.cells(rows.count,1).End(xlup))
>set bk = Workbooks("Data.xls")
>for each cell in rng1
> if cell.offset(0,1).Value = "MB" then
> set sh1 = nothing
> on Error Resume Next
> set sh1 = bk.worksheets(cell.Value)
> on Error goto 0
> if not sh1 is nothing then
> cell.offset(0,2).Resize(1,3).copy _
> sh1.cells(rows.count,1).end(xlup)(2)
> end if
> end if
>Next
>End Sub
>
>> I am looking for code that can do the following task.
>> I have a downloaded file named "SalesReport.csv" contains source data about
>[quoted text clipped - 23 lines]
>> are to be copied and pasted at row 4 in 'Carol' sheet.Then goto next sheet.
>> I shall be thankful if anybody helps me .

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200703/1

I am sorry I didn't checked it properly.Yours code working perfectly.How did
you wrote without using any loop activity.Thak you so much for your help.
Please ignore my last reply.

Tom Ogilvy wrote:
>Sub copydata()
>Dim sh as Worksheet, sh1 as Worksheet
>Dim bk as Workbook, cell as Range
>Dim rng1 as Range
>set sh = WorkBooks("SalesReport.csv").Worksheets(1)
>set rng1 = sh.Range(sh.Cells(2,1),sh.cells(rows.count,1).End(xlup))
>set bk = Workbooks("Data.xls")
>for each cell in rng1
> if cell.offset(0,1).Value = "MB" then
> set sh1 = nothing
> on Error Resume Next
> set sh1 = bk.worksheets(cell.Value)
> on Error goto 0
> if not sh1 is nothing then
> cell.offset(0,2).Resize(1,3).copy _
> sh1.cells(rows.count,1).end(xlup)(2)
> end if
> end if
>Next
>End Sub
>
>> I am looking for code that can do the following task.
>> I have a downloaded file named "SalesReport.csv" contains source data about
>[quoted text clipped - 23 lines]
>> are to be copied and pasted at row 4 in 'Carol' sheet.Then goto next sheet.
>> I shall be thankful if anybody helps me .

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...mming/200703/1

Mr.Tom,my sincere thanks to you,the sub working very fine.If I am running
this sub 'N' number of times,it is adding to my w/book sheets 'N' number of
rows with same data.What alteration to this code will prevent this routine
updates my sheets 'N' number of times.I want this routine updates my w/book
sheets only once.If date value of "SalesReport.csv" (col C) exists in any of
my w/book sheets Col A range,the sub prompts a msg."12-Feb-07(date value of
Sales Report) data already exists.Updation not required".I think this date
logic will prevent the sub to run 'N' number of times.

Tom Ogilvy wrote:
>Sub copydata()
>Dim sh as Worksheet, sh1 as Worksheet
>Dim bk as Workbook, cell as Range
>Dim rng1 as Range
>set sh = WorkBooks("SalesReport.csv").Worksheets(1)
>set rng1 = sh.Range(sh.Cells(2,1),sh.cells(rows.count,1).End(xlup))
>set bk = Workbooks("Data.xls")
>for each cell in rng1
> if cell.offset(0,1).Value = "MB" then
> set sh1 = nothing
> on Error Resume Next
> set sh1 = bk.worksheets(cell.Value)
> on Error goto 0
> if not sh1 is nothing then
> cell.offset(0,2).Resize(1,3).copy _
> sh1.cells(rows.count,1).end(xlup)(2)
> end if
> end if
>Next
>End Sub
>
>> I am looking for code that can do the following task.
>> I have a downloaded file named "SalesReport.csv" contains source data about
>[quoted text clipped - 23 lines]
>> are to be copied and pasted at row 4 in 'Carol' sheet.Then goto next sheet.
>> I shall be thankful if anybody helps me .

--
Message posted via http://www.officekb.com

Mr.Tom,Can you add one more condition - if the date value of "SalesReport"
matches with date value of my w/book sheet ,the sub should end by prompting
msg."12-Feb-07 data already exists.Updation not required".This prevents
repeated updation process.At present If I run the sub n number of times,n
number of times 12-Feb-07 data is being added to last rows of w/book sheets.

"tkraju via OfficeKB.com" wrote:

> Thanks,bou it didn't give me the desired results I need.Your code updated
> only sheet1 of my w/book.It has not looped through all sheets of my w/book to
> do the same task.My sheet2 and Sheet3 names also exists in "SalesReport.csv"
> ColA:A range.Please reread my question carefully.
>
> Tom Ogilvy wrote:
> >Sub copydata()
> >Dim sh as Worksheet, sh1 as Worksheet
> >Dim bk as Workbook, cell as Range
> >Dim rng1 as Range
> >set sh = WorkBooks("SalesReport.csv").Worksheets(1)
> >set rng1 = sh.Range(sh.Cells(2,1),sh.cells(rows.count,1).End(xlup))
> >set bk = Workbooks("Data.xls")
> >for each cell in rng1
> > if cell.offset(0,1).Value = "MB" then
> > set sh1 = nothing
> > on Error Resume Next
> > set sh1 = bk.worksheets(cell.Value)
> > on Error goto 0
> > if not sh1 is nothing then
> > cell.offset(0,2).Resize(1,3).copy _
> > sh1.cells(rows.count,1).end(xlup)(2)
> > end if
> > end if
> >Next
> >End Sub
> >
> >> I am looking for code that can do the following task.
> >> I have a downloaded file named "SalesReport.csv" contains source data about
> >[quoted text clipped - 23 lines]
> >> are to be copied and pasted at row 4 in 'Carol' sheet.Then goto next sheet.
> >> I shall be thankful if anybody helps me .
>
> --
> Message posted via OfficeKB.com
> http://www.officekb.com/Uwe/Forums.a...mming/200703/1
>
>