Hey Mr. Excel
i have this 2 Subs, and 4 excel sheets
<tbody>
</tbody>
the Code will print the lines to the sheets with the same name from column S:W or that is what i have try to make it do, but it will not work tells my that i'm not in range, hope you can help me out
i have this 2 Subs, and 4 excel sheets
Code:
Sub copyPasteData()
Dim strSourceSheet As String
Dim strDestinationSheet As String
Dim lastRow As Long
strSourceSheet = "Counting"
Sheets(strSourceSheet).Visible = True
Sheets(strSourceSheet).Select
Range("R5").Select
Do While ActiveCell.Value <> ""
strDestinationSheet = ActiveCell.Value
ActiveCell.Offset(0, 1).Resize(1, ActiveCell.CurrentRegion.Columns.Count).Select
Selection.Copy
Sheets(strDestinationSheet).Visible = True
Sheets(strDestinationSheet).Select
lastRow = LastRowInOneColumn("P")
Cells(lastRow + 1, 1).Select
Selection.PasteSpecial xlPasteValues
Application.CutCopyMode = False
Sheets(strSourceSheet).Select
ActiveCell.Offset(0, 2).Select
ActiveCell.Offset(1, 0).Select
Loop
End Sub
Code:
Public Function LastRowInOneColumn(col)
Dim lastRow As Long
With ActiveSheet
lastRow = .Cells(.Rows.Count, col).End(xlUp).Row
End With
LastRowInOneColumn = lastRow
End Function
Row | R | S | T | U | V | W |
5 | Overview 2014-4 | 2 | 0 | 2 | 1 | 5 |
6 | Overview 2014-3 | 1 | 0 | 1 | 0 | 2 |
7 | Overview 2015-1 | 2 | 8 | 2 | 0 | 12 |
<tbody>
</tbody>
the Code will print the lines to the sheets with the same name from column S:W or that is what i have try to make it do, but it will not work tells my that i'm not in range, hope you can help me out