VBA problem. Either really easy or well hard

Hile_Troy

New Member
Joined
Sep 27, 2011
Messages
15
This problem may be easier to solve than describe, I hope so... anyway:
I have data in 2 workbooks, call them book1.sheet1, and book2.sheet1. Book2 has very little data (at present only 4 rows, but this will be different each time the macro is run), book1 has roughly 780 rows. I need to:-
1/ Find each record in book1 that matches the first entry in column B in book 2 and where column B in book1 = "NEW".
2/ For each record found above, copy the contents of the first entry in column C book2 to column B in the same row as the record found above.
3/ Do both of the above for each row in book2.

I have read 3 days worth of guides, posts etc. and been unable to find anything that even gets me close. I am not good enough in vba to write my own code, but have become quite adept at reading and adapting code.

Any help on this would be massively appreciated.
 

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
Doesn't seem to be too hard imho...
Don't have time right now, but if someone else hasn't jumped in before tonight, I will give it a go...

Topics move very fast in this forum, it happens that some are overlooked, doesn't mean they are too difficult :)
 
Upvote 0
It is difficult to write the code without examples of your spreadsheets, as such this is untested

Code:
Public Sub test()
 
Dim Wb1 As Workbook, Wb2 As Workbook
Dim sht As Worksheet
Set Wb1 = Workbooks("Book1")
Set Wb1 = Workbooks("Book2")
Set sht = Worksheets("Sheet1")

LRow1 = Wb1.sht.Range("A" & Rows.Count).End(xlUp).Row
LRow2 = Wb1.sht.Range("A" & Rows.Count).End(xlUp).Row

With Wb1
    For i = 1 To LRow1
    
        If Wb1.sht.Cells(i, 2).Value = "New" Then
            If Wb1.sht.Cells(i, 1).Value = Wb2.sht.Cells(1, 2).Value Then
                Wb2.sht.Cells(1, 3).Copy Destination:=Wb1.sht.Cells(i, 2)
            End If
        End If
    
    
    
    
    Next i
End With

End Sub

The workbooks must be named book1 and and book2, and the sheets sheet1.

If you have issues, upload your workbooks and I will have a further look
 
Upvote 0
Hi jameo

Many thanks for your code, it I was most of the way through having a go at writing it myself when I got your message, but it did inspire me on how to finish it and make it work. The code I am using is below, it has an extra If section because the row in book1 changes part of the way down the sheet. (Had a minor panic until I noticed this)

Sub ModuleNumbersToCurriculum()
Dim sRow As Long
Dim dRow As Long
Dim sSheet As Worksheet
Dim dSheet As Worksheet
Set sSheet = Workbooks("DifferenceReport.xls").Sheets("New Modules Report")
Set dSheet = Workbooks("compareCurriculumnew.xls").Sheets("Curriculum Overview")
sRow = 1
dRow = 9
Do Until sSheet.Cells(sRow, "D") = ""
Do Until dSheet.Cells(dRow, "N") = "stop"
If dSheet.Cells(dRow, "B") = "NEW" Then
If dSheet.Cells(dRow, "N") = sSheet.Cells(sRow, "D") Then
sSheet.Cells(sRow, "C").Copy Destination:=dSheet.Cells(dRow, "C")
End If
If dSheet.Cells(dRow, "P") = sSheet.Cells(sRow, "D") Then
sSheet.Cells(sRow, "C").Copy Destination:=dSheet.Cells(dRow, "C")
End If
End If
dRow = dRow + 1
Loop
sRow = sRow + 1
dRow = 9
Loop
End Sub


Anyway, thanks again.
 
Upvote 0

Forum statistics

Threads
1,213,530
Messages
6,114,162
Members
448,554
Latest member
Gleisner2

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