VBA macro to format excel worksheet.

[AllDay1]

Im new to building macros and coding. Ultimately im trying to figure out how to copy data from a dynamic range. The number of rows that need to be copied changes every time.

What my Macro is doing in this worksheet. I have a add-in that pulls a data dump to a tab("raw data") each time you open this worksheet. Its a dynamic data pull, where it looks at the date of the latest row in my tab("formated data") and pulls the raw data from that date to the current date/time. I have a format data button on my main tab(this tab is a vlookup that presents all relevant info needed) and that is all a person would have to press to ultimately get the updated data daily.

The Macro does this

• Filter the new raw data dump by column P for a status. This eliminates hundreds of rows that we wont need. (using raw data tab)
• here is where i need to dynamically copy this data Starting from Cell A2 to P_.
  • The issue is the row value is different every time. so i need to dynamically select here.
  • I only want to copy the filtered data and not all of the unfiltered data. so i paste this into another tab and then cut it.
  • What I attempted to do was use shift,end,right and then shift,end,down. However the recorded macro hardcodes the row value and does not dynamically change.
• From here after i copy this data and shuffle it to final destination...
  • I copy the filtered data from tab "raw data"
  • paste into a tab named "temp"
  • cut this data to then insert it into my tab "formatted data" between rows 1 and 2.

Essentially im dumping data, filtering, and then moving the filtered data to a tab im essentially appending. Its keeping a running log of the last years relevant information. Vs taking a long time to pull 365 days of data everytime you want to view it.

Here is my current VBA Code.

Sub FormatData()
'
' FormatData Macro
'

'
    Sheets("Raw Data").Select
    Rows("1:1").Select
    Selection.AutoFilter
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$P$124").AutoFilter Field:=11, Criteria1:="=STATUS" _
        , Operator:=xlOr, Criteria2:="=Status of sample"
    Range("A1").Select
    Selection.End(xlToRight).Select
    Selection.End(xlDown).Select
    Range("P121:P124").Select
    Range("P124").Activate
    Range(Selection, Selection.End(xlUp)).Select
    Range(Selection, Selection.End(xlToLeft)).Select
    Range("A5:P124").Select
    Range("P124").Activate
    Selection.Copy
    Sheets("temp").Select
    ActiveWindow.LargeScroll ToRight:=-1
    Range("A1").Select
    ActiveSheet.Paste
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlDown)).Select
    Application.CutCopyMode = False
    Selection.Cut
    Sheets("Formated Data").Select
End Sub

 

[HaHoBe]

Hi,

the sample adds a new sheet at the end of the workboo (for demonstration) and dumps the filtered area there:

Sub FormatData()
' https://www.mrexcel.com/board/threads/vba-macro-to-format-excel-worksheet.1232812/
'
' FormatData Macro
'

'
    With Sheets("Raw Data")
      'turn any filter off
      If .AutoFilterMode Then .AutoFilterMode = False
      'get all cells connected to A1
      .Range("A1").CurrentRegion.AutoFilter Field:=11, Criteria1:="=STATUS", Operator:=xlOr, Criteria2:="=Status of sample"
      'check the number of rows being filtered
      If .Cells(.Rows.Count, "A").End(xlUp).Row > 2 Then
        'copy the visible rows
        .Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy
        'add a new sheet after all worksheets and paste
        Worksheets.Add after:=Worksheets(Worksheets.Count)
        ActiveSheet.Paste
      End If
    End With
End Sub

Ciao,
Holger