Copying to first empty row?

[terrypin]

I finally have the copying aspects of my macro working, after much help here thanks. To discuss the next stage I've attached a much simplified version of both source and destination workbooks used.

The macro currently copies data from Source-Simplified.xlsm to fixed row 8 of WalkIndex-Extract.xlsm

Sub Extract_Simplest()

Dim wi As Workbook
Set wi = Workbooks("WalkIndex-Extract.xlsm")

Dim wb As Workbook
Set wb = Workbooks("Source-Simplified.xlsm")

'Track name
wb.Worksheets("Track Data").Range("B5").Copy _
Workbooks("WalkIndex-Extract.xlsm").Sheets("Target").Range("C8")
  
'Main FW Heading (C6 & B3 in formula)
wb.Worksheets("Track Data").Range("B27").Copy
wi.Sheets("Target").Range("D8").PasteSpecial Paste:=xlPasteValues

End Sub

I want to edit that to copy instead to the first row in which col A is empty, row 4 in my example. The first run would therefore enter two cells into row 4, instead of row 8. And so on, as the number of rows increases.

This code finds the first empty row OK.

Sub FirstEmptyRowA1()
'Find the first empty row in Column A
Dim lngLastRow As Long
lngLastRow = Cells(Rows.Count, "A").End(xlUp).Row

Dim FirstEmpty As Long
FirstEmpty = lngLastRow + 1

MsgBox (FirstEmpty)

End Sub

But attempts to use 'FirstEmpty' in Sub Extract_Simplest() have so far defeated me and any help would be appreciated please

[Sintek]

Option Explicit

Sub Extract_Simplest()
Dim nr As Long, wi As Workbook, wb As Workbook, ws As Worksheet
Set wb = Workbooks("Source-Simplified.xlsm")
Set wi = Workbooks("WalkIndex-Extract.xlsm")
Set ws = wi.Sheets("Target")
With ws
    nr = .Cells(.Rows.Count, "C").End(xlUp).Row + 1
    wb.Worksheets("Track Data").Range("B5").Copy ws.Range("C" & nr)
    wb.Worksheets("Track Data").Range("B27").Copy
    ws.Range("C" & nr + 1).PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False
End Sub

[terrypin]

Many thanks @sintek, appreciate the fast reply.

Your code correctly copies source B5 to C4, but B27 should go to D4, not C5. However, you would not have been able to spot this by running your macro because I must have saved the source while B27 was empty, sorry!

As you've done the hard part I was able to make the two minor edits and it now works fine, thanks. I've attached updates of the sheets.

Sub Extract_Simplest_sintek_edited()
Dim nr As Long, wi As Workbook, wb As Workbook, ws As Worksheet
Set wb = Workbooks("Source-Simplified.xlsm")
Set wi = Workbooks("WalkIndex-Extract.xlsm")
Set ws = wi.Sheets("Target")
With ws
    nr = .Cells(.Rows.Count, "C").End(xlUp).Row + 1
    wb.Worksheets("Track Data").Range("B5").Copy ws.Range("C" & nr)
    wb.Worksheets("Track Data").Range("B27").Copy
    ws.Range("D" & nr).PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False
End Sub

Next minor step will be to add a new line each time, to keep TOTALS below the last entry. And then the slog of extending the simplified to the full version. But I'm optimistic of having a working macro soon, which I can then apply to a hundred or two source files.

[Sintek]

TOTALS below the last entry

Totals of what...Nothing to sum?

[terrypin]

Totals of what...Nothing to sum?

That's just text at present, for this simplified version. The full macro counts all rows from row 2 downward to the last one.

I've added a line that keeps the space.

Sub Extract_Simplest_InsertRow()
Dim nr As Long, wi As Workbook, wb As Workbook, ws As Worksheet
Set wb = Workbooks("Source-Simplified.xlsm")
Set wi = Workbooks("WalkIndex-Extract.xlsm")
Set ws = wi.Sheets("Target")
With ws
    nr = .Cells(.Rows.Count, "C").End(xlUp).Row + 1
    wb.Worksheets("Track Data").Range("B5").Copy ws.Range("C" & nr)
    wb.Worksheets("Track Data").Range("B27").Copy
    ws.Range("D" & nr).PasteSpecial xlPasteValues
End With
Application.CutCopyMode = False
'Insert row below to keep a few rows free before TOTALS
ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrAbove
End Sub

[Sintek]

Okey dokie...Thanks for rep +

[terrypin]

This line, to insert a blank row after the last entry was pasted, worked fine in the simplified version I posted earlier. But in the full version it now highlights 'xlFormatFromRightOrAbove' with the error "Compile error: Variable not defined".

No variable was defined before. And isn't this a parameter, not a variable anyway?

ActiveCell.Offset(1).EntireRow.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromRightOrAbove

[Sintek]

Think you should upload a workable file with your actual requirement for this TOTAL...Your solution might not be the best alternative...
Perhaps you should start a new thread depicting your requirement, as it no longer pertains to this thread...

[terrypin]

Not exactly sure how, but I found a winning combination after a couple of hours of experiment and happily that snag is now resolved thanks.

You’re right, I realise now that I should have started a new thread, given that I’d already set this one as solved. There are a few others and I’ll post separately if I can’t fix them myself.