Solved: Cell Name as a variable to feed

[fredlo2010]

Hello,

I have this code that's supposed to go to the sheet Systems select cell A1 and expand the selection till the last cell with content. Then copy move to sheet " Appendix Data" and paste it there.

Here is my code and it works fine

[VBA]Sub AddSystems()


ActiveWorkbook.Sheets("Systems").Activate

Range("A1", Range("A65536").End(xlUp)).Select
Selection.Copy

ActiveWorkbook.Sheets("Appendix Data").Activate

Range("A4").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Range("A1").Select


End Sub
[/VBA]

The issue comes up when I try to modularize it. I want to assign my cell A1 to a variable so I can call the main sub every time i want and change the value.

when I run it i get the error: Expected Funcion or variable.

Any ideas???

here is my code


[VBA]Sub AddSystems(varColumns As String)


ActiveWorkbook.Sheets("Systems").Activate

Range(varColumns, Range("A65536").End(xlUp)).Select
Selection.Copy

ActiveWorkbook.Sheets("Appendix Data").Activate

Range("A4").Select

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False

Range("A1").Select


End Sub


Sub lets()

Run AddSystems("A1")

End Sub[/VBA]

Thanks a lot

[Bob Phillips]

[VBA]Sub lets()

Call AddSystems("A1")

End Sub
[/VBA]

[Bob Phillips]

And the other bit

[VBA]Sub AddSystems(varColumns As String)

ActiveWorkbook.Sheets("Systems").Activate

Range(varColumns & 1, Range(varColumns & Rows.Count).End(xlUp)).Copy

ActiveWorkbook.Sheets("Appendix Data").Activate

Range("A4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False
End Sub
[/VBA]

[fredlo2010]

XBAX Mentor,

I dont understand what you are trying to tell me. Can you explain a little to me please.

Whats different here from my code?

VBA:
Sub lets()

Call AddSystems("A1")

End Sub
VBA tags courtesy of www.thecodenet.com


____________________________________________
Nihil simul inventum est et perfectum

Abusus non tollit usum

Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
James Thurber

thanks

[CatDaddy]

Run <> Call

[fredlo2010]

Hello guys

Thank you so much for the help. The issues were more complex than I thought. it all clear now.

I fixed another little thing. My starting count for the row was 2 not 1

Final Code


[VBA]Sub AddSystems(varColumns As String)



Application.ScreenUpdating = False



ActiveWorkbook.Sheets("Systems").Activate

Range(varColumns & 2, Range(varColumns & Rows.Count).End(xlUp)).Copy

ActiveWorkbook.Sheets("Appendix Data").Activate

Range("A4").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

Application.CutCopyMode = False
End Sub
[/VBA]