Posts by ir121973

Re: VBA Create Unique Distinct Values List

Hi Wigi, thank you for taking the time to reply to my post.

Like snb, I too had thought about using pivot tables, but as I've said to snb, the problem I have is that some of my colleagues are perhaps not particularly well versed in using Excel, even the more straight forward functions. There is an additional problem, in that my employer is due to move to Excel 2013, and although I now have some experience of this version, my colleagues don't which I know would only compound the issue.

Many thanks and kind regards

Re: VBA Create Unique Distinct Values List

Hi snb, thank you very much for taking the time to read my post and for the suggestion.

I had looked at pivot tables, and if it was purely down to me then I may have gone down this route. The unfortunate problem I have, is that some of my colleagues aren't particularly technical, and without being discourteous to them, they would struggle to create a pivot table.

Automating the process via VBA was the option which I thought would be the safest to use.

Many thanks and kind regards

Re: VBA Create Unique Distinct Values List

Hi jindon thank you very much for coming back to me with this. It truly is appreciated.

The code works great, but I've just been looking at the code which copies and pastes the information into the "Projects" sheet. I'm not sure whether you recall from an earlier post I made, that because the project name could literally be anything, I was looking at using the condition of where the value in column E of the 'Source' sheet does not equal "DIR", "Enhancements", "IND", "OVH", then paste this into the "Projects" 'Destination' sheet, rather than the code which specifically looks for the value "Pro".

I'm so very sorry to trouble you with, but could you perhaps tell me please, would this be possible to implement please?

May I also ask please, whether it would be possible to include some annotations to the code so that I can learn from this?

Many thanks and the kindest regards

Re: VBA Create Unique Distinct Values List

Hi jindon,thank you so very much for your continued help with this.

The codes great, but I'm afraid I owe you an apology. In the last file I sent I incorrectly showed "Leave" for "Team A" on the "Overheads Output" page as three separate rows when in fact this sheet should follow the same logic as all the other sheets. i.e. amalgamating like for like values. I'm very sorry! Everything else is perfect.

I've re-attached the file for with the correct values shown on the "Overheads Output" sheet.

Thank you and kind regards

Re: VBA Create Unique Distinct Values List

Hi jindon, thank you for coming back to me so quickly with this.

Absolutely no problem in creating the new sheet. This is called "Overheads Output" in the revised attached file. Hopefully, I've been able to illustrate that there is "Leave" for two separate teams. Any problems, please don't hesitate to let me know.

Many thanks and kind regards

Re: VBA Create Unique Distinct Values List

Hi jindon, thank you so very much taking the time to reply to my post and for putting the solution together, it's incredible.

There are just a few minor queries, if I may.

• In the case of the "OVH" value being found on the 'Source' sheet, the value from column E, rather than column D, is being copied and paste into column B on the "Overheads" 'Destination' sheet.
• I am also very sorry because I omitted this from my original post. Would it be possible please to include the scenario, where the value in column E of the 'Source' file is not "DIR", "Enhancements", "IND" or "OVH", and if this is the case follow the logic of the "DIR", "Enhancements", "IND" and "OVH" extraction and paste to the "Projects" 'Destination'. As I said, I'm really very sorry for omitting this. I have attached a revised file with the extra 'Destination' sheet which runs using your code.
• Would it be possible please for you to include comments in your code so I can follow the process, because I'm very keen to learn more about VBA programming

Once again sincere thanks and apologies

Kind Regards

Hi, I wonder whether someone may be able to help me please.

With some help along the way, I've put together the script in the attached file which compares values from the 'Source' "AllData" sheet and extract these records, creating a unique distinct list before pasting them to specific 'Destination' sheets.

Although I have included explanations in my code for each section, I'll also try to explain in more detail below.

• The code initially looks for the following values in column E of the 'Source' "AllData" sheet, "DIR", "Enhancements", "IND" and "OVH".
• When the values "DIR", "Enhancements" or "IND" are found, the script then creates a unique distinct list and copies the data from column E of the 'Source' sheet and pastes this to column B of the applicable 'Destination' sheet (either "Direct Activities", "Enhancements" or "Indirect Activities"). In the case of the value "OVH" being found, the data from column D, rather than column E is copied and paste to column B of the "Overheads" 'Destination' sheet.
• In addition, the script sums a 'Manhour' figure from column I of the 'Source' sheet and places this under the relevant monthly column heading in the 'Destination' sheet.

What I'm now trying to do is amend this, and include the information from column B "LOB" on the 'Source' "AllData" sheet. So the script will now look and compare the values in columns B and E before creating the unique distinct list on the applicable 'Destination' sheet.

I appreciate that this may not be particularly easy to understand so I have attached my test file.

• The "Macro" sheet is fairly self explanatory. Please select the button to run the macro using the existing code.

• The "AllData" sheet is the 'Source' sheet where the data is extracted from.
• Sheets "Direct Activities", "Enhancements" and "Overheads" are the 'Destination' sheets for the current macro code.
• Sheets "Direct Activities Output", "Enhancements Output" and "Overheads Output" show how I would expect to see the results to be using the above change.

I've been trying for a few days now to make this this change, but I feel my lack of knowledge is hampering me in being able to achieve this.

I just wondered whether someone may be able to look at this please and offer some guidance on how I may go about achieving this.

Many thanks and kind regards

Re: VBA Amalgamate Code

Hi patel, thank you for taking the time to reply to my post and for the solution, it works great/

All the best and kind regards

