Hey Mr. Excel
i have this 2 Subs, and 4 excel sheets
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Row[/TD]
[TD]R
[/TD]
[TD]S
[/TD]
[TD]T
[/TD]
[TD]U
[/TD]
[TD]V
[/TD]
[TD]W
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Overview 2014-4
[/TD]
[TD]2
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Overview 2014-3
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Overview 2015-1
[/TD]
[TD]2
[/TD]
[TD]8
[/TD]
[TD]2
[/TD]
[TD]0
[/TD]
[TD]12
[/TD]
[/TR]
</tbody>[/TABLE]
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
[TABLE="class: outer_border, width: 500"]
<tbody>[TR]
[TD]Row[/TD]
[TD]R
[/TD]
[TD]S
[/TD]
[TD]T
[/TD]
[TD]U
[/TD]
[TD]V
[/TD]
[TD]W
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Overview 2014-4
[/TD]
[TD]2
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[TD]1
[/TD]
[TD]5
[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Overview 2014-3
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]1
[/TD]
[TD]0
[/TD]
[TD]2
[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Overview 2015-1
[/TD]
[TD]2
[/TD]
[TD]8
[/TD]
[TD]2
[/TD]
[TD]0
[/TD]
[TD]12
[/TD]
[/TR]
</tbody>[/TABLE]
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