Solved

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

Posted on 2015-02-18
10
111 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
ID: 40617973
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
ID: 40618986
So you need help or it is working ? not clear your post.
gowlfow
0
 
LVL 11

Author Comment

by:Wilder1626
ID: 40619009
Hi gowflow, It is working, i'm just trying to see if i can clean it up a little.
0
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
LVL 29

Expert Comment

by:gowflow
ID: 40619013
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
ID: 40619036
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
 
LVL 29

Expert Comment

by:gowflow
ID: 40619047
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
ID: 40619113
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
ID: 40619217
ok clear
is this what you want ?
gowlfow
Transfer-data-test-V05.xlsm
0
 
LVL 11

Author Closing Comment

by:Wilder1626
ID: 40620120
Thank you again. Everything is working great.
0
 
LVL 29

Expert Comment

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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

809 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