Problem with Macro error 9

[Xineq]

Hey Mr. Excel

i have this 2 Subs, and 4 excel sheets

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

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

 

[par60056]

I try never to use selection and activecell. Any time you select and operate off of "selection." it slows the macro. There are times when it makes the code easier to change which sheet is active because you don't have to specify the sheet on every Cell or Range access.

You did not say what line you are getting the error on.

If I understand your macro, your source sheet has a sheet name in column R and you want to split the data onto all the sheets. So you want to copy columns S to the end onto columns A(lastrow) on the destination.

See if this does what you want.

Sub copyPasteData()
    
    Dim strSourceSheet As String
    Dim strDestinationSheet As String
    Dim lastRow As Long
    Dim lastSourceRow As Long
    Dim currRow As Long
    
    strSourceSheet = "Counting"
    
    Sheets(strSourceSheet).Visible = True
    Sheets(strSourceSheet).Select
    lastSourceRow = LastRowInOneColumn(strSourceSheet, "R")
    
    For currRow = 5 To lastSourceRow
        strDestinationSheet = Cells(currRow, 18)
        Range(Cells(currRow, 19), Cells(currRow, Cells(currRow, 19).CurrentRegion.Columns.Count)).Copy
        lastRow = LastRowInOneColumn(strDestinationSheet, "P")
        Sheets(strDestinationSheet).Range(Cells(lastRow + 1, 1), Cells(lastRow + 1, 1)).PasteSpecial xlPasteValues
    Next
End Sub
Public Function LastRowInOneColumn(sheetName As String, col As String)
    LastRowInOneColumn = Sheets(sheetName).Cells(.Rows.Count, col).End(xlUp).Row
End Function

 

[Xineq]

Hey par60056

Sorry for that latye feedback, and i'm sorry for not giving a better discription of my problem.

Yes you do understand my code

If I understand your macro, your source sheet has a sheet name in column R and you want to split the data onto all the sheets. So you want to copy columns S to the end onto columns A(lastrow) on the destination.

I just get 1 problem when running your code, i get a error message saying "Invalid or unqualified reference"

[COLOR=#daa520]Public Function LastRowInOneColumn(sheetName As String, col As String)[/COLOR]
    LastRowInOneColumn = Sheets(sheetName).Cells([COLOR=#0000ff].Rows[/COLOR].Count, col).End(xlUp).Row
End Function

(When i debug)

 

[par60056]

sorry didn't see that reference. It should reference sheets(sheetName) and so should be:


LastRowInOneColumn = Sheets(sheetName).Cells(Sheets(sheetName).Rows.Count, col).End(xlUp).Row</pre>

 

[Xineq]

Thanks you so much really took a load of my work day :D

Sorry for the late answer