Matching Worksheets

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
419
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Nov 23, 2020
Messages
419
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
56,816
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Nov 23, 2020
Messages
419
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
56,816
Office Version
  1. 365
Platform
  1. Windows
So anything on the Master gets copied to the first blank row on Analysis, without comparing any cells?
 

Darren Smith

Active Member
Joined
Nov 23, 2020
Messages
419
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jun 12, 2014
Messages
56,816
Office Version
  1. 365
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
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.
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
Top