Posts by oddy73

Re: Combine two data sets and merge them

Hi jindon,

Hopefully you are doing alright. Since our session, I learned allot and developed further our tool, especially hour comparison and merge solution. I’ve implemented and test all the way and it worked great, many thanks again for that!

While using the comparison solution, and running reporting we missed some data… at first everything looked fine and nothing was lost. Suddenly I found the “bug”. Actually it’s not a bug, it’s a scenario that we did not think about at that time.

The scenario that we forgot states:
If a person works on one or more projects per month but he is not sick or he has not taken any holiday in that month. How should the comparison code should handle this scenario. Because the current code does not take that person into account while merging… see the attachment.

For example in the attachment you see that even though Marco worked on one project and he was not sick or taken any holiday on that month, he will not taken into account while merging…

Where should I look for modifying the code in order to take also Marco into account.

And what if a person is the whole month sick and he/she writes zero hours for that project… how should that the code be looking?

Re: Display date Textbox as dd-mm-yyyy

I know royUK and I also know that over couple of month we will using comboboxes duo to performances ...

Thanks any way

Re: Display date Textbox as dd-mm-yyyy

Hi,

thanks for your feedback. Your solution works perfect, exactly how I wished. I discussed this change (using Dropdown box) and unfortunately this is not what the "my boss" wants. In the meantime I developed a workaround by using three textboxs and concatenate it in excel...

I know this is not clean. I will save this solution and use it definitely next time! I did not expected that this was that easy

Thanks man!!!

Re: Code gives error when column is empty

Sorry, here is the link to the person who supplied me the code

Re: Display date Textbox as dd-mm-yyyy

After testing the solution it gave me strange results.
I attached the example and as you may see the birth date Textbox displays correctly the date (the format), however i cannot edit it anymore... I found this a very strange bug...

Any help is welcome

(Since I did not get any response, i post the same question also here, When I find the solution, i will post it here off course )

Re: Code gives error when column is empty

This works now for me

Sub AlignResourceData()   Dim inRRay As Variant, outRRay As Variant
    Dim oneValue As Variant, i As Long




    With Sheet1.Range("A1")
        With Sheet1.UsedRange
            inRRay = .Value
            ReDim outRRay(1 To .Cells.Count, 1 To 1)
        End With




        i = 1
        For Each oneValue In inRRay
            outRRay(i, 1) = oneValue
            i = i + 1
        Next oneValue
        .Resize(UBound(outRRay, 1), 1).Value = outRRay
    End With




    Call RemoveBlanks




End Sub
     








Sub RemoveBlanks()




With Range("A1:A1000")
        .Value = .Value
        .RemoveDuplicates Columns:=1, Header:=xlNo
        On Error Resume Next
        .SpecialCells(xlCellTypeBlanks).Delete xlShiftUp
        On Error GoTo 0
    End With
    
End Sub

Hi,

I have this code that works perfect (almost). It copies all the data in column A, B,C, etc... and pasts under A2. However, it does not work if a column is empty.
What I wish is that this code ignores empty columns and continues collecting columns and pasting it under A2, because I have another macro that removes duplicated and blanks

I have provided an example attachment with the button .

Sub AlignResourceData()    
    Dim inRRay As Variant, outRRay As Variant
    Dim oneValue As Variant, i As Long
     
    With Sheet1.Range("A2"): Rem adjust
        With .CurrentRegion
            inRRay = .Value
            ReDim outRRay(1 To .Cells.Count, 1 To 1)
        End With
         
        i = 1
        For Each oneValue In inRRay
            outRRay(i, 1) = oneValue
            i = i + 1
        Next oneValue
        .Resize(UBound(outRRay, 1), 1).Value = outRRay
    End With


call RemoveBlanks




End Sub


Sub RemoveBlanks()




With Range("A2:A1000")
        .Value = .Value
        .RemoveDuplicates Columns:=1, Header:=xlNo
        On Error Resume Next
        .SpecialCells(xlCellTypeBlanks).Delete xlShiftUp
        On Error GoTo 0
    End With
    
End Sub

I waited and did not get any response so I posted also here.

Re: Display date Textbox as dd-mm-yyyy

Hey guys,

I found the solution in this forum. For those who wants to know:

TextBox12.Text = Format(TextBox12.Text, "dd-mm-yyyy")

Hey guys,

I live in Europe and we I use for the userform the following date notation: dd-mm-yyyy

My userform is inspired from the following source

The user puts a date like: dd-mm-yyyy for example (06-04-2011) (April)
This userform saves the date like dd-mm-yyyy. However when I restart the userform and I look up the data, the date displayed as mm/dd/yyyy.
The funny part is when I change my regional settings to dd-MM-YYYY and the useform is already open, than when I look up data, then it displays how I wanted: dd-mm-yyyy. But when I restart the excel and look up the data, it goes back to mm/dd/yyyy.

My wish is how can I force the textbox to display the date as dd-mm-yyyy.

(tip: you can use the ID to look up data in the userform)

Thanks in advance

Re: Export all the worksheets to a folder expect the first four worksheet

Thanks everyone, I found the solution for deleting worksheets, Love EXCEL!!!

Dim ws As Worksheet
Application.DisplayAlerts = False
For Each ws In Worksheets
If ws.Name <> "Sheet1" And ws.Name <> "Sheet2" Then ws.Delete
Next
Application.DisplayAlerts = True

