How to fetch multiple values from a plan

Question

How to fetch multiple values from a plan

Navigation

#1 by (1 votes)

1

Hello, I have a spreadsheet with lots of data, and I would like to separate them each in one worksheet by category, for example: all products that are as of September are shown in another worksheet or all product codes that are repeated are placed in a separate worksheet, there is some function for this as if it were a procv but for a set of values

excel

asked by anonymous 13.07.2016 / 02:57

1 answer

Update table with data from another table by decreasing character NullPointerException while deleting log

score 1 · Accepted Answer

You can do this in a " macro " of Excel . The code below in VBA does the essentials of what you need and is similar to the macro.

The first step is to sort the worksheet according to the category you need to separate. If you can not change the original worksheet, copy the data to another worksheet and sort it (the code shows how to copy it to another worksheet).

Once you have classified data, it's easy to see that data range matches the category you want to separate.

I called Sheet1 the data sheet and Sheet2 to receive the data for a category.

See that I use two variables of type " string ", one with the track to copy and another with the track to paste.

Sheets("Planilha1").Activate
'Ativa a Planilha de dados

Planilha.Rows(FaixaParaCopiar).Select
'Seleciona faixa a copiar 

Selection.Copy
'Copia os dados selecionados para a área de transferência

Sheets("Planilha2").Activate
'Ativa a Planilha para receber uma categoria de dados

Planilha.Rows(FaixaOndeColar).Select
'Seleciona e prepara para colar

ActiveSheet.Paste
'Cola na Planilha da categoria escolhida

You can test it works!

You do not have a "function" that does this at once, there are several solutions besides VBA, such as using "macros" or "PivotTable" (worth knowing, if any).