Hi there

I have a problem. My code works perfect, but now I want to, instead of displaying multiple sheets, display all data in one sheet. Below is my code I used:

Private Sub Picture2_Click()

Screen.MousePointer = vbHourglass

Dim tempAss As String
Dim xlApp As Excel.Application
Dim wb As Workbook
Dim ws As Worksheet
Dim hold() As String

Dim g As Integer

Set xlApp = New Excel.Application
Set wb = xlApp.Workbooks.Open(folder & "Templates\Expense Report.xls")

Dim oucnt As Integer
Dim expense_amount As Single
expense_amount = 0

outcnt = 0

Dim E_temp1() As String
Dim E_temp2() As String

For j = 0 To assistants.ListCount - 1
    If assistants.Selected(j) Then
        For i = 0 To nu - 1

            If assistants.List(j) = userlog(i).UName Then
                g = 0
                Call Read_TMdata(userlog(i).UID + ".txt")
                Set ws = wb.Worksheets(assistants.List(j)) 'Specify your worksheet name
                For k = 0 To TMcnt - 1
                If TMdata(k).Expenses <> "" Then
                If DateValue(TMdata(k).EDate) >= DateValue(Label4.Caption) And DateValue(TMdata(k).EDate) <= Label6.Caption Then

                    E_temp1 = Split(TMdata(k).Expenses, "$$")

                    For ep = 0 To UBound(E_temp1)

                    If E_temp1(ep) <> "" Then
                    E_temp2 = Split(E_temp1(ep), vbTab)

                    If E_temp2(0) <> "" Then
                        ws.Cells(g + 4, 1).Value = TMdata(k).EDate
                        If InStr(1, TMdata(k).JobCode, "%d%", 1) <> 0 Then
                            hold() = Split(TMdata(k).JobCode, "%d%")
                            ws.Cells(g + 4, 2).Value = hold(0)
                            ws.Cells(g + 4, 2).Value = TMdata(k).JobCode
                        End If

                       For n = 0 To nu - 1
                            If userlog(n).UID = TMdata(k).UID Then
                                ws.Cells(g + 4, 3).Value = userlog(n).UName
                            End If
                       Next n

                       ws.Cells(g + 4, 3).Value = E_temp2(0)

                       ws.Cells(g + 4, 4).Value = E_temp2(6)
                       ws.Cells(g + 4, 5).Value = E_temp2(7)
                       ws.Cells(g + 4, 6).Value = CSng(E_temp2(6)) * CSng(E_temp2(7))

                       expense_amount = expense_amount + (CSng(E_temp2(6)) * CSng(E_temp2(7)))
                   End If
                   g = g + 1
                   End If
                   Next ep
                   End If
                   End If
                Next k
            End If
        Next i
    End If
Next j

Call Read_TMdata(user.UID + ".txt")

xlApp.Visible = True

Set ws = Nothing
Set wb = Nothing
Set xlApp = Nothing

Screen.MousePointer = vbDefault

End Sub

Can anyone help me please?

6 Years
Discussion Span
Last Post by AndreRet


Set ws = wb.Worksheets(assistants.List(j)) 'Specify your worksheet name

this part from your code and all that is referencing it. It opens a specific sheet for you. By removing it, you keep to only one sheet.


Ahh okay I see, but if I remove it it tells me "object variable or with block variable not set and when I debug it, it points to this piece of code:

ws.Cells(g + 4, 1).Value = TMdata(k).EDate

Now this I'm figuring has to do with the cells inside the sheet?

Edited by TheDocterd: n/a


I have added a sheet in the document called "Expenses". But now when I make all the data display there, I only get some of the expenses...??

Set ws = wb.Worksheets("Expenses") 'Specify your worksheet name

"With block or variable not set" refers to a name function in your code that has not been referenced or dimensioned.

In this case it can be any of these 3 "TMdata(k).EDate", TMDate, k, EDate.

Did you manage to get sorted on the one sheet only problem?


Seems that I am behind here, sorry for that.

Where does the "xpenses" data coming from. It might be that you are running a loop through all recordsets, but it "cuts" off at a certain part...


Well it's sounds real simple...I read the expenses data from txt files of users. In my code I use "call read_tmdata". This is where the txt file is taken up in segments.

But all that is correct. I'm guessing my problem lies at the code I gave. I tried disabling the list box where I select what user's expense data to display but nothing


Have a look at the following code, it imports 100%. Paste it after your code to open the excel workbook -

Public Sub ImportTextFile(FName As String, Sep As String)

Dim RowNdx As Long
Dim ColNdx As Integer
Dim TempVal As Variant
Dim WholeLine As String
Dim Pos As Integer
Dim NextPos As Integer
Dim SaveColNdx As Integer

Application.ScreenUpdating = False
'On Error GoTo EndMacro:

SaveColNdx = ActiveCell.Column
RowNdx = ActiveCell.row

Open FName For Input Access Read As #1

While Not EOF(1)
    Line Input #1, WholeLine
    If Right(WholeLine, 1) <> Sep Then
        WholeLine = WholeLine & Sep
    End If
    ColNdx = SaveColNdx
    Pos = 1
    NextPos = InStr(Pos, WholeLine, Sep)
    While NextPos >= 1
        TempVal = Mid(WholeLine, Pos, NextPos - Pos)
        Cells(RowNdx, ColNdx).Value = TempVal
        Pos = NextPos + 1
        ColNdx = ColNdx + 1
        NextPos = InStr(Pos, WholeLine, Sep)
    RowNdx = RowNdx + 1

On Error GoTo 0
Application.ScreenUpdating = True
Close #1
' END ImportTextFile
End Sub

Sub DoTheImport()
ImportTextFile FName:="C:\Test.txt", Sep:="|"
End Sub

I got this from HERE. You might find the other code samples helpful as well.


Thanks for the link. Very useful :)

I don't know why it keeps doing this, but like i mentioned earlier I've created a new template with one sheet called "Expenses". Now when I export the data to the sheet, it only displays one sheets data. How I've noticed this is I've changed it back to the old sheets method and saw the sheets data it displays in the new template I've created.

Isn't there perhaps a way in excel himself where I can create a function to get all the other sheets data and display them all in the "Expenses" sheet??


Try something like -

Dim iSheets As Integer

For iSheets = 0 To MyWorkbookName.Worksheets
   'Code to copy the data into here
Next iSheets

This will start at the first sheets and loop through all sheets to the end.

This topic has been dead for over six months. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.