• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract Data in Different Sheet

Solocharles

New Member
I am looking for VBA code to extract data from Input sheet to output sheet in certain format as mentioned in the file.
 

Attachments

  • Test_Extract Data in Different Sheet.xlsx
    11.6 KB · Views: 8
Using Power Query, here is an alternative means:

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"Data", each _, type table [Name=text, #"Sale%"=number, Country=text, Rank=number, Sub=text, Ticket Numbers=number, Lan=text, Cities=text, School=text, Home=text, Colour=text, Road=text, Email ID=text]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Ticket Numbers"}, {"Data.Ticket Numbers"})
in
    #"Expanded Data"
 

Attachments

  • Test_Extract Data in Different Sheet.xlsx
    26.4 KB · Views: 3
• First correct the cell Output!B1 which must be exactly the same header name than in worksheet Input.​
• Paste this VBA beginner starter (like any Excel beginner operating manually with the Macro Recorder) to the Output worksheet module
(or in a standard module but then Output must be the active worksheet when this VBA procedure is launched) :​
Code:
Sub Macro1()
    [Input!A1].CurrentRegion.AdvancedFilter xlFilterCopy, , [A1:B1], True
    [A1].CurrentRegion.Sort [A1], xlAscending, [B1], , xlAscending, Header:=xlYes
End Sub
Do you like it ? So thanks to click on bottom right Like !​
 
• First correct the cell Output!B1 which must be exactly the same header name than in worksheet Input.​
• Paste this VBA beginner starter (like any Excel beginner operating manually with the Macro Recorder) to the Output worksheet module
(or in a standard module but then Output must be the active worksheet when this VBA procedure is launched) :​
Code:
Sub Macro1()
    [Input!A1].CurrentRegion.AdvancedFilter xlFilterCopy, , [A1:B1]
    [A1].CurrentRegion.Sort [A1], xlAscending, [B1], , xlAscending, Header:=xlYes
End Sub
Do you like it ? So thanks to click on bottom right Like !​

thank you the code but this is not what I need, this code just copy and details from input to output. Please refer to attachment what I have provided.
 
Using Power Query, here is an alternative means:

Code:
let
    Source = Excel.CurrentWorkbook(){[Name="Table1"]}[Content],
    #"Grouped Rows" = Table.Group(Source, {"Name"}, {{"Data", each _, type table [Name=text, #"Sale%"=number, Country=text, Rank=number, Sub=text, Ticket Numbers=number, Lan=text, Cities=text, School=text, Home=text, Colour=text, Road=text, Email ID=text]}}),
    #"Expanded Data" = Table.ExpandTableColumn(#"Grouped Rows", "Data", {"Ticket Numbers"}, {"Data.Ticket Numbers"})
in
    #"Expanded Data"


I am looking excel VBA code to get the results not the Power Query
 
this code just copy and details from input to output.
Wrong !​
As a starter you can mod it …​
Without any accurate explanation so just activate the Macro Recorder and operate manually​
then if you need to amend your code so post it here with a good enough elaboration …​
 
Back
Top