Copying data from one sheet to another and save the data

[haridevadiga]

Hi Friends,

I got stuck while working on this spreadsheet.

I have a two worksheet (Sheet1 and Sheet2). In Sheet1 i have the table which have Date, Associate Name (Vijay, Jaffar, Aniket, etc), Stream Name (Case Initiation (EAD-AP/STEM), Forms (EAD-AP/STEM), etc) and some data is inside C5:R9 Range.

And in Sheet2 i have the same table

Now what i want is:

1) A data in C5:R9 range get fetch from Sheet1 to Sheet2 on the criteria basis of Date, Associate name and Stream name.

2) When ever i change the date in Sheet1 the data should get fetched in respective date table in Sheet2.
For eg: if i am inputting a data for Sept 1 then a data get saved in Sept 1 table of Sheet 2. Same way if i input a data for Sept 2 then a data get saved in Sept 2 table of sheet2.

Hope you got my point.

Regards,
HD

[LeoTaxi]

small code in Sheet_Activate


Kind regards
Leo

[haridevadiga]

Hi Leo,

This is great, but you forget the things i told you.

1) A data in C5:R9 range get fetch from Sheet1 to Sheet2 on the criteria basis of Date, Associate name and Stream name.

2) When ever i change the date in Sheet1 the data should get fetched in respective date table in Sheet2.
For eg: if i am inputting a data for Sept 1 then a data get saved in Sept 1 table of Sheet 2. Same way if i input a data for Sept 2 then a data get saved in Sept 2 table of sheet2.

And when i change the name of the Associate in Sheet1, the data should change in Sheet2

Regards,
HD

[LeoTaxi]

more like this


Kind regards
Leo

[haridevadiga]

Hi Leo Bro,

Its gr8. Working Fine. Just a little one thing remaining. if i change anyone's name the figure is not changing.

For Eg: If i change Vijay to Nitai and transfer again, the value get transfered to nitai but in vijay column that previous value is showing. I dont want that value shown in vijay column

Regards,
HD

[LeoTaxi]

like it is now after run the code the values of all columns going to sheet2, to the columns with the name found in sheet1
nothing change in other columns in sheet2.

so let me know in detail what has to be changed


Kind regards
Leo

[haridevadiga]

Hi Leo Bro,

Last one is gr8 job done by you, but this time the Attached is the actual file and i want a data should be transfer on one button click.

Working sheet is the sheet where i will copy paste the table from outlook

September 2015 is the sheet where i have to transfer a data on the basis of Date, stream and associate name wise. This time September 2015 sheet have date wise columns.

Please help me in this bro.

Regards,
HD

[LeoTaxi]

Not easy if the names has different spelling
so i created a dynamic name ranged, and for the worksheet
validation for the cells with names .
Use this to have always same spelling.


Kind regards
Leo

[haridevadiga]

Hi Bro,

Thank you very much God Bless you.. Its a perfect thing what i required.. Thanks a lot bro.

Regards,
HD

Moderator's note: I have removed your phone number, it is never a good idea to put sensitive info in a public forum

[haridevadiga]

Hi Leo,

One last thing i have to ask you. If i transfer the data from Working sheet to September 2015 sheet, now incase i want to replace the cell C8 value. What should i do in below code:

Sub EAD_AP()
Dim dat As Long, r As Integer, at As Integer, ws2 As Worksheet, ws1 As Worksheet, col As Long
Set ws2 = Sheets("September 2015")
Set ws1 = Sheets("Working")
dat = Range("B3").Value

With ActiveSheet
r = Application.Match((dat), ws2.Range("B:B"), 0)
at = Application.CountA(Range("C3", "CZ3"))
For x = 3 To (at * 2) + 1 Step 2
col = Application.Match(Cells(3, x), ws2.Range("A4", "CZ4"), 0)
ws2.Cells(r + 1, col).Resize(5, 2).Value = ws1.Range(Cells(5, x), Cells(9, x + 1)).Value
ws1.Range(Cells(5, x), Cells(9, x + 1)).ClearContents
Next x
End With
End Sub

[LeoTaxi]

See that all names fields and other fields are empty except for name field up C8 en C8 itself
and run code


Kind regards
Leo

[haridevadiga]

No i mean to say. when i click the Tranfer button, it tranfer the data right. Now in transferred data if i want to replace any value in cell what should i do?

for eg: in C8 cell 10 is there when i transfered the data, now i want to replace it with 20 than.

In above case what should i do in below code

Sub EAD_AP()
Dim dat As Long, r As Integer, at As Integer, ws2 As Worksheet, ws1 As Worksheet, col As Long
Set ws2 = Sheets("September 2015")
Set ws1 = Sheets("Working")
dat = Range("B3").Value

With ActiveSheet
r = Application.Match((dat), ws2.Range("B:B"), 0)
at = Application.CountA(Range("C3", "CZ3"))
For x = 3 To (at * 2) + 1 Step 2
col = Application.Match(Cells(3, x), ws2.Range("A4", "CZ4"), 0)
ws2.Cells(r + 1, col).Resize(5, 2).Value = ws1.Range(Cells(5, x), Cells(9, x + 1)).Value
ws1.Range(Cells(5, x), Cells(9, x + 1)).ClearContents
Next x
End With
End Sub

[haridevadiga]

Hi Leo,

When i copy a table from outlook mail and paste it in working sheet for a respective stream. After that i press Transfer button, that time it given me an error (Type Mismatch). That validation went off when i paste a table from mail.

Can you please help me out in this.

Regards,
HD

[FDibbins]

haridevadiga Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)

[LeoTaxi]

if you want to change 1 cell
go to that cell
you dont need the code

and the name has to be the same and even a space more ore less is not the same

Kind regards
Leo

[haridevadiga]

Actually what i want is if a user copy paste a table from mail to given range in working sheet than it get transferred to October 2015 sheet when i click transfer button.

[LeoTaxi]

if the names are not the same it dont work

[haridevadiga]

Names are same than also it gives me an error "Type Mismatch"

[haridevadiga]

Here is the sample file with one table only.. No Validation is there. Please check

[LeoTaxi]

Nandukumar is not the same as Nandakumar

[haridevadiga]

I am sorry Bro.. Its my fault. I apologize for things.

Its working fine