Getting a range to another sheet and copying and pasting data

[kenman]

Hi all,

I am performing calculations on data in 1000's of workbooks (Files). On top of that I am doing multiple computations (different averages on the same data, say averages using 2, 4, and 8) on data that's in each one. I would like to take that info and place it on another sheet in the same workbook. Can I name the worksheets using a variable name with whatever the average # is that I'm using? I.E. I do a 2 day average for the data, I want to put that onto a worksheet that's named "2 day avg". Is there a way to write a vba line of code that will let me concatenate whatever the average "#" that is used with "day avg."?

Also, does anyone know a VBA line of code that will in effect select a volatile amount of data in a column, copy or cut it and paste it on another worksheet or in another workbook? It's easy to select a cell, hold a mouse button, and drag down to the bottom of the data, but even when I record a macro to see how VBA writes it, I can't figure out how to do it in VBA. In VBA, can this sort of line be written.?

Thanks in advance for everyones help.

Kenman

 

[TTom]

This might get you a start on sheet naming from cell variables:
<code>
Sheet1.Name = Sheet2.Cells(2, 2) & " " & Sheet3.Cells(3, 3)
</code>
This would rename sheet1 from value in Range("B2"), then a space, then the value from sheet3 value in Range("C3") row3, col3.

The & symbol works like comma in concatenate formula. Sheet().Name= sets the sheet name equal to formula value. OF course you can grab data from other workbooks.

In answer to the second question you may just need to tell vba the range (example copys range in sheet 1 to sheet 2 starting a designated cell.)
<code>
Dim rng1 As Range
Set rng1 = Sheet1.Range("A2:A200")
rng1.Copy
Sheet2.Range("B2").PasteSpecial (xlPasteAll)
</code>


TTom