Excel VBA Macro: Autofilter, Copy, Paste to New Sheet and Save to Specified Location (Dynamic Range)
HTML-код
- Опубликовано: 30 сен 2021
- Excel VBA Macro: Autofilter, Copy, Paste to New Sheet and Save to Specified Location (Dynamic Range). In this video, we write code that allows a user to filter data based on cell value, copy the visible range dynamically, paste to another sheet, and then save the data on the sheet in its own workbook to a specific location. We also go over how to clear data and using ScreenUpdating and DisplayAlerts to prevent random popups and limit excessive visuals while running the macro.
Code:
Sub filter_copy_paste_save()
Dim region As String
Dim raw As Worksheet
Dim out As Worksheet
Dim count_col As Integer
Dim count_row As Integer
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Set raw = ThisWorkbook.Sheets("Raw Data")
Set out = ThisWorkbook.Sheets("Output")
region = raw.Range("F2").Text
'clear pervious data
out.Cells.ClearContents
'determine the size of the range
raw.Activate
count_col = WorksheetFunction.CountA(Range("A4", Range("A4").End(xlToRight)))
count_row = WorksheetFunction.CountA(Range("A4", Range("A4").End(xlDown))) + 3
'filter data on Raw Data tab
raw.Range("A4").AutoFilter field:=2, Criteria1:=region
'copy/paste to Output tab
raw.Range(Cells(4, 1), Cells(count_row, count_col)).SpecialCells(xlCellTypeVisible).Copy
out.Range("A1").PasteSpecial xlPasteValues
Application.CutCopyMode = False
'show data and remove filter
With raw
.ShowAllData
.AutoFilterMode = False
End With
'formatting Output tab
With out
.Activate
.Cells.Select
.Cells.EntireColumn.AutoFit
.Range("A1").Select
.Copy
End With
'save and close the workbook
ActiveWorkbook.SaveAs Filename:="C:\Users\greggowaffles\Documents\RUclips Videos\Test\" & _
"Region Report - " & region & ".xlsx"
ActiveWorkbook.Close
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub
Data used in this video:
gsociology.icaap.org/datauplo...
#ExcelVBA #ExcelMacro
This was one of the clearest and easiest to follow tutorials on this subject that I’ve seen. Thank you!! I hope I can be successful at making a few minor changes to what data is selected and where it’s saved …. (only copying a few of the rows and columns to another worksheet within the same workbook, and using an input box to allow the user to select that specific range which gets copied). I’ll be checking out your other videos and subscribing. Keep up the nice work!!
So glad to hear that! Thanks for the feedback!! I’ll definitely include that scenario in a future video
@@greggowaffles I've been retired for over 12 years and you sound like you might be in your mid-20's???.... I was curious what route you took to be this proficient in VBA? I used excel a lot in business but never had a real understanding of VBA and looking back I know that it would have made my life so much easier. You may have reasons why you don't post any info in the ABOUT area, but I'm guessing with your knowledge of Excel and VBA you may be losing opportunities for employment, unless you just do this for fun. Just saying.. Again thanks for the videos!
No problem! I’m actually in my early 30s, and I taught myself VBA to make my job as a financial analyst easier. I’m a data analyst now. I never made an effort to update that section, but I have no reason not to. That’s a really good point. I’ll update that today. Thanks a lot!
Thank you very much for this tutorial. I am new to VBA and I find your videos very helpful.
You’re welcome. So glad to hear that!
I really need your help, is it possible to edit your marco to be able to save every fliter region information instead of having to click in one by one?
Hey - are you able to save the same workbook with the macros? This is amazing - really helpful! I need your guidance where I have a bunch of pivot tables that need to be refreshed before saving the file name, is that possible in your macro at the end? Thanks a lot!!!
Hey this is great stuff and excellent explained! I do have a questions. How to modify the code to filter rows based on dates?
Hello i'm digging your tutorials ! Could you do more on pdf automation ?
Hello greg. I need help on how to auto filter, copy and save to new workbook if there is an the additonal worksheet wherein i have there a simple pivot table for summary. So basically i have the first sheet as raw data and the second worksheet as with pivot table summary. Hope you can see this.
Great Video! One question, how do I make the name of the sheet the same as the name of the file though? For example in your video when you save each file, as you automate the name of that file, is there a similar way to automate the name of the sheet in the bottom left corner of the excel file too?
Hi Greg, Thanks for this tutorial. And could you help me with the code instead of filtering it one by one is there a possible way to run all the region?
is there a way after you filter on the region to save that to its own tab for each instance w/in the same workbook ? thanks.
I am having problem on the Set raw details
It has debu on it
Hi. Subscriber here. I’ve been using this macro and it’s working great! My list is growing. How can I get the macro to auto loop down the list so I don’t have to run the macro for each item in the list?
You can add another tab that has your list. Use another count_row variable to get the length of the list and then use a for loop that starts above the “‘filter data on Raw Data tab” line and ends at the bottom of the “ActiveWorkbook.Close” line. You’ll also want to update the region variable inside of the loop each time. Hope that helps! I’ll make a video on that and post it in the next few days
@@greggowaffles thank you I will watch the video! I appreciate this a ton!!
No problem at all! I’ll let you know when I’ve uploaded it
@@EatGyroSandwiches Hi same here do you have the workaround on this? Thank you!