Re: Export all the worksheets to a folder expect the first four worksheet

YES, that did the trick! Thanks!!
Now, how to delete the remaining six (or more) worksheets... the ones that are exported...?

Re: Export all the worksheets to a folder expect the first four worksheet

Hi DaveExcel,

We are getting closer.

The -4 does export only the first six and and not the last four.

But I want that the code exports everything except the first four worksheets.

Re: Run macro in another worksheet (Advanced )

Hey guys,

Many thanks firstly..

I start a new thread since my main problem had different title and different problems here. Here is the Linkto that other thread.

This thread is solved.

Thanks everyone!!

I start this new thread since my main problem had different title and different problems. Here is the link to that other solved thread.

I have 10 worksheets (and growing) in my workbook
The first four worksheets I use them for some calculations via a macro . And via this macro I create more worksheets for example 6 taps (4+6=10 tabs).

I have the following code, which exports all the worksheets in this workbook in to the same folder where the main file exists. This is not what I wish.

Sub SaveShtsAsBook()      Dim Sheet As Worksheet, SheetName$, MyFilePath$, N&
      MyFilePath$ = ActiveWorkbook.Path & "\" & _
                    Left(ThisWorkbook.Name, Len(ThisWorkbook.Name) - 4)
      With Application
            .ScreenUpdating = False
            .DisplayAlerts = False
            On Error Resume Next    '<< a folder exists
            MkDir MyFilePath            '<< create a folder
            For N = 1 To Sheets.Count
                  Sheets(N).Activate
                  SheetName = ActiveSheet.Name
                  Cells.Copy
                  Workbooks.Add (xlWBATWorksheet)
                  With ActiveWorkbook
                        With .ActiveSheet
                              .Paste
                              .Name = SheetName
                              [A1].Select
                        End With
                        'save book in this folder
                        .SaveAs Filename:=MyFilePath _
                                          & "\" & SheetName & ".xlsx"
                        .Close SaveChanges:=True
                  End With
                  .CutCopyMode = False
            Next
      End With
End Sub

My wish is to have the possibility when using the a button (SaveShtsAsBook) that I get immediately a dialog box where I can select my destination than only the worksheets that are created (6 worksheets) should be exported and not the first four. Once the export is finished, all the visible (because I have also hidden sheets) worksheets should be deleted and only the first four worksheets should remain. Yes the first four worksheets can be hard-coded since its name never changes...

Thanks.

Re: Run macro in another worksheet (Advanced )

Hey guys,

I promised you all to give an update. I succeeded to pass the tabname variable to the random “ButtonGenerate”. Now the function "ButtonGenerate" is applied for each tab.

I also succeeded exporting each individual created tab into a folder. However this is done very badly…

Right now, the export function exports all the sheets of the current workbook and creates a folder and export them.

My wish is to have the possibility when using the “Merge & Report” button that I get immediately a dialog box where I can select my destination and then the process continues by distributing (+"ButtonGenerate") everything.

Once everything is distributed and exported, the main file with all the new created tabs should get removed (be ready for next month). So only two tabs should be there à Merge and Cal_template.

Can anyone help me finalize this reporting tool?

thanks!

Re: Run macro in another worksheet (Advanced )

Hi,

I am wondering whether my problem description is clear enough or may be I am trying to get something which is not logic...

Any suggestions?

Re: Run macro in another worksheet (Advanced )

Hey guys,

I've came very far and now I am stuck. Here is how far I came:

• Implemented a new calendar timesheet
• Implemented a macro that generates random numbers for that person, based on the data we have.

Since couple of days I cannot fix the issue where I can give the macro “ButtonGenerate” the variable of the current or active sheet. Right now its hard-coded for “Rob”. And as you can see, the button generates random numbers only for “Rob”. I tried to pass the variable “e” but had no success. Question is how to generalize the macro “ButtonGenerate” so it can be applied for all the created tabs?

Another issue I have is that I am trying to add another function in this whole process, immediately at the beginning.

So when the user clicks the button “Merge & Report”, he should get first a save as dialog box where he ONLY have to choose (or create a folder somewhere) the folder. Once the folder is created, the process starts by 1 merging, 2 distributing the data over each person, generating random numbers and finally all the created tabs should be exported to the folder that was created at the beginning of this process.

I hope I am clear to what I want to achieve

Thanks in advance!

Re: Run macro in another worksheet (Advanced )

Hi,

Sorry for late feedback... the weekend started and other responsibilities come alone with it.
I have implemented the code jindon and it works very nice and smooth...

Right now I am trying to modify and tweak the code, like removing the formulas and keep the values in the end reporting (per person). Another thing that needs to be done is also run a macro per sheet.

I will update you guys and let you know.

Many thinks everyone!!!!!!!

Re: Run macro in another worksheet (Advanced )

Please find attached an export of Rob.
However the macro I wanted to run on step 3 should delete column A to F and somehow maintain the data in the timesheet. But that is a challenge for later.
(And if possible to give the tab name a date )

royUK, I tried your code it copied only exported cal_template and applied formatting, it did not copy any data from the merge sheet in it.

Thanks guys

Re: Combine two data sets and merge them

Hi Jindon,

Here is a link to my post.

Thanks in advance!