Hi, I wonder whether someone may be able to help me please.

As you can see, I've put together two scripts (below), which perform the following:

Script 1 -

• Check to see if there is a value in column B from row 5.
• If there is, move one row down and one column to the left from the last populated row in column B
• Insert a total of the rows above for the next twelve columns. If there is no value in column B, do not add totals row.

Sub InsertTotals()
Dim colm As Long, StartRow As Long
Dim EndCell As Range
Dim ws As Worksheet
StartRow = 5
For Each ws In Worksheets(Array("Direct Activities", "Enhancements", "Indirect Activities", "Overheads"))
    Set EndCell = ws.Cells(Rows.Count, "B").End(xlUp).Offset(1, 1)
    If EndCell.Row > StartRow Then EndCell.Resize(, 12).Formula = "=SUM(R" & StartRow & "C:R[-1]C)/7.4"
    EndCell.HorizontalAlignment = xlCenter
    ws.Columns("B:N").EntireColumn.AutoFit
Next ws
End Sub

Script 2 -

• Check to see if there is a value in column B from row 5.
• If there is, move one row down and from the last populated row in column B
• Insert a the title 'Mandays Total' and add formatting.

Sub InsertTitle()
Dim colm As Long, StartRow As Long
Dim EndCell As Range
Dim ws As Worksheet
StartRow = 5
For Each ws In Worksheets(Array("Direct Activities", "Enhancements", "Indirect Activities", "Overheads"))
    Set EndCell = ws.Cells(Rows.Count, "B").End(xlUp).Offset(1, 0)
    If EndCell.Row > StartRow Then EndCell.Value = "Mandays Total"
    EndCell.HorizontalAlignment = xlRight
    EndCell.Font.Bold = True
    EndCell.Font.ColorIndex = 2
    EndCell.Interior.ColorIndex = 11
    EndCell.Interior.Pattern = xlSolid
    EndCell.Cells.Font.Name = "Lucida Sans"
    EndCell.Cells.Font.Size = 10
Next ws
End Sub

Both scripts work fine, but I'm relatively new to VBA, so they're perhaps not the most elegant or perhaps the most efficient.

I just wondered whether someone may be able to look at this please and offer some guidance on how I may go about amalgamating these so that they're a little less complicated and more efficient.

Many thanks and kind regards

Re: Copy From Multiple Files To One

Hi, thank you very much for taking the time to reply to my post.

I did try your solution using a button in my Master file, but unfortunately I couldn't get this to work. I did change the relevant data i.e. file path and sheet name to fit my system details, but all that I was able to achieve was that it closed my Master file.

Many thanks and kind regards

Chris

Hi I wonder whether someone may be able to help please.

I'm trying to put together a script which allows the user to select multiple Excel files and paste them into a 'Master' Excel sheet. I've been working with some code that I found on this site here http://www.ozgrid.com/forum/showthread.php?t=65860 and the code below is my adaptation of this:

Sub CopyData()
    Dim wsT As Worksheet
    Dim wsF As Worksheet
    Dim lRow(1) As Long
    Dim iCol As Integer
     
    Dim sFile As String
     
    sFile = Application.GetOpenFilename("Excel Files (*.xls), *.xls")
    If sFile = "False" Then
        Exit Sub
    End If
    Set wsT = ThisWorkbook.Sheets("Combined")
    Workbooks.Open sFile
    Set wsF = ActiveSheet
     
     'get size of data
    lRow(0) = wsF.Cells.Find(What:="*", _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row
    iCol = wsF.Cells.Find(What:="*", _
    SearchOrder:=xlByColumns, _
    SearchDirection:=xlPrevious).Column
    
     
    lRow(1) = wsT.Cells.Find(What:="*", _
    SearchOrder:=xlByRows, _
    SearchDirection:=xlPrevious).Row + 1
    If lRow(1) + lRow(0) > Rows.Count Then
        MsgBox "WorkSheet To full to Copy To", vbInformation
    Else
        wsT.Range("A" & lRow(1) & ":" & Cells(lRow(1) + lRow(0), iCol).Address).Value _
        = wsF.Range("A5:" & Cells(lRow(0), iCol).Address).Value
    End If
    ActiveWorkbook.Close False
End Sub

The 'Source' & 'Destinantion' Files have a header row in row 4, so by making the changes here

= wsF.Range("A5:" & Cells(lRow(0), iCol).Address).Value

I tell the macro to start to copy from row 5 and this works fine.

The problem I'm having is with how the information is then pasted into the 'Destination File'. The macro correctly pastes the information on the correct row and columns, but no matter which source file I use, the macro pastes 5 extra rows of data at the end of the imported data with every cell content shown as #N/A.

Through many hours of working through this, I've found that the number of erroneous rows derive from these two lines:

This is where the number of rows are set

= wsF.Range("A5:" & Cells(lRow(0), iCol).Address).Value

with the '(lRow(0))' variable being used in this line

wsT.Range("A" & lRow(1) & ":" & Cells(lRow(1) + lRow(0), iCol).Address).Value _

which carries out the 'Paste' command. I know this to be true because if I change A5 to A2 for example, there are 2 rows of erroneous data.

I've tried taking the '(lRow(0))' out of the 'Paste' command line which does solve the problem for the first file that I'm importing. However, if I then try to import a second file I encounter the same problems.

I've been working on this for several days now, but I just can't find the answer.

I just wondered whether someone could perhaps take a look at this please and let me know where I'm going wrong.

Many thanks and regards