paste to next empty column

[mahpour4]

i have a set of data that i want to copy and paste to the next empty column i have searched all over but cannot find anything that answer my query
this is what i have so far and it doesnt even do columns it does rows

ActiveSheet.Range("B6:C33").Copy    Sheets(1).Cells(Rows.Count, "A").End(xlUp).Offset (1)
    ActiveSheet.paste Link:=True
    Application.CutCopyMode = False

this is what i have so far and its not working

 

[Sektor]

Your code will always paste into B column. You need to go from right to left.

Sub PasteNext()
     ActiveSheet.Range("B6:C33").Copy Sheets(1).Cells([COLOR=#ff0000][B]1[/B][/COLOR], Columns.Count).End(xlToLeft).Offset(1)
     ActiveSheet.Paste Link:=True
     Application.CutCopyMode = False
End Sub

All you need is to adjust the row (highlighted with red color) which the code will use when going from right to left.

 

[mirabeau]

probably means

Sub PasteNext()
     ActiveSheet.Range("B6:C33").Copy Sheets(1).Cells([COLOR=#ff0000][B]1[/B][/COLOR], Columns.Count).End(xlToLeft).Offset([COLOR=red], [/COLOR]1)
     ActiveSheet.Paste Link:=True
     Application.CutCopyMode = False
End Sub

but still has a couple of potential problems.

 

[Sektor]

mirabeau, oh, thanks for correction!

 

[mirabeau]

mirabeau, oh, thanks for correction!

I was hoping you didn't mind, because I thought you'd probably pick it up anyway.

 

[Sektor]

mirabeau,

...but still has a couple of potential problems.

Which problems you mean?

 

[mirabeau]

mirabeau,


Which problems you mean?

1. If the relevant sheet, Sheet(1), is all empty, then with this formulation the paste will be into ColumnB rather than the empty ColumnA.

2. The first empty column may not be the one immediately to the right after the last filled cell in Row 1. Because it may be that Rows 2 or later may have filled cells even further to the right.

Not hard to avoid both of these, but might take a somewhat longer code.

 

[mahpour4]

1. If the relevant sheet, Sheet(1), is all empty, then with this formulation the paste will be into ColumnB rather than the empty ColumnA.

2. The first empty column may not be the one immediately to the right after the last filled cell in Row 1. Because it may be that Rows 2 or later may have filled cells even further to the right.

Not hard to avoid both of these, but might take a somewhat longer code.

I have a problem the code doesnt work it gives me a debug by paste statement

 

[mirabeau]

I have a problem the code doesnt work it gives me a debug by paste statement

That statement was in your original code.

You didn't say whether or not it worked in your original code, nor whether the modified code otherwise does what you want apart from this error.

If you want to avoid the error statement then try this version

Sub PasteNext()
On Error Resume Next
     ActiveSheet.Range("B6:C33").Copy Sheets(1).Cells(1, Columns.Count).End(xlToLeft).Offset(, 1)
     ActiveSheet.Paste Link:=True
     Application.CutCopyMode = False
End Sub