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
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)
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments