Get data present in Row1 of Sheet1 copied into row1 of Sheet2 [Solved/Closed]

Report
Posts
5
Registration date
Sunday July 12, 2015
Status
Member
Last seen
July 15, 2015
-
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
-
Hello,
I need to get data present in Row1 of Sheet1(Central OPD) to be copied into row1 of Sheet2(Departmental OPD) based on Department Column of Sheet1.
Example,
If patient belongs to gynic department, Whole row need be copied to Sheet2(Gynic Departmental OPD)
Kindly Help

6 replies

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Shambu,

Could you please upload a sample of your work book with Inputs and expected outputs as it is not overly clear what it is that you want to do. Someone should then be able to help you.

You can upload a sample by using a free file sharing site such as DropBox or ge.tt but please be careful with any sensitive data.

Thank you and regards,
vcoolio.
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
5
Registration date
Sunday July 12, 2015
Status
Member
Last seen
July 15, 2015

Could you plz give me your email id? so that i can share my sample data.
Anticipating your earliest response...
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Shambu,

I'm sorry Shambu but sharing e-mail IDs is not permitted. Nor is solving queries by PMs.

As this is a community site, all queries need to be solved here on the forum for all to see. Others, searching various forums by various search engines, and who may be directed here by one of those search engines, may have similar queries to yours to solve so it is a simple case of help one, help all.

Please familiarise yourself with the CCM Charter (click on Charter at the bottom of the page).

We trust that you understand.

Thank you and regards.

vcoolio.
(Moderator)
1
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2942 users have said thank you to us this month

Posts
5
Registration date
Sunday July 12, 2015
Status
Member
Last seen
July 15, 2015

Hello vcoolio ,
Yes, I can understand it.. Sorry...
Plz let me know how can i upload sample data file?
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Shambu,

Please refer to my post #1, second paragraph.

Cheerio,
vcoolio.
Posts
5
Registration date
Sunday July 12, 2015
Status
Member
Last seen
July 15, 2015

Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Shambu,

Just so that we are clear:-

1) The Central OPD and Central IPD sheets are basically "input" sheets.

2) With the Central OPD sheet: When you enter data in Columns A:H, you would like this data (Columns A:H only) transferred to individual OPD sheets such as KC OPD, SL OPD, SK OPD etc. based on a unique criteria being placed in Column I (Department). This criteria is as shown in Column I of the Central OPD sheet.

3) With the Central IPD sheet: I assume the same with this sheet as per the Central OPD sheet. However, the headings in the individual IPD sheets do not match the headings in the Central IPD sheet. In the Central IPD sheet you have Column C as the CR.NO column yet in the individual sheets you have OPD NO (Column C) and IPD NO (Column D). Should the Central IPD sheet have OPD NO and IPD NO columns instead of CR.NO?
Can the Department column be moved to the end of the table since you don't want the criteria in this column transferred to the individual sheets?

4) When the data is transferred, do you want the "used" data in the Central OPD and IPD sheets cleared?

Please confirm /clarify the above.

Cheerio,
vcoolio.
Posts
5
Registration date
Sunday July 12, 2015
Status
Member
Last seen
July 15, 2015

1. Yes . Central OPD and Central IPD sheets are input sheets.
2. Yes right.
3. Yes. Central IPD sheet can have OPD NO and IPD NO columns instead of CR.NO.
4.No. used data must be available....
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Shambu,

I have created two codes, virtually identical, for you. One is for the Central OPD sheet and one for the Central IPD sheet. They are:-

Sub TransferDataOPD()

Application.ScreenUpdating = False

Dim lRow As Long
Dim MySheet As String
lRow = Range("A" & Rows.Count).End(xlUp).Row

On Error Resume Next
For Each cell In Range("I10:I" & lRow)
    MySheet = cell.Value
    Range(Cells(cell.Row, "A"), Cells(cell.Row, "H")).Copy
    Sheets(MySheet).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Sheets(MySheet).Columns.AutoFit
Next cell

Application.ScreenUpdating = True
Application.CutCopyMode = False

End Sub


and
Sub TransferDataIPD()

Application.ScreenUpdating = False

Dim lRow As Long
Dim MySheet As String
lRow = Range("A" & Rows.Count).End(xlUp).Row

On Error Resume Next
For Each cell In Range("L10:L" & lRow)
    MySheet = cell.Value
    Range(Cells(cell.Row, "A"), Cells(cell.Row, "K")).Copy
    Sheets(MySheet).Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
    Sheets(MySheet).Columns.AutoFit
Next cell

Application.ScreenUpdating = True
Application.CutCopyMode = False

End Sub


Here is my test work book, based on the one you supplied, for you to peruse:-

https://www.dropbox.com/s/di42fyiv6rsd0qk/Shambu.xlsm?dl=0

I have made some minor alterations to the work book just to simplify the code. The main alteration is in the "Department" columns of both the input sheets. You will see that I have placed OPD or IPD after the Department criteria with a space in between (e.g. KC OPD or KC IPD) just to match the sheet tab names. Doing so has greatly reduced the size of the macros required to do the task. Please remember to place OPD or IPD after each criteria when you enter it in the Department columns.

Click on the transfer buttons to execute the code for each input sheet.

I hope that this helps.

Cheerio,
vcoolio.
Dear Cheerio,
Thanks a lot.... It worked....
Thank u once again....
Posts
1260
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
February 3, 2020
213
Hello Shambu,

You're welcome. Glad that I could help.

Cheerio,
vcoolio.