VBA Coding Help
Hi Everyone:
I need some help with VBA coding: Here is what I need: Search all sheets in Workbook1 in Column B. Cell A1 in "TEST SHEET" is "XXX" If Column B of All Sheets doesn't have "XXX" in it, Then copy Cell A1 of those sheets to Column A of TEST SHEET, starting with Row 2. I tried using the "<" but it doesn't do what I want it to do. It copies Everything else when i doesn't match, but I need is if "XXX" Not EXIST, just copy Cell A1 of that sheet to TEST SHEET. Hope I explain myself well. Thanks in advance. Neon520 |
VBA Coding Help
Hi
I think this should do it: Sub Neon() Dim TargetSh As Worksheet Set TargetSh = Worksheets("TestSheet") SearchVal = TargetSh.Range("A1").Value For Each sh In ThisWorkbook.Sheets If sh.Name < TargetSh.Name Then Set f = sh.Columns("B").Find(what:=SearchVal) If f Is Nothing Then sh.Range("A1").Copy Destination:=TargetSh.Range _ ("A" & Rows.Count).End(xlUp).Offset(1, 0) End If End If Next End Sub Regards, Per "Neon520" skrev i meddelelsen ... Hi Everyone: I need some help with VBA coding: Here is what I need: Search all sheets in Workbook1 in Column B. Cell A1 in "TEST SHEET" is "XXX" If Column B of All Sheets doesn't have "XXX" in it, Then copy Cell A1 of those sheets to Column A of TEST SHEET, starting with Row 2. I tried using the "<" but it doesn't do what I want it to do. It copies Everything else when i doesn't match, but I need is if "XXX" Not EXIST, just copy Cell A1 of that sheet to TEST SHEET. Hope I explain myself well. Thanks in advance. Neon520 |
VBA Coding Help
Sub FindMissingSheets()
Data = Sheets("TEST SHEET").Range("A1") RowCount = 2 For Each sht In Sheets If UCase(sht.Name) < "TEST SHEET" Then Set c = sht.Columns("B").Find(what:=Data, _ LookIn:=xlValues, lookat:=xlWhole) If c Is Nothing Then With Sheets("TEST SHEET") .Range("A" & RowCount) = sht.Range("A1") .Range("B" & RowCount) = sht.Name End With RowCount = RowCount + 1 End If End If Next sht "Neon520" wrote: Hi Everyone: I need some help with VBA coding: Here is what I need: Search all sheets in Workbook1 in Column B. Cell A1 in "TEST SHEET" is "XXX" If Column B of All Sheets doesn't have "XXX" in it, Then copy Cell A1 of those sheets to Column A of TEST SHEET, starting with Row 2. I tried using the "<" but it doesn't do what I want it to do. It copies Everything else when i doesn't match, but I need is if "XXX" Not EXIST, just copy Cell A1 of that sheet to TEST SHEET. Hope I explain myself well. Thanks in advance. Neon520 |
All times are GMT +1. The time now is 04:07 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com