I am struggling so hard! Please help me! [
Version of the program


Microsoft Excel for Office 365 ProPlus – Excel version: 1908

What I need it to do:I need a VBA macro that will create a new sheet with all Page names, Page Numbers, and Page URLs from another sheet.
In theory it would work something like this: the vba code will find the first cell with text that contains “Site Page” select that cell and the cell next to it that starts with “Page Name”; AND find the next cell under the Site Page that contains “Page URL” select that cell and the cell next to it on the right with the actual URL. Copy the selection range and paste into a new sheet titled “FINAL URL LIST” in the next empty row.
Few key things it needs to do.


When first running the code, it needs to check if the sheet “Final URL List” exists, if it does, then clear it all and add Date and time stamp into A1. If does NOT exist, then create the tab, and add Date and time stamp into A1
Metadata tab layout changes from user to user based on there preference, so the VBA code cannot rely on find first instance, and offset copy. It needs to dynamically search for Site Page # to select and then the Page URL to select then copy and paste
The sheet named “Final URL List” will exist in all variations of the workbook.
The document is setup for SEO to create search engine listing text per webpage, and Social Media to set the OG sharing tags. We only want to grab the Site Page line that is correlated to the SEO metadata and not Open Graph stuff. So the vba will need to skip every other found row with text that contains “Site Page:”

Sample data (before and after sample worksheets, added as attachments)

Below is the workbook i have been trying to get to work – I have gotten the code close to working a few times, but then the loop will run away, or It will copy but not paste. Weird stuff keeps happening and I am begging for help!


Here is the workbook -> MetaCheck+3.0+-+Auto+Generate-SummaryTab-of-Page-name,-url,-and-number.xlsm




Here is the VBA i have so far for searching the sheet for cells that contain "site page", selecting that cell and the 1 to the right, copying and pasting to new sheet.

Sub LoopThroughUntilBlanks2()'this one is getting really close, just not pasting right
'UpdatebyExtendoffice20161222
      ' Select cell A2, *first line of data*.
      Dim xrg As Range
      Dim textToSearchFor As String
      On Error Resume Next
      
      Set xrg = Range("a2")
Worksheets("HD metadata").Activate
      xrg.Cells(2, 0).Select ' Set Do loop to stop when 3 consecutive empty cells are reached.
'      Application.ScreenUpdating = False


      Do Until IsEmpty(ActiveCell) And IsEmpty(ActiveCell.Offset(1, 0))
         ' Insert your code here.
         


textToSearchFor = "site page:"


Cells.Find(What:=textToSearchFor, After:=ActiveCell, LookIn:=xlValues, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, SearchFormat:=False).Select
        
        'Select and copy addtinoal cells to the found cell
    Range(ActiveCell.Offset(0, 0), ActiveCell.Offset(0, 1)).Copy
    'Selection.Copy
    
    
    'copy the selection to last empty row of another worksheet
    
   Worksheets("sheet2").Activate
            'find the last row of data in the worksheet, and paste below
    Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial (xlPasteAll)
    Worksheets("HD metadata").Activate
         ' Step down 2 rows from present location.
         ActiveCell.Offset(2, 2).Select
      Loop
      MsgBox ("empty")
      'Application.ScreenUpdating = False
      
End Sub
Here is an example of the basic output I need -> https://i.imgur.com/Kf2E7xf.jpg
To make it absolutely perfect -> Here is an example of how the data could be processed to really help users read through by putting the data into a table -- screenshot
Example-Output-from-vba.jpgIdeal-output-from-vba.jpg

Here is the VBA i have for chgecking if a specific worksheet exist, and if not, creates one, if it does exist, it clears and sets datea and time in A1 - this is fully working, but im not sure how to combine the VBA.
Function CreateSheetIf(strSheetName As String) As BooleanDim wsTest As Worksheet
CreateSheetIf = False
Set wsTest = Nothing
On Error Resume Next
Set wsTest = ActiveWorkbook.Worksheets(strSheetName)
On Error GoTo 0
 
If wsTest Is Nothing Then
 CreateSheetIf = True
 Worksheets.Add.Name = strSheetName
End If
End Function
Sub Test()
'Create worksheet "Bob" if it doesn't exist. Display Message box if
'sheet is created.
If CreateSheetIf("FINAL URL LIST") Then
 MsgBox ("Welcome to the workbook Bob!")
End If




End Sub
Politeness and gratitude

This is my first time posting, even though I have relied on these forms to help me forever I hope someone can actually help with this specific need. I will be in forever debt to you as you will save my sanity and I will be obligated to begin contributing if someone can pretty please help me! I would appreciate any help at all!

Please let me know if you have any suggestions, can help, or see errors. Thank you so much for anyone who helps!