VB in Excel

Hello Lost_in_VB

you could try something like

worksheets("MysheetWeekly").Range("B2:B7") = worksheets("Mysheet").Range("B3:B8")

if you need more help please give some more info on the data you got and if you got some code already

HAGD:O)Bruintje

On a weekly basis I want to copy a selected range of data into the the relevent column for that week. Any ideas on what I use VB wise > 

I am tring to incorporate this into an excel Macro !!

So I would have 1 column of data that would always be changing, but being applied to a 52 different columns which would represent weeks.

52 different reporting weeks, being updated by one column each week. This is a small part of a large series of macros.

>>have 1 column of data that would always be changing

>>but being applied to a 52 different columns which would represent weeks

guess the weeks are on a different sheet?

Same Sheet ! just 52 columns, that 1column would update all

difficult to imagine

but say you got one column that changes with the week

say column A

next we have 52 columns for the weeks

say B - BA

and now you need a bit of code to copy values from A to the correct week in the range B-BA ?

how many rows are there to copy ?

ok,

Imagine column 1 is a column of data containing 10 different shops weekly total sales.

Then the other 52 columns are for each week of the year.

column 1 would be genreated from various macros, but would then need to be cut & pasted into the relevent column week.... i.e (one of the 52 columns, depending on what week we were reporting.

Hi Lost_In_VB,
How do you want it to work?
Click a button and the current data goes into this weeks column?
Click a button and a dialog box asks you which week you want the data to go into?
Both ways are fairly easy, let me know which you'd prefer and I'll give you an example.
OneEyedMan

Hi oneeyedman

both would be great !

much appriciate the help from all !

Here's what I'm thinking: If you can figure out which week you need to paste the data into, you can move from your column 1's range of cells range.offset(# of columns to move over to).select, then paste your range into the offsetted column.

so, if your range is always going to be range("a1:a10") [or whatever], you can select the range first, then copy, then move over to the correct column [based on which week you need to paste into], then paste your values. For me, the most difficult part of your question is figuring out which week of the year you're currently in. So, if you an figure that out, you might try this sort of thing:

'==================================
Sub doCutPasteThing()
     ' this is the range we're copying from
     range("$a$1:$a$10").select

     ' do the copy bit
     selection.copy

     ' figure out which week we're in
     weekNumber = 'you supply the formula for figuring this out

     ' go to that week's column
     activecell.offset(0, weekNumber).select

     ' now paste into that column
     activeSheet.paste

     ' deactivate the copy mode
     application.cutcopymode = false

End Sub
'==================================

Please let me know if you need further clarification..

geo