VB in Excel

Lost_in_VB
Lost_in_VB used Ask the Experts™
on
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 !!
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2006

Commented:
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

Author

Commented:
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.
Top Expert 2006

Commented:
>>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?
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Same Sheet ! just 52 columns, that 1column would update all
Top Expert 2006

Commented:
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 ?

Author

Commented:
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

Author

Commented:
Hi oneeyedman

both would be great !

much appriciate the help from all !

Commented:
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
Try this:
create a button and put this code behind it (if you don't know how to do that let me know)

Dim WeekNum As Integer

WeekNum = Int((Now - DateSerial(Year(Now), 1, 1)) / 7)
Range("c2:c7").Offset(0, WeekNum).Value = Range("a2:a7").Value

Assuming the data is in a2 to a7 and week 1 is column C

If you want to pick the week number:

Dim WeekNum As Integer

WeekNum = InputBox("Enter Week Number")
Range("c2:c7").Offset(0, WeekNum - 1).Value = Range("a2:a7").Value

Hope this helps.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial