PDA

View Full Version : Solved: Cell Name as a variable to feed



fredlo2010
05-22-2012, 11:17 AM
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

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


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


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

Thanks a lot

Bob Phillips
05-22-2012, 12:19 PM
Sub lets()

Call AddSystems("A1")

End Sub

Bob Phillips
05-22-2012, 12:20 PM
And the other bit

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

fredlo2010
05-22-2012, 12:27 PM
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
05-22-2012, 12:29 PM
Run <> Call

fredlo2010
05-22-2012, 01:20 PM
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


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