Provide for the coders:
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.
Endless loop
Sample data (before and after sample worksheets, add as attachments here)
Here is the workbook I have been working on – 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!
If the link doesn’t work -> https://cloud.jettlifetech.com/excel-vba/MetaCheck+3.0+-+Auto+Gen+Pages+and+URLs.xlsm
Here is an example of the basic output I need ->
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
Here is the VBA i have for checking 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.
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 and the community as you will save my sanity and I will feel obligated to begin contributing to these forms if someone can pretty please help me! I would appreciate any help at all!
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:”
Endless loop
Sample data (before and after sample worksheets, add as attachments here)
Here is the workbook I have been working on – 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!
If the link doesn’t work -> https://cloud.jettlifetech.com/excel-vba/MetaCheck+3.0+-+Auto+Gen+Pages+and+URLs.xlsm
VBA Code:
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 SubHere is an example of the basic output I need ->
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
Here is the VBA i have for checking 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.
VBA Code:
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 SubThis 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 and the community as you will save my sanity and I will feel obligated to begin contributing to these forms if someone can pretty please help me! I would appreciate any help at all!
