VBA macro to format excel worksheet.

AllDay1

New Member
Joined
Jan 15, 2023
Messages
6
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.
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
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Hi,

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

VBA Code:
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
 
Upvote 0

Forum statistics

Threads
1,224,226
Messages
6,177,274
Members
452,765
Latest member
Erka Gizli

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
Back
Top