Matching Worksheets

Darren Smith

Well-known Member
Joined
Nov 23, 2020
Messages
631
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
 
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)
 
Upvote 0

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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?
 
Upvote 0
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
 
Upvote 0
So anything on the Master gets copied to the first blank row on Analysis, without comparing any cells?
 
Upvote 0
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.
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,251
Members
448,556
Latest member
peterhess2002

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