copying (an anchored col) and pasting to incremental colum

Posted by keith kemble on February 03, 2002 10:03 AM

At present I run an archiving macro that selects an anchored column on one sheet and pastes the data to another sheet.

Each week I have to replace the cell reference in the paste section of the macro, manually, so that each week the data moves right one column.

I have linked cell contents up using lookups but cannot get round manually find and replace the macro contents.

I require the past fuction to reference the contents of a cell dynamically when you enter a week number.

Any takers?
keith

Posted by Jack in the Uk on February 03, 2002 10:10 AM

Can we see the code please? [NT]

Posted by keithkemble on February 03, 2002 10:29 AM

Re: Can we see the code please? [NT]

It is the cell reference b6 that I wish to to dynamically change to c6 then d6 and so on.

Thanks for taking the time

Sub archive()
'
' archive Macro
' Macro recorded 03/02/2002 by Keith Kemble
'
Range("B5:B7").Select
Selection.Copy
Sheets("Sheet2").Select
Range("B6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
Range("C13").Select
Sheets("Sheet1").Select
End Sub

Posted by Bill Shakespoke on February 03, 2002 2:41 PM

Re: Can we see the code please? [NT]

archive Macro Macro recorded 03/02/2002 by Keith Kemble

You didn't mention how the week number comes into it.
Does this do it :-

Sub archive()
Sheets("Sheet1").Range("B5:B7").Copy
Sheets("Sheet2").Select
Range("IV6").End(xlToLeft).Offset(0, 1).PasteSpecial Paste:=xlValues
Range("C13").Select
Sheets("Sheet1").Select
End Sub

Posted by keith kemble on February 04, 2002 12:51 AM

Re: Can we see the code please? [NT]

archive Macro Macro recorded 03/02/2002 by Keith Kemble

Bill, Thanks for taking time out to respond.
I have updated my macro so that it declares a variable X. However while it acknowledges the value of x I cannot substitute/use it in the range staements ( line 11) (ignore msgbox statement- put in to check value)

Each week the value of mobarchive!I1 will change. I wish to use this value to move the cursor to that cell in order to paste the contents of the clipboard.
Hope that makes it clearer.
Mean while I will give your routine a try.

Thanks

Sub ARCHIVE()
Dim X
X = Range("mobarchive!I1").Value
Sheets("mobile").Select
Range("BR9:BR13").Select
Selection.Copy
Sheets("mobarchive").Select
MsgBox X
Range("I2").Select
MsgBox X
Range ()
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub

Posted by keithkemble on February 04, 2002 1:05 AM

Re: Can we see the code please? [NT]

Bill, That routine of yours works a treat.
Unfortunately I do not want it to progess a column until the variable changes.
Keith

Posted by Bill Spearchucker on February 04, 2002 2:27 AM

Try this .....

Sub ARCHIVE()
Dim X As String
X = Range("mobarchive!I1").Value
Sheets("mobile").Range("BR9:BR13").Copy
Sheets("mobarchive").Range(X).PasteSpecial Paste:=xlValues
End Sub

Posted by keithkemble on February 05, 2002 12:22 AM

Re: Thanks + how about this?

Bill,

Thanks for that routine. So simple and so effective.

What do you reckon to this onethen?

At present I have to use a DSUM to search for like names and total values for that name.

eg
a b c d
8 title charge
9 ACN AC nie =DSUM( beats,$B8,$A8:$B9)

In Excell you have to use 2 rows where as in Lotus you only had to use 1.
This complicates data management considerably.

Is there a formula , function, event that would do the same thing?