Writing Macro in Excel 2002

[dawn.pitzer]

I am writing a macro that will select Rows 4-10 and copy them and then past
them in row 11. I can do that but what I really would like the macro to do
the next time is to select row 11-17 and copy in row 18. Is there any way
that the macrro will select the 7 row with text and copy that formula to the
next seven rows. This is what I did if that helps.


Sub ResetWeek()
'
' ResetWeek Macro
' Macro recorded 6/12/2008 by dpitzer
'

'
Sheets("Total").Select
ActiveWindow.SmallScroll Down:=-12
Rows("4:10").Select
Selection.Copy
Range("A11").Select
ActiveSheet.Paste
Rows("4:10").Select
Application.CutCopyMode = False
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

 

[Bob Phillips]

Sub ResetWeek()
'
' ResetWeek Macro
' Macro recorded 6/12/2008 by dpitzer
'

'
With Sheets("Total")

.Rows("4:10").Copy .Range("A9").End(xlDown).Offset(1, 0)
With Range(.Range("A11"), .Range("A11").End(xlDown)).EntireRow

.Value = .Value
End With
End With
End Sub

--
__________________________________
HTH

Bob

"(e-mail address removed)"

 

[Don Guillett]

I'm having a hard time figuring out what you want based on your description
and your macro. This macro will take the cells 4-10 and and place the values
starting at cell 11.
Sub copyvalues()
ActiveCell.Offset(6).Resize(7).Value = ActiveCell.Resize(7).Value
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
"(e-mail address removed)"

 

[Joel]

Sub ResetWeek()
'
' ResetWeek Macro
' Macro recorded 6/12/2008 by dpitzer
'

'
With Sheets("Total")
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
.Rows((LastRow - 6) & ":" & LastRow).Copy _
Destination:=.Rows(LastRow + 1)
End With
End Sub

 

[dawn.pitzer]

I tried everyone's suggestions (Thank you) but they don't work the way I
would like them to. I don't think I'm explaining what I would like to do
correctly. So here is try number two.

I have rows 4-10 set up with formulas that will link to other worksheets in
my excel document. I would like to be able to clear out the worksheets at
the end of everyweek and re-enter the new information into it. I am trying
to set up a formula that will copy my formulas that are currently in rows
4-10 and past them in the row 11 ( or the next blank row) but I need the
system to recognize that the last 7 rows should be copied and then pasted in
the first blank row.

I know I can do this I did it before but I can't remeber how.

Thanks for you help.

 

[Dave]

Hi,
I am not the most elegant macro writer, but try this code.

Sub ResetWeek()
A = 1
Do Until Cells(A, 1) = ""
A = A + 1
Loop
Range(Cells(A - 7, 1), Cells(A - 1, 255)).Copy _
Range(Cells(A, 1), Cells(A + 6, 255))
End Sub

Regards - Dave.

 

[Don Guillett]

Finally we find out what you need

Sub copyvaluestonextblankrow()
mc = "A"
lr = Cells(Rows.Count, mc).End(xlUp).Row + 1
Cells(lr, mc).Resize(7).Value = Cells(4, mc).Resize(7).Value
End Sub


--
Don Guillett
Microsoft MVP Excel
SalesAid Software
(e-mail address removed)
"(e-mail address removed)"

 

[Don Guillett]

You didn't say but change the ,3 to reflect how many columns you need from
the base column

Sub copyvaluestonextblankrow()
mc = "A"
lr = Cells(Rows.Count, mc).End(xlUp).Row + 1
Cells(lr, mc).Resize(7, 3).Value = Cells(4, mc).Resize(7, 3).Value
End Sub