Formula to copy specific cells from one sheet to another - Please Help! Thanks

G

G1221

New Member
Joined
Sep 24, 2014
Messages
24
Hello Everyone,

I am definitely not well versed in the awesomeness that is Excel. I have seen many people on here write formulas that work magic and I am in need of that expertise.

I am trying to write a formula that will copy specific cells from one sheet to another. For example: if I have a column that has multiple cells that are identical, I would like to copy that entire row (or even better, certain cells in that row) to another sheet. I have attached an example to hopefully make things more clear.

THANKS SO MUCH!!! :)

A specific example using the info below would be this:
  • This example sheet is B5:B36, so the data starts at B6. The formula will be looking at the "Initials" column to know what row to copy and paste into the new sheet.
  • The example sheet below is "sheet1" and has information to be copied and pasted into different sheets, e.g. "sheet2", "sheet3", etc
  • All rows that start with "WLW" will be copied to "sheet2"; whereas, all rows that start with "JVL" will be copied to "sheet3" ***Ideally, I would like that only information from columns "initials", "patient", and "procedure" to be copied to the new sheet.*** Not sure if this is too difficult though...if it is, the entire row will work well enough.
  • In the end, there will be separate sheets for each set of initials. This way all of the row information corresponding to those initials from "sheet1" will be collected in a separate sheet, e.g. "sheet2" for "WLW" and "sheet3" for "JVL".
  • Also, I am hoping that I can use these formulas in Google Sheets (in addition to Excel). I am trying to make information available real-time.

