Call and Empty Cells

[wssparky]

Using a macro
How would I go about calling the info from a range of cells (F11 – S11)
from worksheets
Sheet1 to Sheet12 in workbook 1, and place them in a new workbook, cells
F11 – S11 and rows 11 to 23. Then empty the cells (F11 – S11) on All of
the worksheet in workbook 1 ?


Thanks for your help.

 

[Tom Ogilvy]

Dim rng as Range
Dim i as long, sh1 as Worksheet
set rg = workbooks("Book2.xls").Range("F11")
for i = 1 to 12
set sh1 = workbooks("Book1.xls").worksheets("Sheet" & i)
rg.offset(i-1,0).Resize(1,14).Value = sh1.Range("F11:S11").Value
sh1.Range("F11:S11").Clearcontents
Next i

Test on a copy of your workbook since you are destroying data.

 

[Terry Cotton]

I have built up an invoice sheet. Many of the cells are not required so I
have lock/protected them
How do I get jump from only the unlocked cells. Not be able to 'land on' a
protected cell
or
using the tab or enter key, to jump from a cell to the next appropriate
cell?
Can I control this flow?
Thanks
Terry

 

[Tom Ogilvy]

activesheet.EnableSelection = xlUnlockedCells


if you want some type of specialized movement between cells you can select
the last cell, then hold down the ctrl key and click the cells in the order
you want them select. Go to the name box and type in Sequence.

Now, you when you hit the tab key, you progress in that sequence. If they
become unselected, you can use the name box to select sequence.