Updating a master spreadsheet

[cccbzg]

Hi,

I'm trying to update a master SUMMARY list with daily data from another tab and I'm not sure how to approach this. I thought of VLOOKUP, but the data to be posted (DAILY DATA tab) will not be in sequence.

So my goal is to run this program to take each day's daily data and post it to the proper ID line in the SUMMARY worksheet. The column it goes in will be a dynamic variable, so the code will have to look for the next blank column available. Any help would be much appreciated.

THANK YOU!


SUMMARY

ID	lname	fname	Date from DAILY DATA 1	CODE From DAILY DATA 1	Date from DAILY DATA 2	CODE from DAILY DATA 2	Date from DAILY DATA 3	CODE from DAILY DATA 3	Date from DAILY DATA 4	CODE from DAILY DATA 4
1	lname1	fname1
2	lname2	fname2
3	lname3	fname3
4	lname4	fname4
5	lname5	fname5
6	lname6	fname6
7	lname7	fname7
8	lname8	fname8
9	lname9	fname9

<tbody>
</tbody>

DAILY DATA

ID	Date	Code
3	09/22/15	VM
1	09/22/15	RRE
5	09/22/15	Other

<tbody>
</tbody>

 

[mumps]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Daily Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim ID As Range
    Dim foundID As Range
    Dim lCol As Long
    For Each ID In Sheets("Daily Data").Range("A2:A" & LastRow)
        Set foundID = Sheets("Summary").Range("A:A").Find(ID, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundID Is Nothing Then
            lCol = Sheets("Summary").Rows(foundID.Row).Find("*", , , , xlByColumns, xlPrevious).Column
            Sheets("Daily Data").Range("B" & ID.Row & ":C" & ID.Row).Copy Sheets("Summary").Cells(foundID.Row, lCol + 1)
        End If
    Next ID
    Application.ScreenUpdating = True
End Sub

Please change the sheet name sin the code to suit your needs.

 

[cccbzg]

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Sheets("Daily Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim ID As Range
    Dim foundID As Range
    Dim lCol As Long
    For Each ID In Sheets("Daily Data").Range("A2:A" & LastRow)
        Set foundID = Sheets("Summary").Range("A:A").Find(ID, LookIn:=xlValues, lookat:=xlWhole)
        If Not foundID Is Nothing Then
            lCol = Sheets("Summary").Rows(foundID.Row).Find("*", , , , xlByColumns, xlPrevious).Column
            Sheets("Daily Data").Range("B" & ID.Row & ":C" & ID.Row).Copy Sheets("Summary").Cells(foundID.Row, lCol + 1)
        End If
    Next ID
    Application.ScreenUpdating = True
End Sub

Please change the sheet name sin the code to suit your needs.

This is great...almost perfect! The code skips the first line (in my example ID 3), but correctly posts the other two. I'm not sure what to change
Would you mind providing a brief explanation of the code. I'm really trying to learn this stuff.

Many, many thanks.

 

[mumps]

I've included comments with the code.

Sub CopyData()
    Application.ScreenUpdating = False 'stops screen from refreshing and consequently speeds up the macro
    Dim LastRow As Long
    LastRow = Sheets("Daily Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'sets row number of the last used cell
    Dim ID As Range
    Dim foundID As Range
    Dim lCol As Long
    For Each ID In Sheets("Daily Data").Range("A2:A" & LastRow) 'loops through all the ID's in column A starting at row 2 until the last used cell
        Set foundID = Sheets("Summary").Range("A:A").Find(ID, LookIn:=xlValues, lookat:=xlWhole) 'looks for the ID in column A of the Summary sheet
        If Not foundID Is Nothing Then 'if the ID is found, the next two lines of code execute
            lCol = Sheets("Summary").Rows(foundID.Row).Find("*", , , , xlByColumns, xlPrevious).Column 'sets the number of the last used column in the row where the ID is found
            Sheets("Daily Data").Range("B" & ID.Row & ":C" & ID.Row).Copy Sheets("Summary").Cells(foundID.Row, lCol + 1) 'copies columns B and C of the ID row to the corresponding ID row in Summary
        End If
    Next ID 'looks for the next ID
    Application.ScreenUpdating = True 'turns screen refreshing back on
End Sub

The code starts searching for the ID in row 2. I always start searches at row 2 because there is usually a column header in row 1. If you have no headers and your ID's start in row 1, the code will skip the first row. If this is the case, change this line:

For Each ID In Sheets("Daily Data").Range("A2:A" & LastRow)

to this line:

For Each ID In Sheets("Daily Data").Range("A1:A" & LastRow)

Notice that A2 was changed to A1 to tell Excel to start the search on row 1. I hope this helps. If you are still getting the problem, please let me know.

 

[cccbzg]

I've included comments with the code.

Sub CopyData()
    Application.ScreenUpdating = False 'stops screen from refreshing and consequently speeds up the macro
    Dim LastRow As Long
    LastRow = Sheets("Daily Data").Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row 'sets row number of the last used cell
    Dim ID As Range
    Dim foundID As Range
    Dim lCol As Long
    For Each ID In Sheets("Daily Data").Range("A2:A" & LastRow) 'loops through all the ID's in column A starting at row 2 until the last used cell
        Set foundID = Sheets("Summary").Range("A:A").Find(ID, LookIn:=xlValues, lookat:=xlWhole) 'looks for the ID in column A of the Summary sheet
        If Not foundID Is Nothing Then 'if the ID is found, the next two lines of code execute
            lCol = Sheets("Summary").Rows(foundID.Row).Find("*", , , , xlByColumns, xlPrevious).Column 'sets the number of the last used column in the row where the ID is found
            Sheets("Daily Data").Range("B" & ID.Row & ":C" & ID.Row).Copy Sheets("Summary").Cells(foundID.Row, lCol + 1) 'copies columns B and C of the ID row to the corresponding ID row in Summary
        End If
    Next ID 'looks for the next ID
    Application.ScreenUpdating = True 'turns screen refreshing back on
End Sub

The code starts searching for the ID in row 2. I always start searches at row 2 because there is usually a column header in row 1. If you have no headers and your ID's start in row 1, the code will skip the first row. If this is the case, change this line:

For Each ID In Sheets("Daily Data").Range("A2:A" & LastRow)

to this line:

For Each ID In Sheets("Daily Data").Range("A1:A" & LastRow)

Notice that A2 was changed to A1 to tell Excel to start the search on row 1. I hope this helps. If you are still getting the problem, please let me know.

THANK YOU SO VERY MUCH!!! I really appreciate your time to help and explain.

 

[mumps]

My pleasure.