Novice VBA Questions

[HLamF]

Good evening everyone,

I have always been interested in making tools that increases efficiency for my professional life. I have recently started to dabble in VBA and learning how to create tools that increases productivity. Being new, I have two main questions to ask the community here on MrExcel.

1. How did everyone get so good at evaluating/creating macro and VBA? Over the past year I have been getting better at creating formulas within spreadsheets but always found VBA to be extremely challenging. Is there any tips or websites you would recommend if I'm a complete novice at this?
2. I recently have a work project that involves breaking apart a colleague's export into multiple tabs within the workbook. The current macro I have splits the entire Range evenly but I was just told that each section could vary in row amount. (Ex: Sheet 1 would have 20 rows, Sheet 2 would have 13, Sheet 3 have 50, etc).

Can someone please give me advice on how to approach this module so it splits the worksheet up based on a specific value? (Ex: Split each section based on the phrase "Page X out of X")

Sub SplitData()'Updated010719
Dim WorkRng As Range
Dim xRow As Range
Dim SplitRow As Integer
Dim xWs As Worksheet
On Error Resume Next
xTitleId = "TestRowBreak"
Set WorkRng = Application.Selection
Set WorkRng = Application.InputBox("Range", xTitleId, WorkRng.Address, Type:=8)
SplitRow = Application.InputBox("Split Row Num", xTitleId, 5, Type:=1)
Set xWs = WorkRng.Parent
Set xRow = WorkRng.Rows(1)
Application.ScreenUpdating = False
For i = 1 To WorkRng.Rows.Count Step SplitRow
    resizeCount = SplitRow
    If (WorkRng.Rows.Count - xRow.Row + 1) < SplitRow Then resizeCount = WorkRng.Rows.Count - xRow.Row + 1
    xRow.Resize(resizeCount).Copy
    Application.Worksheets.Add after:=Application.Worksheets(Application.Worksheets.Count)
    Application.ActiveSheet.Range("A1").PasteSpecial
    Set xRow = xRow.Offset(SplitRow)
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

 

[HLamF]

Good morning,

Bumping to see if anyone knows how I would approach this. Limiting this bump to 1 a day per forum rules. Thanks