Writing data to a sheet duplicates the data for unknown reason 1

[davedave24]

Hello! My procedure writes the data to a sheet, from rows B-V. It writes the data from B-V, then repeats it 5 times down to cell AU. It doesn't write the 20 pieces of data exactly though (B-V writes normally); starting at W, it writes what went in column D; X will contain what went in column E, and so on, as shown below:

D - E - F - G - H - blank - I - blank - K - J - no idea - no idea - repeat another 4 times

The two "no idea" - it puts 2 values in that I don't know where from - I didn't enter them into my form, and they aren't from any of the normal columns (B-V), in fact, they are nowhere on the sheet.

I don't believe it's the code that actually *writes* the data to the sheet, as that is used to write to an identical sheet in another workbook and it works fine.

Here's my code:

I have 2 worksheets, called 'skipleft', and 'skipright'. The user selects one from a combobox. When they hit the ENTER button, it calls EnterSkipData, then SkipDataWorkbook.

edit: I've been playing around with this. If I don't call the SkipData procedure, and just write textDate in on it's own in the first cell, it puts all the other values from the userform into the other colums without being told to (in the wrong places). What is causing this? It's driving me mad!

I've also changed the SkipLR variable to make it just write to the skipleft worksheet, but it does the same

First, this writes to the skipleft or skipright worksheet. The variable SkipLR is determined from a combobox and chooses which sheet.

Sub EnterSkipData()
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' WRITE THE DATA TO THE SKIPLEFT/SKIPRIGHT WORKSHEET
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    On Error GoTo ErrHandler:
ErrHandler:
    SkipMsg = "There was a problem writing the data to the Skip (left/right) worksheet in the All Deliveries 2010 workbook. This shouldn't have happened!" & vbCrLf & vbCrLf
    Resume Next
    
    'Windows("All Deliveries 2010").Activate                         'enter the data in skipleft/right
    With Workbooks("All Deliveries 2010").Sheets(SkipLR)            'select the skipleft/skipright sheet depending on the variable determined by comboskip_change()
        .Select
        .Cells(Rows.Count, 2).End(xlUp).Select                      'find the last row in B
        ActiveCell.Offset(1, 0).Select                              'move down 1 row
        SkipData                                                    'procedure that enters the data
        SkipMsg = ""                                                'all went well, no need for an error message at the end
    End With
End Sub

This is the procedure that just writes the data. AddBorders is a public sub in a module that just adds a border to the cell. I use this method to write data to several other sheets without any issue. SkipDataWorkbook is another procedure that opens a workbook with the exact same layout as the skipleft/skipright sheet, and writes the data there. That one works fine.

Sub SkipData()
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' THIS WRITES THE DATA INTO THE SKIP WORKSHEETS.  CALLED BY EnterSkipData AND SkipDataWorkbook
    '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ActiveCell = textDate.Text
    AddBorders                              'adds a border to the cell
    ActiveCell.Offset(0, 1).Select
    ActiveCell = comboCustomer.Text
    AddBorders
    ActiveCell.Offset(0, 1).Select
    ActiveCell = textConsignmentNote
    AddBorders

    'etc. 20 pieces of data, I won't list it all but it's the same as above

    'insert a new row
    ActiveCell.Offset(1).EntireRow.Insert

This is the SkipDataWorkbook, which opens a workbook (with the same layout as the sheet), and writes the data with no issue, in case someone asks for it.

