PDA

View Full Version : Conditional Paste



Herbiec09
07-04-2013, 12:10 PM
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
07-04-2013, 12:25 PM
A small sample workbook with the before and after will help

Also, is it always 13 cells to be pasted?





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


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

Paul

Herbiec09
07-08-2013, 01:33 AM
A small sample workbook with the before and after will help

Also, is it always 13 cells to be pasted?





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

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