Excel vba copy paste to next empty column

Question

I have calculations and texts in cells D8:E30. I want to create button for copying Range of D8:E30 with all fill colors, borders, formulas and texts as they are and paste to next empty column. At the starting point F8 is the first one where D8:E30 range can be copyed. By pressing the button D8:E30 is copyed to F8, then by pressing the button again D8:E30 is copyed to the next empty column and it will be H8... and so on. I managed to come up with the code below but it is inserting to the next empty ROW (veryically) I need it to be next empty Column (horizontally). Can anyone help with this one? Thank you!

Sub CopyPaste()
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet

Set copySheet = Worksheets("Price calculation")
Set pasteSheet = Worksheets("Price calculation")

copySheet.Range("D8:E30").Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteFormulas

Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Mr ML · Accepted Answer · 2018-10-08 13:06:53Z

1

This part of your code decides where to paste:

pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)

Broken down: Right now it is set to go from cell (1048576, 1)

pasteSheet.Cells(Rows.Count, 1)

and up until it finds a cell which is not blank.

.End(xlUp)

Offset just offsets the cell value by 1 row. (E.g. if last blank row is 12 it will paste to cell 13)

.Offset(1, 0)

What you need is:

pasteSheet.Cells(1, columns.count).End(xlToLeft).Offset(0, 1)

edited Oct 8, 2018 at 13:06

answered Oct 8, 2018 at 13:01

Mr ML

4282 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

10101 Over a year ago

Thank you for you answer! Very nice description but your suggestion is not working properly as with some modifications I can copy range D8:E30 to F8:G30 but it doesn't go further by pressing the button and keeps pasting to D8:E30. It should then paste to H8:I30. I modified your to this pasteSheet.Cells(1, Columns.Count).End(xlToLeft).Offset(7, 5).PasteSpecial xlPasteFormulas

Mr ML Over a year ago

It makes it a bit hard when i don't know what your data looks like. Anyways if your data is in row 8 it should be: pasteSheet.Cells(8, columns.count).End(xlToLeft).Offset(0, 1)

10101 Over a year ago

Thank you! I ahd a merged cell in D8:E8. After unmerging your last code does the job. Can you please point me one more time.. What I should add to keep my colors and borders while running this code? Now data is "copy pasting" but colors and borders are not "copy pasting" from D8:E30

Mr ML Over a year ago

pasteSheet.Cells(8, columns.count).End(xlToLeft).Offset(0, 1).PasteSpecial xlPasteAll xlPasteFormulas only pastes whatever is in your formula. Like when you copy, right click and choose paste as formulas.

Collectives™ on Stack Overflow

Excel vba copy paste to next empty column

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related