paxrack.blogg.se

'Specify sheet name in which the data is storedħ. To change columns which need to be copied, make changes in this line of code. In the program below, it copies columns A, D and F Suppose you want to copy specific columns instead of all the columns in your sheet. If Not GetWorksheet(x.Text) Is Nothing ThenĦ. Caution : It removes the existing worksheets which were created via macro. (ii) Add the following lines of code after 'For Each x In Range(, Cells(Rows.Count, "AA").End(xlUp))'. (i) Add the following function before sub filter().įunction GetWorksheet(shtName As String) As Worksheet To workaround this issue, follow the steps below. If you run the macro more than once, the error would occur as sheets already exist. Change the below line of code in the program.ĥ. Specify name of the sheet in which data is stored. Sheets.Add(After:=Sheets(Sheets.Count)).Name = x.ValueĮnd Sub How to Filter and Paste Values to New Workbookġ.

Sheets(sht).Range("F1:F" & last).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("AA1"), Unique:=Trueįor Each x In Range(, Cells(Rows.Count, "AA").End(xlUp)) Set rng = Sheets(sht).Range("A1:F" & last) Last = Sheets(sht).Cells(Rows.Count, "F").End(xlUp).Row 'change filter column in the following code 'specify sheet name in which the data is stored In this case, it creates four worksheets - 1, 2, 3, 4 as these are unique values in column Rank (column F). This macro would filter a column and paste distinct values to the sheets with their respective names. In the following excel macro, it is assumed a filter is applied on column F (Rank) and data starts from cell A1.Įxcel Macro : Filter and Paste Unique Values to New Sheets