PDA

View Full Version : Paste into next available column



antonc
11-13-2008, 02:32 AM
Hi guys,

I wracked myself silly with this problem and it's probably very easy to solve.

I have attached a file for ease of explanation

I want to complete a expense forecaster for the next 24 months.
The idea is to fill in the expense in the yellow highlighted blocks, then hit the "Transfer" button and the expenses are transfered into the next available month (Col E to start with). The user would then fill in their forecast in the yellow blocks again and once the transfer button is used, the selection is transferred to the next month (Col F).

Thanks Guys - really appreciate the time taken to help me.

Bob Phillips
11-13-2008, 02:47 AM
Sub TransferData()
Dim NextCol As Long

With ActiveSheet

NextCol = .Cells(2, .Columns.Count).End(xlToLeft).Column + 1
If NextCol = 3 Then NextCol = 5

.Range("B2:B11").Copy
.Cells(2, NextCol).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With

End Sub

Bob Phillips
11-13-2008, 02:47 AM
Sub TransferData()
Dim NextCol As Long

With ActiveSheet

NextCol = .Cells(2, .Columns.Count).End(xlToLeft).Column + 1
If NextCol = 3 Then NextCol = 5

.Range("B2:B11").Copy
.Cells(2, NextCol).PasteSpecial Paste:=xlValues
Application.CutCopyMode = False
End With

End Sub

antonc
11-13-2008, 03:00 AM
Thanks XLD - worked great... lovely stuff