Please tweak this VBA code from Jay Petrulis

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
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
1) Selection.MergeCells = False
2) Selection.PasteSpecial Paste:=xlValues

should help you.
 
Upvote 0

Forum statistics

Threads
1,213,507
Messages
6,114,029
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top