VBA Macro Copy to next empty row, different sheet

[xenohadden]

Hey guys, so I made a macro to copy data from my data entry sheet to my database sheet:

Private Sub CommandButton1_Click()
        
    Application.ScreenUpdating = False
     
    Dim NextRow As Range
    
    Set NextRow = Sheet4.Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0)
    
    Sheet2.Range("d3,d5,d7,d9,d11,d13,d15,d17,d19,d21,d23,d25,d27,d29,d33,d35").Copy
    
    'copy to next page
    Sheets("September").Select
    Selection.PasteSpecial (xlValues), Transpose:=True


    Application.CutCopyMode = False
    Application.ScreenUpdating = True
      
    Me.Hide
    
End Sub


Private Sub CommandButton2_Click()
        
   Me.Hide
            


End Sub

The main to problems are that its not pasting in in the right collum and its not passing the next entry in the next free row.

Have attached a screenshot of the two sheets, at the sheet has some personal data, i won't be able to upload it.





Hope someone can help me wrap my head round this, been searching all over the web all day !

 

[xenohadden]

Hi again,

Personally I try to avoid using the sharing feature - I've experienced so many issues with shared workbooks!

However, if sharing the workbook is your only option you probably need to check if the workbook is in "MultiUserEditing" mode, automatically accept all changes, save the workbook, immediately run your code to update the records, and (belt and braces!) save the workbook again.

Something like the following might work for the check, update and save before you run your code, but no guarantees!

    With ThisWorkbook
        If .MultiUserEditing Then
            .AcceptAllChanges
            .Save
        End If
    End With
    'Run your code to do the paste here
    With ThisWorkbook
        If .MultiUserEditing Then
            .AcceptAllChanges
        End If
        .Save
    End With

... but like I say, I try as hard as possible to stay away from shared workbooks - I prefer to use a proper database management system for the kinds of solution that needs many users to access and update the same data.

Ay would love to just use some of my SQL knowledge and out together a smashing database, but sadly work doesn't have the facilitiy and the sheet will need to be worked on by multiple people at once.

I will give your coding a go, thanks!

 

[xenohadden]

Hi again,

Personally I try to avoid using the sharing feature - I've experienced so many issues with shared workbooks!

However, if sharing the workbook is your only option you probably need to check if the workbook is in "MultiUserEditing" mode, automatically accept all changes, save the workbook, immediately run your code to update the records, and (belt and braces!) save the workbook again.

Something like the following might work for the check, update and save before you run your code, but no guarantees!

    With ThisWorkbook
        If .MultiUserEditing Then
            .AcceptAllChanges
            .Save
        End If
    End With
    'Run your code to do the paste here
    With ThisWorkbook
        If .MultiUserEditing Then
            .AcceptAllChanges
        End If
        .Save
    End With

... but like I say, I try as hard as possible to stay away from shared workbooks - I prefer to use a proper database management system for the kinds of solution that needs many users to access and update the same data.

unfortunatley i'd dont have access to anything i would be able to run a database from, would love to put some of the SQL knowledge I have gained into practice, but hey, i've got what i've got to work with, nothing more.

Will give your code a try, thanks again!

 

[AnAnalyst]

Ay would love to just use some of my SQL knowledge and out together a smashing database, but sadly work doesn't have the facilitiy

You're welcome!

There is always the option to use technologies like ADO within Excel VBA - this can give you the best of both worlds (to an extent!) and is usually a better option than shared workbooks. If you're familiar with databases and SQL then ADO is a possible option for you in the future. I often use ADO to issue SQL to large Oracle databases and this allows me to work faster, smarter and more reliably.

All the best.