I need to show at sheet1 in Cell "A1" last number on a last row from sheet2 Column A, but it does not appear as expected...

[reinsermat]

Hello!


Sorry for bad english, I really hope, you do understand the question.

I have problem. My Paste works well, but I lost my "Next enter ID"
I need to show at sheet "P" (Sheet1) in Cell "A1" last number on a last row from sheet "p_koond" (Sheet2) Column A, but it does not appear as expected...
Anybody can offer a solution?
i'am very new on this, so every move takes a ton of time.

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- HERE START'S THE PROBLEM?
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]

Do you want to copy the entire row across, or just one cell?

 

[reinsermat]

Do you want to copy the entire row across, or just one cell?

Thank you for quick reply!

I would like to have only the last active cell in column A (picture included)

 

[Fluff]

In that case replace this

With Sheets("p_koond") '<--| reference "source" sheet- HERE START'S THE PROBLEM?
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

with

      Worksheets("p").Range("A1").Value = Sheets("p_koond").Range("A" & Rows.Count).End(xlUp).Value

 

[reinsermat]

Seems, that nothing happens. Just empty Cell.
Maybe this problem is caused by using on sheet also this code:

Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errh
Dim t As Long
If Not Intersect(Target, Range("B:B")) Is Nothing Then
Application.EnableEvents = False
Range("A:A").ClearContents
t = 0
For Each r In Range("B:B").SpecialCells(xlCellTypeConstants)
If r.Row <> 1 Then
t = t + 1
r.Offset(, -1).Value = t
End If
Next
errh:
Application.EnableEvents = True
End If
End Sub

But my code looks now like this. Not working...

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

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

       Range("D3:N6").Select
    Range("N6").Activate
    Selection.ClearContents
   
    Worksheets("P").Range("A1").Value = Sheets("p_koond").Range("A" & Rows.Count).End(xlUp).Value

End Sub

 

[Fluff]

Do you have formulae in col A of the p_koond sheet?

 

[reinsermat]

Do you have formulae in col A of the p_koond sheet?

Yes

 

[Fluff]

Ok, in that case use

      Worksheets("p").Range("A1").Value = Sheets("p_koond").Range("A:A").Find("*", , , , xlByRows, xlPrevious, , , False).Value

 

[reinsermat]

Thank you!
For me you are a superhero.
I feel so stupid...
I'am not a computer person, but I'am good at my work (environment for 25 years) and there is no computer person who can understand my needs about excel (or to combine mysql, & autocad & html etc...) so I have to figure it out step by step on my own.

6 months now... pff, but I'am very close now
So, you are really my hero!

Rein Sermat

 

[Fluff]

You're welcome & thanks for the feedback.