VBA code to merge weekly spreadsheet with main spreadsheet

[skarut]

I have an excel sheet that our research lab uses to update our mouse colony. In addition, every week a veteranarian emails a list of new mice that they numbered for us


I would like to take the information from weekly email spreadsheet and have a macro button on the main sheet to open the emailed sheet and copy certain information from it and append it to the bottom of the sheet. There are also some strings I want altered upon the copying of text:


As an example...on my main sheet
Column A: corresponds to the mouse number
Column B: mouse sex (written as Male or Female)
Column H: date of birth
Column L: mother ID number (written as just a number)
Column R: father ID number (written as just a number)


On the sheet that gets emailed to us each week
Column A: new mouse number (the first mouse listed is NOT always in A1 so the code would need to search column A for the first numeric value and copy cell values in that row that will correspond to the main sheet listed below)
Column B: date of birth
Column D: mouse sex (written as M or F - needs to be converted to "Male or Female" on the main sheet)
Column E: mother ID number (written as a number followed by a text string that needs to be removed when copied to the main sheet)
Column F: father ID number (written as number followed by text string that needs to be removed)


So basically, when I click the button. I want a fileopen box to pop up so i can select my import sheet then I want the macro to search for the first new mouse in the import list and copy the corresponding information to the main sheet at the first blank row on the bottom of the sheet


So if my last mouse record is in row 1000, I want all new mice to be in the rows 1001 and down.


Column A Import Sheet ----> Column A Main Sheet
Column B Import Sheet ----> Column H Main Sheet
Column D Import Sheet ----> Column B Main Sheet (changing M or F on the import, to Male or Female on the Main sheet)
Column E Import Sheet ----> Column L Main Sheet (change number followed by text to just the number)
Column F Import Sheet ----> Column R Main Sheet (change number followed by text to just the number)

Then after copying the last mouse record on the Import Sheet, I would like the imported sheet to close

Thanks!

 

[My Aswer Is This]

Show me an example of how this would look:

Column E: mother ID number (written as a number followed by a text string that needs to be removed when copied to the main sheet)
Column F: father ID number (written as number followed by text string that needs to be removed)

Would it always be a number then a space and then some text?

For example would it look like this:

2145 And the mouse is blue

Or would it look like this:

12345And The mouse is blue

Can I tell the script to just keep the values to the left of the first space?

 

[skarut]

Show me an example of how this would look:

Column E: mother ID number (written as a number followed by a text string that needs to be removed when copied to the main sheet)
Column F: father ID number (written as number followed by text string that needs to be removed)

Would it always be a number then a space and then some text?

For example would it look like this:

2145 And the mouse is blue

Or would it look like this:

12345And The mouse is blue

Can I tell the script to just keep the values to the left of the first space?

It would be the first scenario (as an example the cell would contain "6141 fl/fl tg" but all I want is the number to left of the space)

Thanks for your help!!!

 

[My Aswer Is This]

Now that I know this I will get back with you. Check back in here later today.

It would be the first scenario (as an example the cell would contain "6141 fl/fl tg" but all I want is the number to left of the space)

Thanks for your help!!!

 

[My Aswer Is This]

OK lets try this:

Assuming your Master Sheet is named "Master"
And assuming your Imported sheet is Sheet(2) in your workbook.

Run this script:

Now this script will not open your import sheet and will not close your workbook.

If we get this script to work then I will attempt to provide those options later.

Sub Mouse_Work()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Sheets("Master").Activate
Lastrow = Sheets(2).Cells(Rows.Count, "A").End(xlUp).Row
Dim Lastrowa As Long
Lastrowa = Sheets("Master").Cells(Rows.Count, "A").End(xlUp).Row + 1
Dim ans As String
Dim ans2 As String
    For i = 2 To Lastrow
    If IsNumeric(Sheets(2).Cells(i, 1)) Then  'Check to see if cell value is numeric
        ans = Application.WorksheetFunction.Find(" ", Sheets(2).Cells(i, "E").Value, 1) 'Mother ID
        ans2 = Application.WorksheetFunction.Find(" ", Sheets(2).Cells(i, "F").Value, 1) 'Father ID
        
        Sheets("Master").Cells(Lastrowa, "L").Value = Left(Sheets(2).Cells(i, "E"), ans)  'Mother ID
        Sheets("Master").Cells(Lastrowa, "R").Value = Left(Sheets(2).Cells(i, "F"), ans2) ' Father ID
        Sheets(2).Cells(i, "A").Copy Destination:=Sheets("Master").Cells(Lastrowa, "A") 'Mouse Number
        Sheets(2).Cells(i, "B").Copy Destination:=Sheets("Master").Cells(Lastrowa, "H") 'Birth
        
            If Sheets(2).Cells(i, "D").Value = "M" Then Sheets("Master").Cells(Lastrowa, "B").Value = "Male" 'Sex
            If Sheets(2).Cells(i, "D").Value = "F" Then Sheets("Master").Cells(Lastrowa, "B").Value = "Female" 'Sex
            Lastrowa = Lastrowa + 1
            End If
    Next
Application.ScreenUpdating = True
End Sub