copy a columns

[kevcar40]

hi
i have a table of data that is overwritten every week
the table starts in b2 and ends in column and ends in bb65
this data is imported from access via a macro

what i want to do is
on week one copy the data in range c2 : c65 paste it in b 100
the next time i run the macro i want to copy d2:d65 and paste it in b
100
and so on all year
how do i

change the range via formula or vb ?

the copy/ paste actions will take place off a button

thanks

kevin

 

[Per Jessen]

Hi Kevin

You have to change the set the range in the macro assigned to the copy/paste
button.
The code below where to paste the data.

Dim CopyToRange As Range
Set CopyToRange = Range("B2")
If CopyToRange.Value <> "" Then
Set CopyToRange = Range("B" & Rows.Count).End(xlUp).Offset(35, 0)
End If

Regards,
Per

 

[Rick Rothstein]

Assuming you mean you want to evaluate the **current** week number and use
that to copy the appropriate column to B100, give this macro a try...

Sub MoveCurrentWeekNumbersData()
Range("B2:B65").Offset(0, DatePart("ww", Date)).Copy Range("B100")
End Sub

Note that "week one" can mean different things to different people, so you
should check out the VBA help files for the DatePart function to see if you
accept their default definition of when week one starts or whether you want
to change it by specifying the appropriate optional 3rd argument to the
function.