Help with copying from one work sheet to another

[erika_walls]

Hello Everyone My name is Erika and I am new here ,
I am looking for some help with excel. Our company just switched over to an EMR (Electronic medical Record) Program and I am in charge of doing the data transfer. I have to compile a list of patients and their information to one giant excel spread sheet. Unfortunately my system is old and I can only transfer little bits of information at a time so Im having issues.
I have 2 excel sheets open

the first one has collum A - H, Collum A has a list of chart numbers on it which I want to use as reference the other collums contain data like names adress ph # etc.

The Second sheet has Collums A-P Collum P has chart numbers just like collum A does on the other sheet however there are not as many chart numbers and if I try to simply copy and paste the data does not match up.

Is there a way I can Copy paste or enter a formula that will match up what is on sheet 1 with what is on sheet 2 using those reference numbers?

Gosh I hope this makes sence, Your help is apreciated thank you all so much in advance.

[darknation144]

Change your title to something more descriptive before this thread gets closed.

[erika_walls]

oops, how do I change it?... nevermind i found out how thanks

[darknation144]

click edit post on your first post then go advanced. There is a button below the text box. Then you can change the title.

[darknation144]

You could use a formula but then it would still reference the old excel file. To completely move the data you will need to copy paste.

[erika_walls]

I want to do all the data in one workbook to the other, I've been copy pasting one row at a time and it is so time consuming I hope someone has another way to do it

[ParkerExcel]

If there is a pattern you could record a macro and loop it. If there is a lot of data that would definitely be the quickest. If you're repeating a task, say, copying Column A on one worksheet to Column A on the other, and B to B and so on, then you can open the Developer tab and hit record macro and perform the actions. If it's a more complicated pattern, simply record until you get to the place it repeats. Once you have a repetative course of action, open the macro for editing and create a loop. It would be easier to explain if I could see what you're trying to do, but that's the simplest quick answer I can give you.

[erika_walls]

here is another way to put it Column A has about 1000 cells of numeric codes column B has about 800 cells of numeric codes some of those numbers match what is on column A. Each code in column b has has associated data in column C,D,E,F and so on is there a way to maybe sort the data to match column a with what is on column b and move the data that is attached to column b with it?

[DGagnon]

if you move the chart number in sheet2 to over to column A, then you could use vlookup in sheet1 to get your data. if you provide a sample with headers i could help you to determin a good formula

[tkowal]

This sounds like something a database can do more quickly and efficiently.

Have you thought about pasting the two sheets as tables in MS Access and do queries to match up the data...? After all the cleanning is completed then it is a simply copy from MS Access back into a spreadsheet....

[ParkerExcel]

I agree, a database would probably be easier. But assuming it's not available, this might work. I'm not sure what the correlation between column A and column B is, but I created a workbook that does something similar to what you need. I took 3 columns, A, B, and C. The first 3 numbers in A go with the first number in B and the first and second in C. The second 3 in A go with the second in be and the 3rd and 4th in C. The copies and pastes accordingly. To start the program, cell A1 in both sheets must be selected. Key Code is Ctrl+Shift+A

If you're interested here is the code.

Sub CopyPaste()
'
' CopyPaste Macro
'
' Keyboard Shortcut: Ctrl+Shift+A
'
'These first lines just set some variable definitions so the program will run down the list properly.
Dim A As Integer
Dim B As Integer
Dim C As Integer
A = 2
B = 2
C = 0
Do
'Here it's copying the A values from the first to the second sheets
Sheets("Sheet2").Select
Selection.Copy
Sheets("Sheet3").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
'At this point, I had to step into the program and modify it changing the value in the Offset Box to "B"
Sheets("Sheet2").Select
ActiveCell.Offset(-B, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
ActiveCell.Offset(-2, 1).Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
'At this point, I had to step into the program and modify it changing the value in the Offset Box to "C"
ActiveCell.Offset(C, 1).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
ActiveCell.Offset(0, 1).Range("A1").Select
ActiveSheet.Paste
Sheets("Sheet2").Select
ActiveCell.Offset(1, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet3").Select
ActiveCell.Offset(2, 0).Range("A1").Select
ActiveSheet.Paste
ActiveCell.Offset(-2, -1).Range("A1:A3").Select
Application.CutCopyMode = False
'This is just some formatting
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Selection.Merge
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = True
End With
ActiveCell.Offset(1, -1).Range("A1").Select
Sheets("Sheet2").Select
'Here I changed the input to "A"
ActiveCell.Offset(A, -2).Range("A1").Select
Sheets("Sheet2").Select
A = A + 1
B = B + 2
C = C + 1
'By using the varibles I was able create a loop that will sort the data without confusing itself
Loop Until IsEmpty(ActiveCell.Offset(0, 0)) = True
Sheets("Sheet3").Select
End Sub

[ParkerExcel]

All you'll have to do is determine the correlation so you can use the correct variable relationship. You can use the macro recorder to record the steps for copying and pasting and then add the variables and loop in at the end. Hope that works for you