VBA coding

[Aberdham]

Hi there,

I tried to combine sheets with different columns to a new sheet. and ran the code. but it says user-type not defined.

following link to the workbook: https://1drv.ms/x/s!Aqt4VfikFsyKdP__4BbJF1LMxlM

could anyone help me to run a debug?

Thank you in advance !

 

[mumps]

Re: Need help with VBA coding

Could you clarify what you mean by:

do a filter on column A (only material) and column F ( dated between 01.04-30.04.2018)

Do you simply want to filter all the rows on Sheet1 to show only those between 04/01/2018 and 04/30/2018 ?

 

[Aberdham]

Re: Need help with VBA coding

Could you clarify what you mean by:
Do you simply want to filter all the rows on Sheet1 to show only those between 04/01/2018 and 04/30/2018 ?

As well as in column A the course type material.

Thanks !

 

[Aberdham]

Re: Need help with VBA coding

As well as in column A the course type material.

Thanks !

Perhaps you can walk me through the machism then I would know how the code for filterings would work

I see in the code that you indicate the file name there. And if there’s a different sheet name. I just change the name within the code ?

 

[mumps]

Re: Need help with VBA coding

I don't refer to the file name. I refer to the sheet name. I can have the macro place the data in a separate workbook as you requested but I'm not sure how you want to filter column A. How do you decide on the criteria for the filter? Do you want the macro to prompt you to enter the filter criteria for column A and also to enter the start and end dates?

 

[Aberdham]

Re: Need help with VBA coding

I don't refer to the file name. I refer to the sheet name. I can have the macro place the data in a separate workbook as you requested but I'm not sure how you want to filter column A. How do you decide on the criteria for the filter? Do you want the macro to prompt you to enter the filter criteria for column A and also to enter the start and end dates?

Yes keep all the rows that are material and falls into (only invoice date) April and filter out all the rest. It would be nice to have the code to prompt the filter.

 

[Aberdham]

Re: Need help with VBA coding

I don't refer to the file name. I refer to the sheet name. I can have the macro place the data in a separate workbook as you requested but I'm not sure how you want to filter column A. How do you decide on the criteria for the filter? Do you want the macro to prompt you to enter the filter criteria for column A and also to enter the start and end dates?

Oh right. Sheet name sorry. That can be modified just by change the name in the code? I mean to only do the filtering in sheet 1 not the purchase usd nor the purchase eur sheets.
Sorry to bother you. But I have one more question. Would the rows be auto populated whenever new entries are recorded in the output files? Or do I need to first delete all the entries and then run the code once again?

Thanks a lots

 

[mumps]

Re: Need help with VBA coding

I can have the macro clear the old entries and refresh with all the new data. I'm almost finished with the macro but I have to go out for a few hours so I will get back to you as soon as I can.

 

[Aberdham]

Re: Need help with VBA coding

I can have the macro clear the old entries and refresh with all the new data. I'm almost finished with the macro but I have to go out for a few hours so I will get back to you as soon as I can.

Great. please take your time and look forward to hear from you...

 

[mumps]

Re: Need help with VBA coding

Because of the prompts involved, I've had to add error checking code to validate the data entered when prompted. Start by opening a new blank workbook and save it as "CombinedData.xlsx". Sheet1 of this workbook will be the destination sheet. Then copy/paste the following macro in a regular module in the source workbook and run it from there. Make sure both workbooks are open before running the macro.

Sub CopyCols()
    Application.ScreenUpdating = False
    Dim srcWB As Workbook
    Set srcWB = ThisWorkbook
    Dim desWS As Worksheet
    Set desWS = Workbooks("CombinedData.xlsx").Sheets("Sheet1")
    desWS.UsedRange.ClearContents
    Dim beginDate As String
    Dim endDate As String
    Dim lastRow As Long
    Dim bottomA As Long
    Dim ws As Worksheet
    desWS.Range("A1:H1") = Array("Course Type", "Invoice No.", "Lecturer", "Description", "Net Amount", "Invoice Date", "Exchange", "Classification")
    For Each ws In srcWB.Sheets(Array("Purchase  USD", "Purchase EUR"))
        If ws.Name = "Purchase  USD" Then
            bottomA = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
            Intersect(ws.Rows("2:" & bottomA), ws.Range("A:A,D:E,G:G,K:L,N:N,S:S")).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0)
        ElseIf ws.Name = "Purchase EUR" Then
            bottomA = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
            Intersect(ws.Rows("2:" & bottomA), ws.Range("A:A,D:F,J:K,M:M,S:S")).Copy desWS.Cells(desWS.Rows.Count, "A").End(xlUp).Offset(1, 0)
        End If
    Next ws
    response = InputBox("Please enter the search criteria for the course type material.")
    If WorksheetFunction.CountIf(desWS.Range("A:A"), response) = 0 Then
        MsgBox ("Filter criteria not found in Column A.  Please try again.")
        desWS.UsedRange.ClearContents
        Exit Sub
    End If
ReTry1:
    beginDate = InputBox("Please enter the start date in format mm/dd/yyyy", "Beginning date", Format(Now(), "mm/dd/yyyy"))
    If beginDate = "" Then
        MsgBox ("You have not entered a date.")
        desWS.UsedRange.ClearContents
        Exit Sub
    End If
    If Format(beginDate, "mm/dd/yyyy") <> beginDate Then
        MsgBox "Wrong date format. Please enter in format mm/dd/yyyy.": GoTo ReTry1
    End If
ReTry2:
    endDate = InputBox("Please enter the end date in format mm/dd/yyyy", "End date", Format(Now(), "mm/dd/yyyy"))
    If endDate = "" Then
        MsgBox ("You have not entered a date.")
        desWS.UsedRange.ClearContents
        Exit Sub
    End If
    If Format(endDate, "mm/dd/yyyy") <> endDate Then
        MsgBox "Wrong date format. Please enter in format mm/dd/yyyy.": GoTo ReTry2
    End If
    desWS.Activate
    desWS.Columns.AutoFit
    lastRow = desWS.Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    desWS.Range("A1:H" & lastRow).AutoFilter Field:=1, Criteria1:="<>" & response
    If desWS.Range("A2", Cells(desWS.Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row > 1 Then
        desWS.Range("A2:H" & lastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End If
    If desWS.AutoFilterMode = True Then desWS.AutoFilterMode = False
    desWS.Range("A1:H" & lastRow).AutoFilter Field:=6, Criteria1:="<" & CDate(beginDate)
    If desWS.Range("A2", Cells(desWS.Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row > 1 Then
        desWS.Range("A2:H" & lastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End If
    If desWS.AutoFilterMode = True Then desWS.AutoFilterMode = False
    desWS.Range("A1:H" & lastRow).AutoFilter Field:=6, Criteria1:=">" & CDate(endDate)
    If desWS.Range("A2", Cells(desWS.Rows.Count, "A").End(xlUp)).SpecialCells(xlCellTypeVisible).Cells(1, 1).Row > 1 Then
        desWS.Range("A2:H" & lastRow).SpecialCells(xlCellTypeVisible).EntireRow.Delete
    End If
    If desWS.AutoFilterMode = True Then desWS.AutoFilterMode = False
    Application.ScreenUpdating = True
End Sub

 

[mumps]

Re: Need help with VBA coding

I forgot to mention that you should format the Invoice Date column on "Purchase USD" and "Purchase EUR" as Custom .... mm/dd/yyyy