Help - how to conditionally copy columns to multiple worksheets

Registration date
Tuesday November 25, 2014
Last seen
November 25, 2014
Newbie member searching for a solution to what is probably a straight forward problem. I am new to Vba so have little understanding but am familiar with excel macros. My issue, in simple terms is I have 2 sheets, we will call them Sheets1 and Sheets2. I enter data weekly into Sheet1 Col B rows 4 to 50 (numbers entered can be from -20 to 150) and want to copy this data to Sheet2 Col B rows 4 to 50 if week = 1, Sheet2 Col C rows 4 to 50 if week= 2, Col D if week = 3 and so on up to week 45.

I can easily achieve this using if else for each week but am looking for a more elegant solution as in the production workbook I will be entering data into 6 separate columns in Sheet1 and copying each column to one of 6 sheets.

Thanking you all in advance.

Just as an aside, I would probably want to have an Input box and enter week number and use this as the variable.