VBA: Copy/Paste cells from Old Data Sheet into new Data Sheet based on criteria

[Lres81715]

Hello everyone,

I have a massive project that has sheets that need to be updated at least once a week. I have an existing VBA code that pulls certain columns from the Database and pastes them into a new sheet. "CalNew"
The worksheet from the previous week "CalOld" has information that is manually entered all throughout the week and it needs to be carried over to "CalNew" (Note: This sheet will eventually be renamed CalOld by the end of the week to continue the process the following week) . I currently use a MATCH/INDEX INDIRECT formula to match the data from both sheets. However, I want to automate this process as my workflow just tripled.

I've attached a sample of the worksheet in question but I'll be using this same formula for nearly 30 new sheets with a similar setup but different criteria and identifiers for each project.

https://app.box.com/s/zu3oow4j6ii11igfrtn5f4tw1augxdn7

I don't need anything fancy. The simpler the better as I'm only beginner level VBA.

Long story short, I want a VBA code that does a Match/Index function but created to be edited in a simple way. If you can add 'comments and notes along the way, I would be forever grateful! =) thanks all.

 

[Lres81715]

Additional Notes that might be important:
As the week progresses, the status of some orders will drop off the report (IE: Future CalNew) as it moves into the next stage. Also: New orders will then appear on CalNew in various positions unique to a particular filter sort. They will then need to be manually updated and most likely need to be copied over as CalOld into CalNew. Thus the cycle repeats.
Column B on both CalNew and CalOld are the unique identifiers that I use to match the data. L M N O P are the columns that are manually entered and updated each week that need to be carried over. This report normally has between 30-100 rows of revolving data at any given data depending on the time of the month. Hope that helps in clarifying any questions you might have

 

[JLGWhiz]

Try this. Copy to standard code module 1

Sub copyStuff()
Dim sh1 As Worksheet, sh2 As Worksheet, c As Range, fn As Range 'declare variables
Set sh1 = Sheets("CalNew") 'initialize variables
Set sh2 = Sheets("CalOld")
    With sh1
        For Each c In .Range("B2", .Cells(Rows.Count, 2).End(xlUp)) 'initiate loop to walk down col B of CalNew
            Set fn = sh2.Range("B:B").Find(c.Value, , xlValues, xlWhole) 'look for matched value, column B
                If Not fn Is Nothing Then 
                    fn.Offset(0, 11).Resize(1, 5).Copy c.Offset(0, 11) 'If match found, copy the five columns to other sheet.
                End If
        Next
    End With
End Sub

 

[JLGWhiz]

Change this line to:

fn.Offset(0, 10).Resize(1, 5).Copy c.Offset(0, 10)

 

[Lres81715]

This looks like it will do the trick.. I'll confirm it tomorrow. Thanks JLGWhiz!

 

[JLGWhiz]

This looks like it will do the trick.. I'll confirm it tomorrow. Thanks JLGWhiz!

You're welcome,
Regards, JLG

 

[Lres81715]

Follow up,

Yes this works great! thanks again!