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 !

 

[AnAnalyst]

Hi,
Assuming "NextRow" is the row you want to copy to on the "September" sheet (aka Sheet4).

Dim NextRow As Range
    With Sheet4
            Set NextRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
    End With
    Sheet2.Range("d3,d5,d7,d9,d11,d13,d15,d17,d19,d21,d23,d25,d27,d29,d33,d35").Copy
    NextRow.PasteSpecial Paste:=xlValues, Transpose:=True
    Application.CutCopyMode = False
    Set NextRow = Nothing

 

[xenohadden]

Hi,
Assuming "NextRow" is the row you want to copy to on the "September" sheet (aka Sheet4).

Dim NextRow As Range
    With Sheet4
            Set NextRow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0)
    End With
    Sheet2.Range("d3,d5,d7,d9,d11,d13,d15,d17,d19,d21,d23,d25,d27,d29,d33,d35").Copy
    NextRow.PasteSpecial Paste:=xlValues, Transpose:=True
    Application.CutCopyMode = False
    Set NextRow = Nothing

Thank for the reply AnAnalyst, I think i've managed to confuse myself, the next row part was to try and get excel to post onto the next empty row. The normal code is:

Private Sub CommandButton1_Click()
        
    Application.ScreenUpdating = False
    
    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
      
    Me.Hide
    
End Sub


Private Sub CommandButton2_Click()
        
   Me.Hide
            
End Sub

 

[AnAnalyst]

Okay, so you want to paste the data to the next empty row of the sheet called "September", starting at column 2?

 

[xenohadden]

Okay, so you want to paste the data to the next empty row of the sheet called "September", starting at column 2?

Yes cheer man, the first empty cell will be : B4, in the September sheet. Once the macro has copied the data across, the next push of the button should place the next lot of data on the row below.

 

[AnAnalyst]

...in which case ...

    Dim NextRow As Range
    With Sheets("September")
        Set NextRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0)
    End With
    Sheet2.Range("d3,d5,d7,d9,d11,d13,d15,d17,d19,d21,d23,d25,d27,d29,d33,d35").Copy
    NextRow.PasteSpecial Paste:=xlValues, Transpose:=True
    Application.CutCopyMode = False
    Set NextRow = Nothing

 

[Shweta]

Try this:

 Private Sub CommandButton1_Click()   

 Application.ScreenUpdating = False

    Dim NextRow As Range
    
    Sheet2.Range("d3,d5,d7,d9,d11,d13,d15,d17,d19,d21,d23,d25,d27,d29,d33,d35").Copy
    
    Sheets("September").Select
    Set NextRow = Activesheet.Cells(Cells.Rows.Count, 1).End(xlUp).Offset(1, 0)
    NextRow.Select
    Selection.PasteSpecial (xlValues), Transpose:=True


    Application.CutCopyMode = False
    Application.ScreenUpdating = True

 

[xenohadden]

...in which case ...

    Dim NextRow As Range
    With Sheets("September")
        Set NextRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0)
    End With
    Sheet2.Range("d3,d5,d7,d9,d11,d13,d15,d17,d19,d21,d23,d25,d27,d29,d33,d35").Copy
    NextRow.PasteSpecial Paste:=xlValues, Transpose:=True
    Application.CutCopyMode = False
    Set NextRow = Nothing

You sir, are a pure genious, have i mentioned i love you haha! On a serious note thanks alot works perfectly now! Now only got the other bits of tidying to do, you've solved in a few minutes what i spent all day trying to fix :P

 

[xenohadden]

...in which case ...

    Dim NextRow As Range
    With Sheets("September")
        Set NextRow = .Cells(.Rows.Count, 2).End(xlUp).Offset(1, 0)
    End With
    Sheet2.Range("d3,d5,d7,d9,d11,d13,d15,d17,d19,d21,d23,d25,d27,d29,d33,d35").Copy
    NextRow.PasteSpecial Paste:=xlValues, Transpose:=True
    Application.CutCopyMode = False
    Set NextRow = Nothing

quick question mate, how would excel handle this if it was a shared workbook? would it be best to put a force save in it before it pastes the data? Or would there be a better way round it?

 

[AnAnalyst]

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.