Help! Pasting data to sheets based on names

[Xineq]

Hey Mr. Excel

I have this Code seen below. The point of the macro is to past data from my source sheet to sheets with matching names with some cell values.

I have the following sheet setup

	R (Sheet names)	S	T	U	V	W
5	Overview 2014-4	1	1	6	9	17
6	Overview 2015-1	6	0	3	12	21
7	Overview 2014-3	2	0	3	1	6

<tbody>
</tbody>

More specific info:
In my source sheet named "Counting" i have sheet names in column R and i want to split the data onto all the sheets that matchs with the cell name of the sheets. So I want to copy columns S to the end onto columns A(lastrow) on the destination.




This is my Code i trying to do it with: I just get a problem in the last line, getting the error "Application-defined or object-defined error" "Run time error 1004"

[COLOR=#ffd700]Sheets(strDestinationSheet).Range(Cells(lastRow + 1, 1), Cells(lastRow + 1, 1)).PasteSpecial xlPasteValues[/COLOR]

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, "A")
        [COLOR=#ffd700]Sheets(strDestinationSheet).Range(Cells(lastRow + 1, 1), Cells(lastRow + 1, 1)).PasteSpecial xlPasteValues[/COLOR]
    Next
End Sub
Public Function LastRowInOneColumn(sheetName As String, col As String)
    LastRowInOneColumn = Sheets(sheetName).Cells(Sheets(sheetName).Rows.Count, col).End(xlUp).Row
End Function

(When i debug)

Hope you can help me

 

[venkat1926]

try this code slip

Sheets(strDestinationSheet).cells(rows.count,"A").end(xlup).offset(1,0).PasteSpecial xlPasteValues

 

[Xineq]

Hey Venkat

when i try your code, it gives me the error message subscript is out of range. When i debug it this is what i get.

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

Btw thanks for the help and sorry for the late feedback

 

[venkat1926]

subscript is out of range.

means that that named sheet is not available
check spelling of the sheet name in the code

 

[Xineq]

Arhh thanks, it's because i some time have some blanks cells that it cant find do you know how to tell the code to ignor them

i tryed with

SkipBlanks:=True

 

[venkat1926]

not clear. the named sheet is not at all available in the workbook. then question of blank cells does not arise
if there is a sheet named like that the error will not be subscript out of range.

clarify.