[TABLE="width: 808"]
<tbody>[TR]
[TD]Initials[/TD]
[TD]Status[/TD]
[TD]Room[/TD]
[TD]Patient[/TD]
[TD]Procedure[/TD]
[TD]POD #[/TD]
[TD]Physician[/TD]
[TD]Notes[/TD]
[/TR]
[TR]
[TD]WLW[/TD]
[TD]DC[/TD]
[TD]250[/TD]
[TD]Doe, Jane[/TD]
[TD]SBO[/TD]
[TD]7[/TD]
[TD]Smith[/TD]
[TD][/TD]
[/TR]
[TR]
[TD] JVL[/TD]
[TD][/TD]
[TD]300[/TD]
[TD]Doe, John[/TD]
[TD]Lap Chole[/TD]
[TD]1[/TD]
[TD]Smith[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]WLW[/TD]
[TD][/TD]
[TD]350[/TD]
[TD]Mouse, Minnie[/TD]
[TD]RIH[/TD]
[TD]1[/TD]
[TD]Smith[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]JVL[/TD]
[TD]New[/TD]
[TD]400[/TD]
[TD]Mouse, Mickey[/TD]
[TD]Lap Appy[/TD]
[TD]1[/TD]
[TD]Smith[/TD]
[TD](+) N/D (-) Fever, Vomit[/TD]
[/TR]
[TR]
[TD]MAB[/TD]
[TD][/TD]
[TD]450[/TD]
[TD]Duck, Donald[/TD]
[TD]Bilat IH[/TD]
[TD]1[/TD]
[TD]Smith[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Here's one way to do it:

Sub MoveData()
Dim r As Integer, r2 As Integer, r3 As Integer, Dict As Object
Dim init As String, init2 As String

Set Dict = CreateObject("Scripting.Dictionary")

r = 2
While Cells(r, 1) <> ""
init = Cells(r, 1)
If Dict.exists(init) Then GoTo EndWhile:
Dict.Add init, 1
r2 = r
r3 = 2
Sheets.Add
ActiveSheet.Name = init
Sheets("Sheet1").Select
Sheets(init).Cells(1, 1) = ActiveSheet.Cells(1, 1)
Sheets(init).Cells(1, 2) = ActiveSheet.Cells(1, 4)
Sheets(init).Cells(1, 3) = ActiveSheet.Cells(1, 5)
While Cells(r2, 1) <> ""
init2 = Cells(r2, 1)
If init2 = init Then
Sheets(init).Cells(r3, 1) = ActiveSheet.Cells(r2, 1)
Sheets(init).Cells(r3, 2) = ActiveSheet.Cells(r2, 4)
Sheets(init).Cells(r3, 3) = ActiveSheet.Cells(r2, 5)
r3 = r3 + 1
End If
r2 = r2 + 1
Wend
EndWhile:
r = r + 1
Wend

End Sub

Not a lot of error checking, but I think it's fairly self-explanatory. Forgive any formatting lapses of this post, I'm new here.

-Eric
 
Upvote 0
Hi Eric,

Not quite sure how to apply this to my worksheet. I apologize for my ignorance. Any suggestions?

Eric W said:
Here's one way to do it:

Sub MoveData()
Dim r As Integer, r2 As Integer, r3 As Integer, Dict As Object
Dim init As String, init2 As String

Set Dict = CreateObject("Scripting.Dictionary")

r = 2
While Cells(r, 1) <> ""
init = Cells(r, 1)
If Dict.exists(init) Then GoTo EndWhile:
Dict.Add init, 1
r2 = r
r3 = 2
Sheets.Add
ActiveSheet.Name = init
Sheets("Sheet1").Select
Sheets(init).Cells(1, 1) = ActiveSheet.Cells(1, 1)
Sheets(init).Cells(1, 2) = ActiveSheet.Cells(1, 4)
Sheets(init).Cells(1, 3) = ActiveSheet.Cells(1, 5)
While Cells(r2, 1) <> ""
init2 = Cells(r2, 1)
If init2 = init Then
Sheets(init).Cells(r3, 1) = ActiveSheet.Cells(r2, 1)
Sheets(init).Cells(r3, 2) = ActiveSheet.Cells(r2, 4)
Sheets(init).Cells(r3, 3) = ActiveSheet.Cells(r2, 5)
r3 = r3 + 1
End If
r2 = r2 + 1
Wend
EndWhile:
r = r + 1
Wend

End Sub

Not a lot of error checking, but I think it's fairly self-explanatory. Forgive any formatting lapses of this post, I'm new here.

-Eric
Click to expand...
 
Upvote 0
G1221 said:
Hi Eric,

Not quite sure how to apply this to my worksheet. I apologize for my ignorance. Any suggestions?
Click to expand...

Your requirements sounded a bit complicated for basic Excel formulas, so I wrote a macro to do what you asked. To use it, you need to load it into the Visual Basic editor and run it.

To do that, open your spreadsheet. Press Alt-F11 to open the editor. On the left side of the screen is a navigation panel. Double-click on Sheet1. This will open a blank screen in the main window. Copy the macro from my previous post (from Sub MoveData() to End Sub) and paste it into this window. Then press F5 to run it.

Good luck.
 
Upvote 0
Hi Eric,

Thanks for the update, that definitely helped. My ultimate issue is that I would like to have the command in a formula because I need to apply it to Google Sheets. You're probably right that it's too complicated for a formula. Do you have any suggestions for making it more simple, so that it can be achieved with a formula? Regardless, you have been a great help. Thank you so much.

Cheers,
G
Eric W said:
Your requirements sounded a bit complicated for basic Excel formulas, so I wrote a macro to do what you asked. To use it, you need to load it into the Visual Basic editor and run it.

To do that, open your spreadsheet. Press Alt-F11 to open the editor. On the left side of the screen is a navigation panel. Double-click on Sheet1. This will open a blank screen in the main window. Copy the macro from my previous post (from Sub MoveData() to End Sub) and paste it into this window. Then press F5 to run it.

Good luck.
Click to expand...
 
Upvote 0
Given that you only want to use basic formulas, there are fewer options. One thing you can do is to copy all the data from Sheet1 to a blank sheet. (Either select all cells, copy it, and paste to an empty sheet, or right click on the sheet tab and select Move or Copy.) Select column A on the new sheet. Then go to the Data tab and click on the Filter button. Then go to the drop down button in column A, uncheck the Select All box, and check one of the initial boxes. Repeat for each set of initials. I don't know enough about Google Sheets to say if this will work there.

-Eric
 
Upvote 0
Hi Eric,

Thanks for the response. I was really trying to create a formula that would automatically populate the info from one sheet to a separate sheet. You have been very helpful though - thanks for all the advice!

Cheers,
G
Eric W said:
Given that you only want to use basic formulas, there are fewer options. One thing you can do is to copy all the data from Sheet1 to a blank sheet. (Either select all cells, copy it, and paste to an empty sheet, or right click on the sheet tab and select Move or Copy.) Select column A on the new sheet. Then go to the Data tab and click on the Filter button. Then go to the drop down button in column A, uncheck the Select All box, and check one of the initial boxes. Repeat for each set of initials. I don't know enough about Google Sheets to say if this will work there.

-Eric
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

A
  • Question Question
Detecting when a user has highlighted a row and displaying the contents of that row in another sheet
Replies
0
Views
75
ashamy
A
gymwrecker
  • Solved
Formula to copy data from one sheet to another sheet based on specific criteria
Replies
4
Views
464
Fluff
Fluff
A
  • Question Question
How restrict copy data from form to sheet based on five days within current month
Replies
3
Views
179
Ali M
A
A
  • Locked
  • Question Question
Severe Amateur needs code to pull data from multiple sheets into one master
Replies
1
Views
158
Peter_SSs
Peter_SSs
M
  • Solved
Copy of specific data from one sheet to another
Replies
4
Views
256
mahmoudguda29
M

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top