Macro Copy and Paste

Microsoft Excel 2007
December 8, 2009 at 12:07:22
Specs: Windows XP
I currently have a macro that copy and pastes a column from one sheet onto another, but is there a way to have the column copied into the next free column rather than the same column each time

See More: Macro Copy and Paste

Report •

December 8, 2009 at 12:12:10
It would help if you posted the code so that we can see what it is doing, but Yes, in general, locating the next empty column via VBA can be done.

For example, this can be used to find the next empty column in Row 1:

LastCol = Cells(1, Columns.Count).End(xlToLeft).Column + 1

Report •

December 9, 2009 at 05:05:04
Thank you, this is code that I have for the macro at the minute

Sub TransferOfData()
' TransferOfData Macro

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Sub

Where would I put the code you have given me and is there anything that needs deleting?

Report •

December 9, 2009 at 06:16:48
Try this code.

The first time it pastes into a blank sheet, it will put the data in Column 2. After that it will paste into 3, 4, 5, etc.

If you need the data pasted into Column 1 the first time, let me know and I can modify the code to make that happen.

Sub TransferOfData()
' TransferOfData Macro
Dim NextCol As Integer
  NextCol = Sheets(2).Cells(1, Columns.Count).End(xlToLeft).Column + 1
    With Sheets(2).Cells(1, NextCol)
     .PasteSpecial Paste:=xlPasteValues
    End With
End Sub

Report •

Related Solutions

December 9, 2009 at 10:25:22
Thank you very much
was very helpful

Report •

Ask Question