• Skip to main content
  • Skip to header right navigation
  • Skip to site footer

My Online Training Hub

Learn Dashboards, Excel, Power BI, Power Query, Power Pivot

  • Courses
  • Pricing
    • Free Courses
    • Power BI Course
    • Excel Power Query Course
    • Power Pivot and DAX Course
    • Excel Dashboard Course
    • Excel PivotTable Course – Quick Start
    • Advanced Excel Formulas Course
    • Excel Expert Advanced Excel Training
    • Excel Tables Course
    • Excel, Word, Outlook
    • Financial Modelling Course
    • Excel PivotTable Course
    • Excel for Customer Service Professionals
    • Excel for Operations Management Course
    • Excel for Decision Making Under Uncertainty Course
    • Excel for Finance Course
    • Excel Analysis ToolPak Course
    • Multi-User Pricing
  • Resources
    • Free Downloads
    • Excel Functions Explained
    • Excel Formulas
    • Excel Add-ins
    • IF Function
      • Excel IF Statement Explained
      • Excel IF AND OR Functions
      • IF Formula Builder
    • Time & Dates in Excel
      • Excel Date & Time
      • Calculating Time in Excel
      • Excel Time Calculation Tricks
      • Excel Date and Time Formatting
    • Excel Keyboard Shortcuts
    • Excel Custom Number Format Guide
    • Pivot Tables Guide
    • VLOOKUP Guide
    • ALT Codes
    • Excel VBA & Macros
    • Excel User Forms
    • VBA String Functions
  • Members
    • Login
    • Password Reset
  • Blog
  • Excel Webinars
  • Excel Forum
    • Register as Forum Member

Help with macros|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / Help with macros|VBA & Macros|Excel Forum|My Online Training Hub
Avatar
sp_LogInOut Log In sp_Registration Register
sp_Search Search
Advanced Search|Last Search Results
Search
Forum Scope




Match



Forum Options



Minimum search word length is 3 characters - maximum search word length is 84 characters
sp_Search Search
sp_RankInfo
Lost password?
sp_CrumbsHome HomeExcel ForumVBA & MacrosHelp with macros
sp_PrintTopic sp_TopicIcon
Help with macros
Avatar
lea cohen
Member
Members
Level 0
Forum Posts: 219
Member Since:
March 10, 2016
sp_UserOfflineSmall Offline
1
May 8, 2022 - 6:34 am
sp_Permalink sp_Print

Hi, thank you for the steps that need to be done with the macro,
I will note that I do not know the VBA code
Therefore, I would like to receive an answer on the order of actions required for the following result to occur - (I tried alone and failed ...)
Sheet1 has a delivery note where I enter the data
(The data currently available is for illustration purposes only)
At the end of entering the data into a delivery note, I want to press a button
Then all the data will go into the data table in the DATA sheet
Below the data written is already there.
And the date + the delivery note found in row 9 will also enter the table
And will have all the lines of the current delivery note.

Thanks for the help, thank you very much !!!! Leah

sp_AnswersTopicSeeAnswer See Answer
Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 440
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
2
May 8, 2022 - 6:07 pm
sp_Permalink sp_Print

Although you specifically asked for a macro/VBA solution, I would like you to consider PowerQuery for a task like this. It's relatively easy to extract data from the shipping certificate and then append the current information to the previously extracted information. That way you can build a history table of all shipping certificates. 

The attached file contains such a PQ solution. Change certificate numbers and shipping details in Sheet1, press Refresh All and check out the DATA tab. 

Note that I defined two named ranges to which PQ connects. One for the certificate header and one for the certificate body. 

Avatar
lea cohen
Member
Members
Level 0
Forum Posts: 219
Member Since:
March 10, 2016
sp_UserOfflineSmall Offline
3
May 9, 2022 - 12:58 am
sp_Permalink sp_Print

Hi, seems like a great solution, thanks!
I need to explain to the company how to operate it
On her file.
So I would be very grateful if you could list the steps of execution step by step
So that I can run this principle in the real file as well.
Thank you!!! Leah

Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 440
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
4
May 9, 2022 - 4:41 pm
sp_Permalink sp_Print

It depends a little bit on how accustomed you are with PQ, but when you look at the queries in the file, you should be able to follow the applied steps. The process works as follows.

First create two named ranges. One for the information on row 9 in Sheet1 and one for the block of shipping details in rows 14 to 38. I just called them "ship_hdr" and "ship_body".

Load these ranges into PQ. From the ship_hdr query I drilled down to the value in Column2 to create a variable for the ship_date (right-click and Drill Down). A similar procedure for the certificate number (Column8) creating a variable ship_nbr.

Now, load the ship_body query and extract all rows with data. Use Add Column, Custom to add columns for the date and certificate numbers. For the date it looks like this and it's similar for the certificate number:

Screenshot-2022-05-09-081326.pngImage Enlarger

Reorder and remove some columns and set the correct data types. Loading it back to Excel creates a table called "ship_body_2". That's the green table in the DATA sheet. Being the very first extracted data set from Sheet1.

Now, it becomes a bit tricky as you have to edit the M-code to arrive at the script that you find in the file. The bold/red code is what needs to be added/changed.

