get data present in Row1 of Sheet1 copied into row1 of Sheet2 Solved/Closed

Question

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

vcoolio · Answer

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.

vcoolio · Answer

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)

vcoolio · Answer

Hello Shambu,

Please refer to my post #1, second paragraph.

Cheerio,
vcoolio.

vcoolio · Answer

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.

vcoolio · Answer

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.

vcoolio · Answer

Hello Shambu,

You're welcome. Glad that I could help.

Cheerio,
vcoolio.

Get data present in Row1 of Sheet1 copied into row1 of Sheet2

6 responses

Similar discussions

Subscribe To Our Newsletter!