Copy range of cells from sheet1 and paste to sheet2 into last row by using macro. Problem

[reinsermat]

Hello!

Excel. Macros VBA.
I'am new on that.
I would like to copy range of cells from sheet1 and paste to sheet2 into last row by using macro.
Problem is, that if in sheet2 column A (or B; C) is empty, then last action (paste) will owerwrite the last row in destination (sheet2)

Just can't get it correct.

Thank you for any ideas!

    Private Sub vaart_md_sis_Click()

    Dim SourceWS As Worksheet, DestWS As Worksheet
    Dim SourceRng As Range, DestCell As Range
    Dim lloop As Long
    Set SourceWS = Sheets("md")
    Set DestWS = Sheets("y_koond")
    Application.ScreenUpdating = 0
    With SourceWS
    Set DestCell = DestWS.Range("a" & Rows.Count).End(xlUp).Offset(1)
    For lloop = 1 To 7
    Set SourceRng = Choose(lloop, .Range("A3"), _
    .Range("B3"), .Range("C3"), .Range("F3"), .Range("G3"), .Range("H3"), .Range("I3"))
    SourceRng.Copy
    DestCell.Offset(, lloop - 1).PasteSpecial xlPasteValues
    Next lloop
    End With
    With Application
    .CutCopyMode = 0
    .ScreenUpdating = 1
    End With 
    End Sub

 

[Fluff]

How about

Private Sub vaart_md_sis_Click()
   Dim SourceWS As Worksheet, DestWS As Worksheet
   Dim NxtRw As Long
   
   Set SourceWS = Sheets("md")
   Set DestWS = Sheets("y_koond")
   Application.ScreenUpdating = 0
   NxtRw = DestWS.Cells.Find("*", , , , xlByRows, xlPrevious, , , False).Row + 1
   SourceWS.Range("A3:C3,F3:I3").Copy
   DestWS.Range("A" & NxtRw).PasteSpecial xlPasteValues
   Application.CutCopyMode = False
End Sub

 

[reinsermat]

How about

Private Sub vaart_md_sis_Click()
   Dim SourceWS As Worksheet, DestWS As Worksheet
   Dim NxtRw As Long
  
   Set SourceWS = Sheets("md")
   Set DestWS = Sheets("y_koond")
   Application.ScreenUpdating = 0
   NxtRw = DestWS.Cells.Find("*", , , , xlByRows, xlPrevious, , , False).Row + 1
   SourceWS.Range("A3:C3,F3:I3").Copy
   DestWS.Range("A" & NxtRw).PasteSpecial xlPasteValues
   Application.CutCopyMode = False
End Sub

Thank you! But this does not seem to work for me. Nothing happens, just blank cells.
added screen how it should appear in sheet2. Last empty row.

 

[Fluff]

If you add a messgae box as shown, what does it say?

   NxtRw = DestWS.Cells.Find("*", , , , xlByRows, xlPrevious, , , False).Row + 1
   MsgBox NxtRw
   SourceWS.Range("A3:C3,F3:I3").Copy

 

[reinsermat]

How about

Private Sub vaart_md_sis_Click()
   Dim SourceWS As Worksheet, DestWS As Worksheet
   Dim NxtRw As Long
  
   Set SourceWS = Sheets("md")
   Set DestWS = Sheets("y_koond")
   Application.ScreenUpdating = 0
   NxtRw = DestWS.Cells.Find("*", , , , xlByRows, xlPrevious, , , False).Row + 1
   SourceWS.Range("A3:C3,F3:I3").Copy
   DestWS.Range("A" & NxtRw).PasteSpecial xlPasteValues
   Application.CutCopyMode = False
End Sub

My bad!
Everything just perfect! Thank you! I love U!

Rein Sermat

 

[Fluff]

You're welcome & thanks for the feedback.

 

[reinsermat]

Ok, but somehow I do have problem. My Paste works well, but I lost my "Next enter ID"
I need to show at sheet "P" in Cell "A1" last number on a row from sheet "p_koond" Column A, but it does not appear as expected...
This thing is way over my head.

Thank you for any help!


Private Sub vaart_sis_GI_Click()
P_vaart_G.Hide
Dim SourceWS As Worksheet, DestWS As Worksheet
Dim NxtRw As Long

Set SourceWS = Sheets("P")
Set DestWS = Sheets("p_koond")
Application.ScreenUpdating = 0
NxtRw = DestWS.Cells.Find("*", , , , xlByRows, xlPrevious, , , False).Row + 1
SourceWS.Range("B3:N3").Copy
DestWS.Range("B" & NxtRw).PasteSpecial xlPasteValues
Application.CutCopyMode = False


Range("D3:N6").Select
Range("N6").Activate
Selection.ClearContents


With Sheets("p_koond") '<--| reference "source" sheet
With Range(.Cells(.Rows.Count, "A").End(xlUp), _
.Cells(.Cells(.Rows.Count, "A").End(xlUp).Row, .Columns.Count).End(xlToLeft)) '<--| reference is range from its column A last not empty cell to this latter cell row last not empty cell
Worksheets("p").Range("A1").Resize(, .Columns.Count).Value = .Value '<--| paste values to "target" sheet starting from its cell A1
End With
End With
End Sub

 

[Fluff]

As this is a different question, you will need to start a new thread. Thanks

 

[reinsermat]

As this is a different question, you will need to start a new thread. Thanks

Thank you for information!

 

[Fluff]

You're welcome.