Formula help

[kole]

I have a workbook that updates daily. When it updates the updated value goes in the top row of the column lets say A3 and moves the rest of the numbers in the column down one row. I want to link these values to another workbook. What formula can I use to copy these values. example The first day it copies A3:A212 the next day A3:A213. Can anyone help?

 

[Tazguy37]

You can copy A3 (in the active workbook) to the last populated cell in Column A with something like:

Range("A3:A" & Rows.Count).End(xlUp).Copy Workbooks("myWorkbookName.xls").Worksheets("mySheet").Range("A" & Rows.Count).End(xlUp).Offset(1)

Or everything in Column A that's a number with:

Range("A:A").SpecialCells(xlCellTypeConstants, xlNumbers).Copy Workbooks("myWorkbookName.xls").Worksheets("mySheet").Range("A" & Rows.Count).End(xlUp).Offset(1)

How's that? Post back if you have any problems.

I forgot to mention that either of these would go in a standard code module.

• How to use the above code:
  
  Press Alt-F11 to open the VBE.
  Press Control-R to open the Project Explorer.
  Click "Microsoft Excel Objects" for the file you're working on.
  Select Insert, Module from the drop down menus.
  
  Open the Code pane with F7.
  Paste the above code in.
  Press Alt-Q to close the VBE and return to Excel

 

[randyrest]

When I use either of the codes I get and error message,"Invalid outside procedure" and it highlights (1) at the end of the code.

 

[Tazguy37]

The code I posted should go between a "Sub" declaration and "End Sub".

Insert a module using Insert, Module (from the VBE menus), and write this code in the module that appears. You can run this from withing the VBE (F5 or Run, Run Sub/Userform, or from Excel using Tools, Macro, Macros (Alt-F8).

Sub myCopyTest()
    Range("A3:A" & Rows.Count).End(xlUp).Copy Workbooks("myWorkbookName.xls").Worksheets("mySheet").Range("A" & Rows.Count).End(xlUp).Offset(1)
End Sub

That's all it'll do is just copy cells (from the active sheet. HTH