Excel VBA复制粘贴到下一个空列 [英] Excel vba copy paste to next empty column

问题描述

我在D8:E30单元格中有计算和文本.我要创建用于复制D8:E30范围的按钮，并按原样填充所有填充颜色，边框，公式和文本，然后粘贴到下一个空列.在起点F8是可以复制D8:E30范围的第一个.通过按下按钮D8:E30复制到F8，然后再次按下按钮D8:E30复制到下一个空列，它将是H8 ...，依此类推.我设法弄出下面的代码，但是它正在插入到下一个空的ROW(非常)，我需要它成为下一个空的Column(水平).有人可以帮忙吗?谢谢！

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)

细分为:现在将其设置为从单元格(1048576，1)转到

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)

偏移量仅将单元格值偏移1行.(例如，如果最后一个空白行是12，将粘贴到单元格13)

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)

您需要的是:

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

这篇关于Excel VBA复制粘贴到下一个空列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！