Updating a master spreadsheet

C

cccbzg

Board Regular
Joined
Oct 5, 2014
Messages
68
Office Version
  1. 365
Platform
  1. Windows
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
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]lname
[/TD]
[TD]fname
[/TD]
[TD]Date from DAILY DATA 1
[/TD]
[TD]CODE From DAILY DATA 1
[/TD]
[TD]Date from DAILY DATA 2
[/TD]
[TD]CODE from DAILY DATA 2
[/TD]
[TD]Date from DAILY DATA 3
[/TD]
[TD]CODE from DAILY DATA 3
[/TD]
[TD]Date from DAILY DATA 4
[/TD]
[TD]CODE from DAILY DATA 4
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]lname1
[/TD]
[TD]fname1
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]lname2
[/TD]
[TD]fname2
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]lname3
[/TD]
[TD]fname3
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]lname4
[/TD]
[TD]fname4
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]lname5
[/TD]
[TD]fname5
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]lname6
[/TD]
[TD]fname6
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]lname7
[/TD]
[TD]fname7
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8
[/TD]
[TD]lname8
[/TD]
[TD]fname8
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]lname9
[/TD]
[TD]fname9
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

DAILY DATA

[TABLE="width: 500"]
<tbody>[TR]
[TD]ID
[/TD]
[TD]Date
[/TD]
[TD]Code
[/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]09/22/15
[/TD]
[TD]VM
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]09/22/15
[/TD]
[TD]RRE
[/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]09/22/15
[/TD]
[TD]Other
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Sort by date Sort by votes
Try:
Code: 
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.
 
Upvote 0
mumps said:
Try:
Code: 
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.
Click to expand...


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.
 
Upvote 0
I've included comments with the code.
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:
Code: 
For Each ID In Sheets("Daily Data").Range("A2:A" & LastRow)
to this line:
Code: 
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.
 
Last edited:
Upvote 0
mumps said:
I've included comments with the code.
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:
Code: 
For Each ID In Sheets("Daily Data").Range("A2:A" & LastRow)
to this line:
Code: 
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.
Click to expand...

THANK YOU SO VERY MUCH!!! I really appreciate your time to help and explain.
 
Upvote 0
You must log in or register to reply here.

Similar threads

V
  • Question Question
Import and update automatically in a master sheet filtered data from daily sheets
Replies
5
Views
619
mumps
mumps
P
  • Question Question
Count names based on enrollment
Replies
2
Views
270
paulkc
P
B
  • Question Question
Append data based on Date using links
Replies
1
Views
563
etaf
etaf
S
  • Solved
Seperate Data In One Cell Across Multiple Cells
Replies
6
Views
2K
Alex Blakenburg
A
M
  • Question Question
Data update based on periodical base date data change
Replies
0
Views
332
Muthukrishnan V
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