Sub SkipDataWorkbook()
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ' WRITES THE DATA TO THE SKIP DATA WORKBOOK.  THIS IS CALLED BY EnterSkipData()
    ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    On Error Resume Next
    
    Dim SkipWorkbook As Workbook
    Dim p As String
    p = "C:\Users\Dave\Documents\xl\SKIP DATA "                         'file path of the SKIP DATA workbook
    If FileThere(p & SkipNumber & ".xlsx") Then                         'check if the file exists, open it
        Set SkipWorkbook = Workbooks.Open(p & SkipNumber)               'SKIP DATA workbook
        With SkipWorkbook.Worksheets("Sheet1")
            .Cells(Rows.Count, 2).End(xlUp).Select                      'find last row in column B
            ActiveCell.Offset(1, 0).Select                              'move down 1 row
            SkipData                                                    'procedure that enters the data
        End With

        SkipWorkbook.Save                                               'save
        SkipWorkbook.Close                                              'close
        SkipDataMsg = ""                                                'all went well, no need for an error message at the end
    Else        'the workbook could not be found.  This should not happen as the workbook is created when a new skip is created
        SkipDataMsg = "There was a problem writing the data to the SKIP DATA workbook. The SKIP DATA " & SkipNumber & " file doesn't exist. You must manually create the skip file now and enter the data." _
        & vbCrLf & vbCrLf
   End If
End Sub

This explains the variable SkipLR, used to determine if the user selects left or right skip. This doesn't appear to be the issue.

Private Sub comboSkip_Change()
    'cut off the words to leave only the skip name
    Select Case comboSkip.ListIndex
        Case 0:     SkipNumber = Mid(comboSkip, 12)                     'left skip
                    SkipLR = "skipleft"
        Case 1:     SkipNumber = Mid(comboSkip, 13)                     'right skip
                    SkipLR = "skipright"
    End Select
End Sub

And this is where the skip combobox is filled on userform_initialize. It's simply getting the name of the skip from cell B2 on the skipleft or skip right worksheet.

    comboSkip.AddItem "Left skip" & vbTab & Mid(Workbooks("All Deliveries 2010").Sheets("skipleft").Range("B2").Value, 5)
    comboSkip.AddItem "Right skip" & vbTab & Mid(Workbooks("All Deliveries 2010").Sheets("skipright").Range("B2").Value, 5)
    comboSkip.ListIndex = 0

This is a crosspost on ozgrid forums

http://www.ozgrid.com/forum/showthread.php?t=150929&p=543514#post543514

Thanks for any help

 

[PHV]

Anyway, replace this:
.Cells(Rows.Count, 2).End(xlUp).Select
with this:
.Cells([!].[/!]Rows.Count, 2).End(xlUp).Select

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[davedave24]

Thanks PHV but that didn't do it.

 

[yooneek]

You could always put in a flag in the data writing area:

If ActiveCell.Column = 23 Then
MsgBox "Here is where things are going wrong"
On Error Resume Next
End If

Select the On Error line as a Break, that way you can go back and look at what other variables are triggered.

Don't suppose you'd be willing to post the workbooks?

 

[davedave24]

I've determined the problem is that something else is telling it to write the data. I've run the procedure on it's own from a button and it works okay. I'll see if I can bundle up the workbooks in a zip

 

[davedave24]

Okay I've uploaded the files

http://www.mediafire.com/?8dmkk6trhv2cey3

Hopefully I've got them all there. Just open the Aug10 file, click the button on sheet1. You'll need to put all these files in c:\users\documents\Dave\xl\ for it to work.

Thanks a bunch!

 

[davedave24]

OOPS Try this one instead:

http://www.mediafire.com/?ofwz3r1glipy3df

I didn't include everything in the first one. Same as before.

 

[yooneek]

Took some digging, but I think I have it mostly ironed out...

1) Workbook calling - The main problem was the way you were calling workbooks and the error handling. In some cases you would call ??.xls, which is correct, but in other cases you would just have the workbook name in "??" without the extension, and becuase of the error handler it would just pass over these file locations and continue with the code...

2) Application.DisplayAlerts - There were a couple of segments of code where you had it =False at the begining and didn't turn it =True at the end. This can create problems with notifications.

3) Variable Naming - The Customer combo list generator used 'val' as the variable, and in another procedure you had 'path' Dim'd as a variable. val is a built in VBA function and Path is used for file locations.

4) File Path location - In some cases you had C:\..\Documents\.. and in others you had C:\..\My Documents\.. If you want to have everything located under a master folder location, I'd recommend always using ThisWorkbook.Path for the current file location, or you can declare a global variable with a base file location and then extend from there for making new folders for the clients. (I just used ThisWorkbook.Path to keep all new files under the same folder as this one.)

