Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy a columns
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy a columns
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 "kevcar40" skrev i meddelelsen ... 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
copy a columns
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. -- Rick (MVP - Excel) "kevcar40" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy Columns | Setting up and Configuration of Excel | |||
copy over just columns and headers | Setting up and Configuration of Excel | |||
How do I copy columns between worksheets if the columns don't matc | Excel Worksheet Functions | |||
Copy columns values into separate columns | Excel Discussion (Misc queries) | |||
Why can't a copy and paste columns? | Excel Worksheet Functions |