add code to a macro add row to paste into another sheet
I have wkbk which includes a master (summary) sheet which I've set up a macro in to take also copy each cell entry over into a corresponding cell within other worksheets. In other words, when I input a date (for tracking a maintenance log) into my 'master' worksheet, I have a separate worksheet for each individual machine to log maintenance performed at specific intervals (i.e. daily, weekly, etc.). So far I have my macro working to transfer dates entered into my 'master' worksheet onto the other worksheets to populate the corresponding cell - to keep from doing double the work. Where I'm having difficulty is getting it to populate the next empty cell, so that I can have a running log on subsequent worksheets.
This is an exerpt of my existing code, the later of which works. I played around with the first section to try getting it to add to next line, and feel I'm on the right track but have a glitch somewhere. Can anyone tell me where I'm going wrong:
Sub TransferOntoAppropriateLogSheet()
Dim CopyRange As Range, NextCell As Range
Set CopyRange = Sheets("MaintenanceLog").Range("B3")
Set NextCell = Sheets("MiterSaw").Cells("A" & Cells.Count).End(x1Up).Offset(1, 0)
Sheets("MiterSaw").Range("A" & NextCell).PasteSpecial Paste:=xlPasteValues
Sheets("MaintenanceLog").Range("f3").Copy
Sheets("MiterSaw").Range("e3").PasteSpecial Paste:=xlPasteValues
Sheets("MaintenanceLog").Range("b4").Copy
Sheets("StraightSaw").Range("a3").PasteSpecial Paste:=xlPasteValues
Sheets("MaintenanceLog").Range("f4").Copy
Sheets("StraightSaw").Range("e3").PasteSpecial Paste:=xlPasteValues
| Attachment | Size |
|---|---|
| Maintenance Log Sheet.xls | 54.5 KB |
Here is a bit of tidier
Here is a bit of tidier code:
Sub TransferOntoAppropriateLogSheet()
With ThisWorkbook.Sheets("MaintenanceLog")
Sheets("MiterSaw").Range("A" & Sheets("MiterSaw").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row).Value = .Range("B3").Value
Sheets("MiterSaw").Range("e3").Value = .Range("f3").Value
Sheets("StraightSaw").Range("a3").Value = .Range("b4").Value
Sheets("StraightSaw").Range("e3").Value = .Range("f4").Value
End With
End Sub
Rather than using Copy and
Rather than using Copy and PasteSpecial...
Maybe use:
Sheets("StraightSaw").Range("e3").value = Sheets("MaintenanceLog").Range("f4").value
As you are only interested in moving values it is not efficient to use the copy function.
To answer your original question:
(you need NextCell to be a number to hold the row, not range):
Dim CopyRange As Range, NextCell As Long
Set CopyRange = Sheets("MaintenanceLog").Range("B3")
NextCell = Sheets("MiterSaw").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).Row
Sheets("MiterSaw").Range("A" & NextCell).PasteSpecial Paste:=xlPasteValues