Conditional Paste

[Herbiec09]

Hi All,

Could someone possibly assist with a quirky problem I am trying to solve.

I have data on Sheet 1 in a column, say A3:A15. I would like to copy this data to sheet 2. Now depending on the month that this data relates to, it should be pasted to a specific column. For example if it is month 1 data, it should be pasted into B3, month 2 data would go into C3, month 3 data would go into D3, month 4 data would go into E3 etc etc. How excel will know which month it is dealing with, will either be a cell on sheet one with the month number or an input box which allows the user to specify which month we are dealing with.

Can someone assist me with a code that could achieve this.

Thank you

Herbert

[Paul_Hossler]

A small sample workbook with the before and after will help

Also, is it always 13 cells to be pasted?




[VBA]
Option Explicit
Sub CopySheet1ToSheet2()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim iDestinationColumn As Long
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

iDestinationColumn = ws1.Range("B1").Value + 1 ' change as needed

Call ws1.Range("A3:A15").Copy(ws2.Cells(3, iDestinationColumn))
End Sub
[/VBA]

You'll probably has to tweak this a little, but I think the basics are there

Paul

[Herbiec09]

A small sample workbook with the before and after will help

Also, is it always 13 cells to be pasted?




[vba]
Option Explicit
Sub CopySheet1ToSheet2()
Dim ws1 As Worksheet, ws2 As Worksheet
Dim iDestinationColumn As Long
Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

iDestinationColumn = ws1.Range("B1").Value + 1 ' change as needed

Call ws1.Range("A3:A15").Copy(ws2.Cells(3, iDestinationColumn))
End Sub
[/vba]
You'll probably has to tweak this a little, but I think the basics are there

Paul

Thanks Paul,

The range will always be a fixed range, so I am guessing I can just tweak the range references.

How would the code look if I wanted to copy the data not to another worksheet, but to another workbook?

Thanks

Herbert