simon clark
New Member
- Joined
- Oct 14, 2002
- Messages
- 29
Recently, Jay provided the following code which worked very well. I wanted the user to manual copy an area in a source sheet then run a macro to paste it into the first empty column in a separate destination sheet.
The code was:-
Sub test()
Dim PasteBook As Workbook, PasteSheet As Worksheet
Dim LastCol As Integer
Dim CalcSetting As Integer
With Application
CalcSetting = .Calculation
.Calculation = xlCalculationManual
End With
Set PasteBook = Workbooks("TestBook.xls")
Set PasteSheet = PasteBook.Sheets("data entry")
With PasteSheet
If .Cells(1, 1) = "" Then
LastCol = 1
Else
LastCol = .Cells(1, 256).End(xlToLeft).Column + 1
End If
Selection.Copy .Cells(1, LastCol)
End With
Application.Calculation = CalcSetting
PasteBook.Sheets("Statistics").Activate
End Sub
I now have two further questions:-
1. The data being copied consists of a column of cells which are all two merged cells each. Therefore when it is pasted the data merges into two columns. Is there any code which will unmerge the data on pasting so it only takes up one column in the destination sheet (using two columns each time restricts my number of columns available to 127 instead of 255)
2. All the additional formatting is also pasted over e.g. font size, color etc. Is it possible to paste over only the values, like with paste special. I don't know if this is possible with the "selection.copy" method
Hope you can help
The code was:-
Sub test()
Dim PasteBook As Workbook, PasteSheet As Worksheet
Dim LastCol As Integer
Dim CalcSetting As Integer
With Application
CalcSetting = .Calculation
.Calculation = xlCalculationManual
End With
Set PasteBook = Workbooks("TestBook.xls")
Set PasteSheet = PasteBook.Sheets("data entry")
With PasteSheet
If .Cells(1, 1) = "" Then
LastCol = 1
Else
LastCol = .Cells(1, 256).End(xlToLeft).Column + 1
End If
Selection.Copy .Cells(1, LastCol)
End With
Application.Calculation = CalcSetting
PasteBook.Sheets("Statistics").Activate
End Sub
I now have two further questions:-
1. The data being copied consists of a column of cells which are all two merged cells each. Therefore when it is pasted the data merges into two columns. Is there any code which will unmerge the data on pasting so it only takes up one column in the destination sheet (using two columns each time restricts my number of columns available to 127 instead of 255)
2. All the additional formatting is also pasted over e.g. font size, color etc. Is it possible to paste over only the values, like with paste special. I don't know if this is possible with the "selection.copy" method
Hope you can help