• 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

AUTOFILL HELP PLEASE!|VBA & Macros|Excel Forum|My Online Training Hub

You are here: Home / AUTOFILL HELP PLEASE!|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 & MacrosAUTOFILL HELP PLEASE!
sp_PrintTopic sp_TopicIcon
AUTOFILL HELP PLEASE!
Avatar
Carlos Arcilla
Member
Members
Level 0
Forum Posts: 10
Member Since:
June 1, 2020
sp_UserOfflineSmall Offline
1
July 7, 2020 - 5:26 am
sp_Permalink sp_Print

Okay, I am unable to provide a sample workbook because the file is too large so I will try my best to explain. I am creating shipping tickets for work,

Two sheets. We will say Sheet 1 (S1) and Sheet 2 (S2). Sheet 1 is where all the information is, and Sheet 2 is the ticket sheet itself where we can extract information from Sheet 1 to create our ticket. 

On Sheet 1 there are 4 significant columns:

- Column A: Every row (we can assume up to 500 rows) has a drop down list with one option of "Yes" 

- Column B: Every row is populated with a unique number that refers to the project number

- Column C: This column refers to the Shipping #... this used to be manual entry, but we are trying to automate this.. I will explain later. Assume every row on this column empty. What is populated in each row in this column comes from a cell in Sheet 2.

 - Column D: This column refers to the Shipment Date. This has the same properties as Column C, and we are also trying to automate this.

 

Now the process of making the ticket...

Once a row, or project, is tagged "Yes" in column A (S1), we can go to S2 and sort and show all the "Yes" rows to make the ticket. Once this is done, we will see the ticket format with the project information in it (S2). When this is done, we have formulas that generate the Shipping # and Shipment date on S2, lets assume this content was populated on cells K8 and K9 respectively. Now that S2 is filled up, we have our ticket.

 

What we want...

Now that we have the Shipping # and Shipment date on S2, we want to put this information automatically in S1 to the correct columns (Columns C and D) ONLY for the rows that were tagged "Yes" for that specific Shipping # and Shipment date. Even once that row is untagged as "Yes", I still want the date and Shipping # to stay there. I need help to make a code that automates this. The operation of the user would be to make the ticket then activate this macro through a command button. 

 

I have tried an autofilter, copy-paste type of macro, but I can't even seem to get it to work, so if someone could help me that would be great. Please let me know if you have any further questions.

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
2
July 7, 2020 - 1:45 pm
sp_Permalink sp_Print

From what you have wrtitten the process seems fairly straight forward, but the different sheets and locations make it very complex without seeing it. I know your work book is large but can you please put together a test file of say 5 lines in the same format?

I will be happy to try and help but guessing & recreating formats and locations/positions will be a waste of everyones time and is likley to lead to mistakes.

Purfleet

Avatar
Carlos Arcilla
Member
Members
Level 0
Forum Posts: 10
Member Since:
June 1, 2020
sp_UserOfflineSmall Offline
3
July 8, 2020 - 5:06 am
sp_Permalink sp_Print

As you can see on Sheet1, you can tag each Project as "Yes" in column A. I will provide an example procedure that we can both follow to help give you an understanding. 

So...

Lets tag Projects 402 and 404 as "Yes" in Sheet1. When you go to Sheet2, you can see that both of these are tagged "Yes". On Sheet 2, you can also see the Shipping # which is Cell B1 (value of 7204), and the Shipment Date which is Cell C1 (whatever date you open this.. for example's sake I will say 7/7). 

Once this process is done and clarified, I want to have a command button (which will be on Sheet2) with your proposed macro assigned to it. Once you press the button on Sheet2, I want projects 402 and 404 to have the Shipping # 7204 (from Sheet2) on its designated cells (C2 & C4 - on Sheet1) and Shipment date on its designated cells (D2 & D4 - on Sheet1). 

Now that the data has been filled out for projects 402 and 404, it is time for me to document another order which will involve other projects. This means I now have to un-tag the "Yes" from projects 402 and 404. Once untagged, I still want the data to stay. 

 

NOTE: ALL projects are unique so there should be no issues on overriding in case you had any questions on that.

NOTE: Let's say this is for the first order shipment. Is there a way that you can design the code to say "if there is already data for the Shipping # and Shipment Date of the first shipment, then this will put the data on the second shipment" (which will just be the same thing but a couple columns down - everything of the same format).... and so on for multiple shipments

 

I am sorry if I typed too much, I am just trying to elaborate as much as possible. I have attached the sample workbook.

Avatar
Purfleet
England
Member
Members


Trusted Members
Level 4
Forum Posts: 412
Member Since:
December 20, 2019
sp_UserOfflineSmall Offline
4
July 10, 2020 - 12:32 pm
sp_Permalink sp_Print

Sorry work keeps getting in the way of more important stuff

Have a look at the attached and see what you think, its a bit rough and ready but i think it does what you want - i have also made it clear yes's from column A once run.

Note that i have changed the sheet names just so i knew what i was doing, if you channge them to anything else you will need to update the code

Sub UpdateRecords()

Dim ShipNo As String
Dim ShipDate As Date
Dim ProjectNo As String
Dim ProjectRange As Range
Dim ProjectCell As Range
Dim ProjectFound As Long

ShipNo = Range("b1")
ShipDate = Range("c1")

'Sets the range of project numbers to look up
Set ProjectRange = Range("d3:d" & Cells(Rows.Count, 4).End(xlUp).Row)

'Looks at each cell that is not blank and updates the records on the Log sheet
For Each ProjectCell In ProjectRange
If ProjectCell.Value = "" Then
GoTo Skip
Else
ProjectFound = Worksheets("Log").Range("b:B").Find(what:=ProjectCell).Row
Worksheets("Log").Range("c" & ProjectFound) = ShipNo
Worksheets("Log").Range("d" & ProjectFound) = ShipDate
End If
Skip:

Next ProjectCell

'clears yes's from the Log sheet once updated
Worksheets("Log").Range("a:a").ClearContents

End Sub

Avatar
Carlos Arcilla
Member
Members
Level 0
Forum Posts: 10
Member Since:
June 1, 2020
sp_UserOfflineSmall Offline
5
July 21, 2020 - 12:05 am
sp_Permalink sp_Print

Thank you. I tested out the code and it works well. I am learning as I am writing these codes, so could you please provide clarification for these three lines?

 

Set ProjectRange = Range("d3:d" & Cells(Rows.Count, 4).End(xlUp).Row)

ProjectFound = Worksheets("Log").Range("b:B").Find(what:=ProjectCell).Row

Worksheets("Log").Range("a:a").ClearContents

I have highlighted what I do not understand in red. How should I interpret those ranges as well as the "Rows.Count,4"? What do these mean?

sp_Feed
Go to top
Forum Timezone: Australia/Brisbane
Most Users Ever Online: 245
Currently Online: Genevieve Guex, Richard Benson-King, Janset Beyaz
Guest(s) 8
Currently Browsing this Page:
1 Guest(s)
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: 27209

 

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.