Solved

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

Posted on 2015-02-18
10
114 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 30

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
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!

 
LVL 30

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 30

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 30

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 30

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

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

695 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