-----

Upgrades/Enhancements/etc

1) I've rearranged the initial code of the Waste form, so that the sheet calling would be easier.

2) Changed the way the SkipData function enters the data. I tell the cells what value to be without selecting them first, then I moved the apply the AddBoarders function at the end (applied to the range). (this was also done for any mass data placement)

3) Fixed the Total calculations in the form. You formated the Value of each with a financial symbol, which converted that into a text. You cant apply arithmatic to text, so I replace the character and then multiply the values.

-----

I checked with both Left and Right, then created a new Right... worked almost every time... I'm not sure what was happening, but everyonce in a while it would not want to populate any other sheet than Sheet1 (button sheet), but when I re-ran it, it would work perfectly. I think it was just some memory problems on my side.

I've uploaded the forms, just replace the ones you have and check them out.

 

[davedave24]

Wow thanks yooneek! I really appreciate you doing all that!

Thanks again!

 

[yooneek]

Glad I could help.

Have you had a chance to implement it yet? Come across any issues?

 

[davedave24]

Yeah I've put it all in. Few small issues where the file string was missing a few words, you had missed out the filename (so it should be \xl\companyname\companyname weekly report.xls, you had \xl\companyname weekly report.xls).

The other only issue now is that it doesn't write to the second sheet of the weekly report if the first sheet is full (the 25 lines). It always goes to the last line and overwrites.

 

[yooneek]

Company Sub Folder: Sorry about that, thought I had put that in my reply to you... I took it out to make it easier to troubleshoot (trying to keep all the files in the same folder).

Weekly Report: Remove the ThisWorkbook from the For j statement (should be For j = 1 To Worksheets.Count), and for some reason it wasn't allowing the new row selection of the next sheet without the sheet being activated. Add a Select statement after the count row check:
If nextrow < 25 Then
.Select
.Cells(31 + nextrow + 1, 2).Select

I've tested it out to Sheet4.

----

Complete Revision of your Weekly Template:
(standardize the Weekly template and allow for unlimited sheets…)

1) delete Sheets 3-7;
2) take Sheet1, rename to Sheet_Blank, and make it Hidden (easy to do in VBA properties);
3) Rename Sheet2’s Name (tab name) to Sheet1.
4) Change the code to this:
If FileThere(wk) Then 'The workbook already exists
Workbooks.Open (wk)

Dim nextrow As Long, j As Long

On Error GoTo 0

j = Worksheets.Count
nextrow = Application.WorksheetFunction.CountA(Sheets(j).Range("B32:B56"))
If nextrow >= 25 Then
'unhide and copy
Sheets(1).Visible = -1 'Visible
Sheets(1).Copy After:=Sheets(j)
Sheets(1).Visible = 0 'Hidden

'reset the count
j = Worksheets.Count

'rename the new sheet to maintain naming convention
Sheets(j).Name = "Sheet" & j - 1

'Copy over Customer and Week number
With Sheets(j)
.Cells(27, 3).Value = Sheets(j - 1).Cells(27, 3).Value
.Cells(27, 14).Value = Sheets(j - 1).Cells(27, 14).Value
End With

'reset starting row value
nextrow = 0
End If

Sheets(j).Select
Sheets(j).Cells(31 + nextrow + 1, 2).Select
PasteDataWeeklyDeliveryReport
ActiveWorkbook.Save
ActiveWorkbook.Close

Else

As you can see, this eliminates the need for a loop and will ensure that all of your sheets look the same (hidden template, within the template )

 

[davedave24]

Many thanks yooneek! I've altered it to that and it works flawlessly.

 

[yooneek]

I was thinking... you could also apply the hidden sheet process to the WeeklyLayout in All Deliveries. That way you don't have to worry about someone using it inadvertently.

Also (this would take some time to implement), but I was thinking that you could assign Form level variables to capture the values on the EnterWaste form, and then use those in your pasting areas. This is not a critical thing, but would speed up the overall process, and would minimize the interaction between Sheet(s)<=>Form.