# Matching Worksheets

#### Darren Smith

##### Active Member
Hi all

Trying to match worksheets see below. Can`t make it work??

VBA Code:
If ListBox4 = "Fills Job Card Master Sheet into Time Analysis Sheet" Then

Dim Ary As Variant, Nary As Variant
Dim wsDest As Worksheet
Set wsDest = Worksheets("Job Card with Time Analysis")
Dim rng As Range
Set rng = wsDest.Range("A10:N")
Dim Dic As Object
Dim l As Long

Set Dic = CreateObject("scripting.dictionary")

With Sheets("Job Card Master")

Ary = .Range("A10", .Range("A" & Rows.Count).End(xlUp)).Value2
Nary = .Range("A10:N" & .Range("A" & Rows.Count).End(xlUp).Row).Value2
End With

With Sheets("Job Card with Time Analysis")

For Each rng In .Range("A10", .Range("A" & Rows.Count).End(xlUp))
Dic(rng.Value) = Empty
Next rng
End With
For l = 1 To UBound(Ary)
If Dic.Exists(Ary(r, 1)) Then Nary(r, 1) = "Yes"
Next l
Sheets("Job Card Master").Range("A10").Resize(UBound(Nary)).Value = Nary

End If

End Sub

#### Darren Smith

##### Active Member
Terribly sorry if i sound confusing. finding it difficult to put into words. Basically I am trying to work it so that whatever is written into the Master worksheet (Job Card Master), in whatever cell, ranging from A10 across to N10 & then down to bottom of sheet, is automatically copied onto the next worksheet (Job Card with Time Analysis). If any cells are left blank on the master, then they are blank on the next sheet (Job Card with Time Analysis)

### Excel Facts

Square and cube roots
The =SQRT(25) is a square root. For a cube root, use =125^(1/3). For a fourth root, use =625^(1/4).

#### Fluff

##### MrExcel MVP, Moderator
Do they need to written to a specific row? So for instance does it need to find the value in col A of the master in col A of Analysis & then copy across to that row?
If so what happens if the value in col doesn't already exist?
Also do you need this to happen whenever any cell is manually changed, or on the click of a button?

#### Darren Smith

##### Active Member
Line 1: It needs to find for instance, the value in col A of master & copy that across to col A of the Analysis but apply this to the whole sheet - col A across to col N, and then down to bottom of sheet
Line 2: If there is nothing in the cell on the Master, then nothing copies over to the Analysis. It's just a straightforward copy from one sheet to the other.
Line 3: Needs to happen on click of a button

#### Fluff

##### MrExcel MVP, Moderator
So anything on the Master gets copied to the first blank row on Analysis, without comparing any cells?

#### Darren Smith

##### Active Member
Yes that`s right. You may be wondering why i'm not using copy & paste. it's because sometimes some of the cells are merged on one sheet & not on the other - everytime i try & do copy & paste it fails. but i find it works with the match facility - have proved it by taking one cell and matching it to another cell of the other sheet.

#### Fluff

##### MrExcel MVP, Moderator
Merged cells are an abomination & should be avoided like the plague, they will cause you nothing but problems.
I have no idea what you mean by
it works with the match facility - have proved it by taking one cell and matching it to another cell of the other sheet.
As I do not work with merged cells (for the reasons stated above) I'm afraid I cannot help.

Replies
2
Views
126
Replies
17
Views
304
Replies
5
Views
45
Replies
3
Views
172
Replies
29
Views
1K

1,130,083
Messages
5,639,977
Members
417,120
Latest member
Pavithra devi

### 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.

### Which adblocker are you using?

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

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