Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2015-02-18
10
Medium Priority
?
118 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 31

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 31

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 31

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 31

Accepted Solution

by:
gowflow earned 2000 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 31

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

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

After seeing numerous questions for Dynamic Data Validation I notice that most have used Visual Basic to solve the problem. This suggestion is purely formula based and can be used in multiple rows.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

609 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