Problem with Macro error 9

Xineq

New Member
Joined
Aug 29, 2014
Messages
34
Hey Mr. Excel

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 :)
 

Excel Facts

VLOOKUP to Left?
Use =VLOOKUP(A2,CHOOSE({1,2},$Z$1:$Z$99,$Y$1:$Y$99),2,False) to lookup Y values to left of Z values.
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.

Code:
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
 
Upvote 0
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"

Code:
[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)
 
Last edited by a moderator:
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,223,227
Messages
6,170,853
Members
452,361
Latest member
d3ad3y3

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top