using the copy and paste link into different worksheets using variables

[stephicohu]

This is me again, what I did was take out all the .select and .activate from my subroutine and the code is shown below:

Sub FillingColumnSum(ByVal SourceMonth As String, ByRef TargetMonth As String, ByVal FirstRow1 As Integer, _
ByVal SecondRow1 As Integer, ByVal FirstColumn As Integer, ByVal TargetColumn As Integer)

    Sheets(SourceMonth).Range(Cells(FirstRow1, FirstColumn), Cells(SecondRow1, FirstColumn)).Copy Sheets(TargetMonth).Range(Cells(FirstRow1, TargetColumn), Cells(SecondRow1, TargetColumn))
    ActiveSheet.Paste Link:=True

End Sub

I run the program and I get an error code 9 the subscript is out of range. The debug shows the values right so I must of screw up again. Can you guys help in this matter? should I use a string formula?

 

[stephicohu]

I have revised the code and still got an error code 9.

Sub FillingColumnSum(ByRef SourceMonth As String, ByRef TargetMonth As String, ByRef FirstRow1 As Integer, _
ByRef SecondRow1 As Integer, ByRef FirstColumn As Integer, ByRef TargetColumn As Integer)

    Sheets(SourceMonth).Range(Cells(FirstRow1, FirstColumn), Cells(SecondRow1, FirstColumn)).Copy _
    Destination:=Sheets(TargetMonth).Range(Cells(FirstRow1, TargetColumn), Cells(SecondRow1, TargetColumn))
    ActiveSheet.Paste Link:=True

End Sub

 

[My Aswer Is This]

I think instead of showing us a code that does not work.
Please tell us with specifics what you're wanting the script to do.

 

[Fluff]

You need to qualify all the cells ranges like

Sub FillingColumnSum(ByRef SourceMonth As String, ByRef TargetMonth As String, ByRef FirstRow1 As Integer, _
ByRef SecondRow1 As Integer, ByRef FirstColumn As Integer, ByRef TargetColumn As Integer)

    With Sheets(SourceMonth)
      .Range(.Cells(FirstRow1, FirstColumn), .Cells(SecondRow1, FirstColumn)).Copy _
         Sheets(TargetMonth).Range(Sheets(TargetMonth).Cells(FirstRow1, TargetColumn), Sheets(TargetMonth).Cells(SecondRow1, TargetColumn))
    End With
    ActiveSheet.Paste Link:=True
End Sub

 

[stephicohu]

I have copy your example and the subroutine stills errors out. It gives me: "run-time error 9, subscript out of range." Does this needs to be a function?

 

[stephicohu]

What the script is supposed to do is copy a column of sums from cell(3,33) to cell(7,33). Then, using paste link them to another sheet. I want to use variables because I am repeating the code for different sets rows of the same column later.

 

[Fluff]

If you are still getting a subscript out of range error, it suggests that one of the sheet names is wrong.

 

[stephicohu]

Thank you, It works..... what the problem was the name of the sheet didn't have a space between the word and the number. Thanks again

 

[Fluff]

Glad it's sorted & thanks for letting us know.