<hr style="color: rgb(255, 255, 255); background-color: rgb(255, 255, 255);" size="1"> <!-- / icon and title --> <!-- message --> 1.'Want to copy From Sheet 1 and paste data into this workbook Flows
'Allow a command button to allow the user to open the workbook trade.xls than automactically copy the Sheet 1 data only two columns 'Porfolio and groupnumber' onto this workbook with this code I crop from the internet and how do I execute it? Thankyou for the first answer but I not allow to use macro recorders due to restrictions so i have to use purely code or is there a better code to crop the first two columns to from sheet1 of another workbook and paste onto my one called flows?
How do I tackle this with this code?
Sub TrdFlw()
Dim sht As Worksheet
On Error Resume Next
Application.CommandBars("Choose Sheet to Copy").Delete
On Error GoTo 0
'Added this to allow the user to search for the file.
Application.FindFile
With Application.CommandBars.Add("Choose Sheet to Copy", , False, True)
With .Controls.Add(msoControlDropdown)
For Each sht In ActiveWorkbook.Sheets
If sht.Visible = xlSheetVisible Then
.AddItem "" & sht.Name
End If
Next sht
.TooltipText = "SheetNavigate"
.OnAction = "Sheet_Navigate"
End With
.Protection = msoBarNoCustomize
.Position = msoBarFloating
.Visible = True
End With
End Sub
'This is to import data from sheet 1.
Sub Sheet_Navigate()
Dim stActiveSheet As String
With CommandBars.ActionControl
stActiveSheet = .List(.ListIndex)
End With
Worksheets("" & stActiveSheet).Activate
'Added this to copy the selected sheet to the template.
ActiveSheet.Copy Before:=Workbooks("NCUA 5300 Call Report.XLS").Sheets(4)
Application.CommandBars("Choose Sheet to Copy").Visible = False
Sheets("MACRO PAGE").Select
End Sub
2. Sorry for the trouble but I need a workaround
'Want to find duplicates Portfolios, Group Number if duplicate please prompt with same colors or message since I don't know codes I just grap it from websites? What code should I write for these
e.g. Portfolio and Group Number into a new workbook. These two columns will contain data e.g.
Portfolio Group Number
Vanessa 0
Standard 1
Than the good data that has no same names or double ups will be pasted onto a separate sheet1 or sheet2
Than I have to write a code to prompt the user about the two same names from Portfolios with a message prompt or a color prompt to tell the user we have a same Portfolios with same names
e.g.
Before
Portfolio Group Number Bad
Standard 1 B
Standard 1 B
After I would like to prompt the user of the error than after they checked it they can put a simple C for checked and paste onto a new worksheet
Portfolio Group Number Checked
Standard 1 C
Standard 1 C
Sub FindDupes()
Dim varCol As String
SelectColumn:
varCol = InputBox("Please enter the column number to search.", "Duplicates")
If Not (IsNumeric(varCol)) Then
MsgBox "Please enter in the column number only"
GoTo SelectColumn
End If
For x = 1 To 1000
Set Sheet = ActiveSheet
If Sheet.Cells(x, varCol * 1).Value = Sheet.Cells(x + 1, varCol * 1).Value Then
MsgBox "Duplicate found at row " & x
Sheet.Cells(x, varCol * 1).Select
Exit Sub
End If
Next x
End Sub
Sub FormatDuplicate()
With Selection
.FormatConditions.Delete
.FormatConidtions.AddUniqueVales
.FormatConditions(1).DupeUnique = xlUnique
.FormatConditions(1).Interior.Color = RGB(254, 0, 0)
End With
End Sub
Sub FormatContainsA()
With Selection
.FormatConidtions.Delete
.FormatConidtions.Add Type:=xlTextString, String:="Portfolios, Customers"
TextOperator:=xlContains
xlBeginsWith , xlDoesNotContain, xlEndsWith
.FormatCondition(1).Interior.Color = RGB(255, 0, 0)
End With
End Sub
