SOLVED

reorganize data from one worksheet to another

%3CLINGO-SUB%20id%3D%22lingo-sub-1477721%22%20slang%3D%22en-US%22%3Ereorganize%20data%20from%20one%20worksheet%20to%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1477721%22%20slang%3D%22en-US%22%3E%3CP%3EI%20need%20a%20macro%20to%20move%20the%20data%20on%20one%20worksheet%20to%20a%20table%20on%20another%20worksheet%20without%20me%20copy%20and%20pasting%20each%20section.%20I%20have%20uploaded%20a%20sample%20that%20hopefully%20explains%20what%20I%20am%20trying%20to%20do%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1477721%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EExcel%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EFormulas%20and%20Functions%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EMacros%20and%20VBA%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1478386%22%20slang%3D%22en-US%22%3ERe%3A%20reorganize%20data%20from%20one%20worksheet%20to%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1478386%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F144248%22%20target%3D%22_blank%22%3E%40Alan%20Skelly%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EIn%20case%20you%20are%20using%20a%20recent%20version%20of%20Excel%20and%20are%20on%20the%20right%20channel%2C%20you%20could%20consider%20using%20FILTER%20in%20A3%20and%20copy%20it%20once%2C%20to%20each%20of%20the%2014%20areas%20(2%20-15)%20into%20the%20same%20(relative%20position).%20Thus%20A24%20for%20area%202%20etc.%20See%20attached.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENo%20need%20for%20VBA.%3C%2FP%3E%3CPRE%20class%3D%22lia-code-sample%20language-excel%22%3E%3CCODE%3E%3DFILTER(DATA1!%24A%241%3A%24AV%241000%2CDATA1!%24C%241%3A%24C%241000%3DA1%2C%22%22)%3C%2FCODE%3E%3C%2FPRE%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3E%3CEM%3EThis%20function%26nbsp%3Bis%20currently%20available%20to%20Microsoft%20365%20subscribers%20in%20Current%20Channel.%20It%20will%20be%20available%20to%20Microsoft%20365%20subscribers%20in%20Semi-Annual%20Enterprise%20Channel%20starting%20in%20July%202020.%3C%2FEM%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1479155%22%20slang%3D%22en-US%22%3ERe%3A%20reorganize%20data%20from%20one%20worksheet%20to%20another%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1479155%22%20slang%3D%22en-US%22%3E%3CP%3EThank%20You%20I%20already%20got%20the%20VBA%20done%2C%20but%20that%20works%20and%20will%20come%20in%20useful%20for%20various%20things%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F403176%22%20target%3D%22_blank%22%3E%40Riny_van_Eekelen%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-BODY%3E
Highlighted
Occasional Contributor

I need a macro to move the data on one worksheet to a table on another worksheet without me copy and pasting each section. I have uploaded a sample that hopefully explains what I am trying to do

2 Replies
Highlighted
Solution

@Alan Skelly 

In case you are using a recent version of Excel and are on the right channel, you could consider using FILTER in A3 and copy it once, to each of the 14 areas (2 -15) into the same (relative position). Thus A24 for area 2 etc. See attached.

 

No need for VBA.

=FILTER(DATA1!$A$1:$AV$1000,DATA1!$C$1:$C$1000=A1,"")

 

This function is currently available to Microsoft 365 subscribers in Current Channel. It will be available to Microsoft 365 subscribers in Semi-Annual Enterprise Channel starting in July 2020.

Highlighted

Thank You I already got the VBA done, but that works and will come in useful for various things@Riny_van_Eekelen