let
   Source = Excel.CurrentWorkbook(){[Name="ship_body"]}[Content],
   Source2 = Excel.CurrentWorkbook(){[Name="ship_body_2"]}[Content],
   PromoteHdr = Table.PromoteHeaders(Source, [PromoteAllScalars=true]),
   Filter = Table.SelectRows(PromoteHdr, each ([#"No."] <> null and [#"No."] <> "")),
   AddDate = Table.AddColumn(Filter, "Date", each ship_date),
   AddCertNbr = Table.AddColumn(AddDate, "Cert", each ship_nbr),
   Reorder = Table.ReorderColumns(AddCertNbr,{"Date", "Cert", "No.", "1", "Column3", "2", "Column5", "3", "Column7", "4", "5", "6", "7"}),
   RemoveCols = Table.SelectColumns(Reorder,{"Date", "Cert", "No.", "1", "2", "3", "4", "5", "6", "7"}),
   Append = Table.Combine({Source2, RemoveCols}),
   RemoveDupl = Table.Distinct(Append, {"Cert", "No."}),
   Type = Table.TransformColumnTypes(RemoveDupl,{{"Date", type date}})
in
   Type

What this does is add the initial table "ship_body_2" table as a second source. Later on, you append the ship_body table (= current query at the RemoveCols step) to the previous version of this table which is in Source2. The remove duplicates step is needed to avoid duplication of rows when you press Refresh All without having changed the data in Sheet1.

All of this can be invisible for the users. Once set-up correctly, all they need to know is to press Refresh All on the Data ribbon. And what this does is take the newly entered data from Sheet1 and appends it to the existing ship_body_2 table, thus building a history table off all shipping certificates created.

sp_PlupAttachments Attachments
  • sp_PlupImage Screenshot-2022-05-09-081326.png (8 KB)
Avatar
lea cohen
Member
Members
Level 0
Forum Posts: 219
Member Since:
March 10, 2016
sp_UserOfflineSmall Offline
5
May 10, 2022 - 12:45 am
sp_Permalink sp_Print
Well done for the detailed and extended answer !!!
I will work according to the steps here in the coming days.
Thank you!! Very appreciative !!

By the way, with a macro it is complex to execute ??
It just intrigues me to know ...
 
Avatar
Riny van Eekelen
Örnsköldsvik, Sweden
Moderator
Members


Trusted Members

Moderators

Power BI
Level 0
Forum Posts: 440
Member Since:
January 31, 2022
sp_UserOfflineSmall Offline
6
May 10, 2022 - 3:25 am
sp_Permalink sp_Print

Glad you like the proposed solution. Perhaps, I'm not the best to judge what macros can do. Personally, I'm not very good with them and have learned I don't need them in my line of business.  PQ has given me much more joy and proved to be much easier to learn. One can do magic with PQ through just pushing the correct buttons 80% and if you dive into M for the other 20%, you can do real magic. With macro's it's the other way around I believe. But that's just my personal opinion.

sp_AnswersTopicAnswer
Answers Post
sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Andy Kirby, Chandler Davis, Atos Franzon
Guest(s) 9
Top Posters:
SunnyKow: 1432
Anders Sehlstedt: 870
Purfleet: 412
Frans Visser: 346
David_Ng: 306
lea cohen: 219
A.Maurizio: 202
Jessica Stewart: 202
Aye Mu: 201
jaryszek: 183
Newest Members:
wahab tunde
Cong Le Duc
Faisal Bashir
Ivica Cvetkovski
Blaine Cox
Shankar Srinivasan
riyepa fdgf
Hannah Cave
Len Matthews
Kristine Arthy
Forum Stats:
Groups: 3
Forums: 24
Topics: 6205
Posts: 27210

 

Member Stats:
Guest Posters: 49
Members: 31878
Moderators: 3
Admins: 4
Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT
Moderators: MOTH Support, Velouria, Riny van Eekelen
© Simple:Press —sp_Information

Sidebar

Blog Categories

  • Excel
  • Excel Charts
  • Excel Dashboard
  • Excel Formulas
  • Excel PivotTables
  • Excel Shortcuts
  • Excel VBA
  • General Tips
  • Online Training
  • Outlook
  • Power Apps
  • Power Automate
  • Power BI
  • Power Pivot
  • Power Query
microsoft mvp logo
trustpilot excellent rating
Secured by Sucuri Badge
MyOnlineTrainingHub on YouTube Mynda Treacy on Linked In Mynda Treacy on Instagram Mynda Treacy on Twitter Mynda Treacy on Pinterest MyOnlineTrainingHub on Facebook
 

Company

  • About My Online Training Hub
  • Disclosure Statement
  • Frequently Asked Questions
  • Guarantee
  • Privacy Policy
  • Terms & Conditions
  • Testimonials
  • Become an Affiliate

Support

  • Contact
  • Forum
  • Helpdesk - For Technical Issues

Copyright © 2023 · My Online Training Hub · All Rights Reserved. Microsoft and the Microsoft Office logo are trademarks or registered trademarks of Microsoft Corporation in the United States and/or other countries. Product names, logos, brands, and other trademarks featured or referred to within this website are the property of their respective trademark holders.