copy data from source file to w/book

[tkraju via OfficeKB.com]

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 .

 

[Joel]

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.

 

[Tom Ogilvy]

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

 

[Joel]

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)

 

[tkraju via 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.

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 .

 

[tkraju via OfficeKB.com]

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.

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 .

 

[tkraju via OfficeKB.com]

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.

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 .

 

[TUNGANA KURMA RAJU]

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.

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.

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 .