Loop all macros every time button is clicked

powertothecreators

New Member
Joined
May 20, 2021
Messages
1
Office Version
  1. 365
Platform
  1. Windows
how do I loop a sub button click () so it will run data again using the same macros, but populate information in the next row down?

The file that it runs is an SQL, but I need the data to go for example line C3 down the column to C4

(Module two)
VBA Code:
Sub Button2_Click()
    Call TXTdATA   'Macro1
    Call rundataPIVTab 'Macro2
    Call datatrans 'Macros3
    Call valuemove 'Macro4
    Call Delete_Multiple_Columns 'delete multiple columns (columns A, B, C and D)
    Call moveselection
    Call deleteprevious 'delete previous macro
    End Sub

(Module one)
VBA Code:
Sub TXTdATA()
'
' TXTdATA Macro
' collect data from text
'
' Keyboard Shortcut: Ctrl+r
'
    With ActiveSheet.QueryTables.Add(Connection:= _
        "TEXT;C:\Users\twhiting\Downloads\Taylor's Logging 2.txt", Destination:=Range _
        ("$A$1"))
        .Name = "Taylor's Logging 2"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = False
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePromptOnRefresh = False
        .TextFilePlatform = 437
        .TextFileStartRow = 22
        .TextFileParseType = xlFixedWidth
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = False
        .TextFileSpaceDelimiter = False
        .TextFileColumnDataTypes = Array(9, 1, 1, 1, 1)
        .TextFileFixedColumnWidths = Array(39, 8, 7, 24)
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=True
    End With
End Sub
Sub rundataPIVTab()
'
' rundataPIVTab Macro
' run data into a pivot table
'
' Keyboard Shortcut: Ctrl+t
'
    Cells.Select
   ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "Data!R2C1:R1048576C4", Version:=6).CreatePivotTable TableDestination:= _
        "Data!R13C13", TableName:="PivotTable6", DefaultVersion:=6
    Sheets("Data").Select
    Cells(13, 13).Select
    ActiveWindow.SmallScroll Down:=-12
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("-------")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("------")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable6").AddDataField ActiveSheet.PivotTables( _
        "PivotTable6").PivotFields("------"), "Count of ------", xlCount
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("-------------------")
        .Orientation = xlColumnField
        .Position = 1
    End With
    ActiveSheet.PivotTables("PivotTable6").PivotFields("-------------------"). _
        AutoGroup
    ActiveWindow.SmallScroll ToRight:=5
    With ActiveSheet.PivotTables("PivotTable6").PivotFields("-------")
        .PivotItems("(blank)").Visible = False
     End With
End Sub

Sub Delete_Multiple_Columns()
    'delete multiple columns (columns A, B, C and D)
     Sheets("Data").Columns("A:D").Delete
    
End Sub


Sub datatrans()
'
' datatrans Macro
'
' Keyboard Shortcut: Ctrl+y
'
    Selection.Copy
    Sheets("Data").Select
    Range("C3:M3").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
End Sub

Sub valuemove()
'
' valuemove Macro
'

'
    Range("N18:N24").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Daily Produced PR").Select
    Range("F3:C3").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Range("N3").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=SUM(RC[-11]:RC[-1])"
    Range("C3").Select
    
End Sub

Sub moveselection()
 
 Selection.Offset(1, 0).Select
 
 End Sub
 Sub deleteprevious()
'
' deleteprevious Macro
'

'
    Sheets("Data").Select
    Range("A1:O28").Select
    Range("O28").Activate
    With Selection.Interior
        .Pattern = xlNone
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.ClearContents
    Sheets("Daily Produced PR").Select
End Sub
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Forum statistics

Threads
1,141,715
Messages
5,708,049
Members
421,540
Latest member
quocbinh

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
Top