Solved

vba - Macro to transfer from one sheet to another no 2

Posted on 2015-02-18
10
110 Views
Last Modified: 2016-02-10
Hi

This is to follow the topic: vba - Macro to transfer from one sheet to another
http://www.experts-exchange.com/Software/Office_Productivity/Office_Suites/MS_Office/Excel/Q_28619344.html


Previously, The macro was transferring specific trips to the Consolidation sheets.

I now need to add an extra validation.

When i double click in column AG if the previous cell starts with "FLEET", it needs to look in column F and find if there is an action on any stops for the TRIP. If there is no stops with actions, it should not transfer the data. But if there is 1 or more actions on any of the TRIP stops, then, it will transfer the full TRIP with all the STOPS in the consolidation sheet.

How can i do that?
Transfer-data-test-V04.jpgTransfer-data-test-V04.xlsm
0
Comment
Question by:Wilder1626
  • 5
  • 5
10 Comments
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
So far, i'm testing this.

Looks like its working so far
Dim WS2 As Worksheet
Dim ws As Worksheet
Dim MaxRow2 As Long, MaxTrip As Long, I As Long, Actions As Long

''''''''''''''''''''''''''''   Send info to Sharepoint   ''''''''''''''''''''''''''''''''''''''''

Application.ScreenUpdating = False

Set ws = ActiveSheet
Set WS2 = Sheets("Consolidation")

MaxRow2 = 2

If Not Intersect(Target, ws.Range("AG:AG")) Is Nothing Then

	If Intersect(Target, ws.Range("AG:AG")) = "DIspatched " Then
		MsgBox "BOL was previously sent"
		Exit Sub
	Else

		If ws.Range("AF" & Target.Row) = "" Then
			MsgBox "A carrier must be assigned to send BOL before dispatching"
			Exit Sub
		Else

			' Populates the Email1 tab with DC, BOL, Carrier, Line# and Filename
			WS2.Range("2:" & WS2.Rows.Count).EntireRow.Delete
			I = Target.Row
			Do
				I = I + 1
			Loop Until ws.Range("J" & I) = ""
			MaxTrip = ws.Range("J" & I - 1).Row

			Action = ws.Range("F" & Target.Row & ":F" & MaxTrip)

			For Each Action In ws.Range("F" & Target.Row & ":F" & MaxTrip).Cells
				If Action > "" Then

					ws.Range("AG" & Target.Row) = "Dispatched"
					ws.Range("A" & Target.Row & ":AQ" & MaxTrip).Copy WS2.Range("A" & MaxRow2)

					MSG1 = MsgBox("Do you want to send the data to SharePoint?", vbYesNo, "Send...")

					If MSG1 = vbYes Then
						Sheets("Consolidation").Visible = True
						Sheets("Consolidation").Select

						Sheets("Consolidation").Range("A1:E" & MaxTrip).Select
						With Selection.Validation
						.Delete
						.Add Type:=xlValidateInputOnly, AlertStyle:=xlValidAlertStop, Operator _
						:=xlBetween
						.IgnoreBlank = True
						.InCellDropdown = True
						.ShowInput = True
						.ShowError = True
					End With
					Sheets("Consolidation").Range("A2").Select

				End If
			End If
		Next
	End If
End If
End If

Open in new window

0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
So you need help or it is working ? not clear your post.
gowlfow
0
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
Hi gowflow, It is working, i'm just trying to see if i can clean it up a little.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
No it is not working as if you doublclick on AG for trip2 although it have FLEET but there is no stop it is not supposed to copy but it does.

So what you want ?
gowlfow
0
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
Hummm, you are right.

 When i double click in column AG if the previous cell starts with "FLEET", it needs to look in column F and find if there is an action on any stops for the TRIP. If there is no stops with actions, it should not transfer the data. But if there is 1 or more actions on any of the TRIP stops, then, it will transfer the full TRIP with all the STOPS in the consolidation sheet.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 29

Expert Comment

by:gowflow
Comment Utility
ok here it is !!!!

WAIT !!!
you said
 if the previous cell starts with "FLEET" ??
like if it is 'ABC' or 'BLABLA' it should not also copy it ???

and if it is "FLEET 123" should it copy it ???

gowflow
0
 
LVL 11

Author Comment

by:Wilder1626
Comment Utility
Let me try to explain this a better way.

Actually, If the first 5 digits from the previous cell starts with "FLEET" and if on that trip, there is an action  at any stops in column F, it needs to transfer to Consolidation sheet.

If the first 5 digits from the previous cell starts with "FLEET" and if on that trip, there is NO action  at any stops in column F, It should not transfer to the Consolidation sheet.

Also

If the first 5 digits from the previous cell DOEST NOT starts with "FLEET" , It automatically transfer to the Consolidation sheet.

Does it make more sense this way?
0
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
ok clear
is this what you want ?
gowlfow
Transfer-data-test-V05.xlsm
0
 
LVL 11

Author Closing Comment

by:Wilder1626
Comment Utility
Thank you again. Everything is working great.
0
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
Good. Let me know if you need help in an other question simply post a link in here.
gowflow
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

How to quickly and accurately populate Word documents with Excel data, charts and images (including Automated Bookmark generation) David Miller (dlmille) Synopsis In this article you’ll learn how to use ExcelToWord! to copy data,charts, shapes …
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
The viewer will learn how to create two correlated normally distributed random variables in Excel, use a normal distribution to simulate the return on different levels of investment in each of the two funds over a period of ten years, and, create a …
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

763 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now