Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 119
  • Last Modified:

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

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
Wilder1626
Asked:
Wilder1626
  • 5
  • 5
1 Solution
 
Wilder1626Author Commented:
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
 
gowflowCommented:
So you need help or it is working ? not clear your post.
gowlfow
0
 
Wilder1626Author Commented:
Hi gowflow, It is working, i'm just trying to see if i can clean it up a little.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
gowflowCommented:
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
 
Wilder1626Author Commented:
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
 
gowflowCommented:
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
 
Wilder1626Author Commented:
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
 
gowflowCommented:
ok clear
is this what you want ?
gowlfow
Transfer-data-test-V05.xlsm
0
 
Wilder1626Author Commented:
Thank you again. Everything is working great.
0
 
gowflowCommented:
Good. Let me know if you need help in an other question simply post a link in here.
gowflow
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now