Paste Special problem 1

[rob9740]

Hi all,
I have some code that transfers a range of data but it copies and pastes all the formatting from the original range which I don't want. I've tinkered around a bit with it but can't seem to fit the PasteSpecial function xlPasteValues into the code.Any suggestions????

Here is what I'm using (courtesy of Abi1):

Sub Datatransfer()
Worksheets("DataValues&quot.Activate
Range("Transfer&quot.Select
Selection.Copy
Workbooks.Open FileName:=<filename>
Worksheets(&quot;Sheet1&quot.Activate
Range(&quot;Test&quot.Cells(1,1).Select
If ActiveCell.Value= &quot;&quot; Then
ElseIf ActiveCell.Offset(1, 0) = &quot;&quot; Then ActiveCell.Offset(1, 0).Select
Else
ActiveCell.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
End If
ActiveSheet.Paste
End Sub

Oh yeah, there's another if anybody can give me a hand with is, this code tests for an empty cell in the range of the sheet to be transferred to but it only checks the first column. I need it two check two or more and I'm not to sure how this code works.

Cheers,

Rob

 

[Abi1]

The PasteSpecial function needs a range variable to operate on, so you either have to say Range(&quot;A1:B1&quot.PasteSpecial or you can but ActiveCell.PasteSpecial.

Replace your ActiveSheet.Paste line with this:

ActiveCell.PasteSpecial (xlPasteValues)


As for the testing whether the worksheet's empty, the code works by starting at once cell, testing its emptiness, then going down to the bottom of the column with text in, and moving down one more (to get to an empty cell).

If you want to just check for, say, two columns, you could just repeat the lot with a different start cell. For more, it might be better to use a piece of code to do it. Let me know.

Abi

 

[DaleWatson123321]

Rob,

There's no reason why something SIMPLE like the following should not work.

Sub CopyData()
Range(&quot;TransferData&quot.Copy
Selection.PasteSpecial Paste:=xlValues
End Sub

This only requires: 1) that you first name the range &quot;TransferData&quot;, and 2) that you add the code to place your cursor in the position where you want the data transferred.

Please advise as to how you make out.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[rob9740]

Cheers you guys.

Abi: I'm gonna play about with it for a while and see how I get on with checking the blank cell in multiple columns. I'll let you know how I get on before I throw the PC out the window!?!?!?!

Dale: Cheers for that, I was having problems with the syntax before cos the help file example wasn't very clear. i.e. the Paste:=xlValues I kept getting 1004 run-time error, but seems to be working so far so good.

 

[tsuji]

Hello, rob9740.

This is what I would develop based on your posted script to include multiple column search for nonempty starting cell. The number of columns to search is that declared as ColCheck.

The search results in the first row where all cells are empty. (If you have other criteria in your mind, you've to modify according to your need.)

regards - tsuji

Sub Datatransfer()

Const ColCheck = 3 'modify it according to your need
Dim Flag As Boolean
Dim J As Integer

Worksheets(&quot;DataValues&quot.Activate
Range(&quot;Transfer&quot.Select
Selection.Copy
ActiveCell.Select 'Just to clear the selection.copy highlight

Workbooks.Open FileName:=<filename>
Worksheets(&quot;Sheet1&quot.Activate
Range(&quot;Test&quot.Cells(1, 1).Select

Flag = False
Do While Not Flag
For J = 1 To ColCheck
If ActiveCell.Value = &quot;&quot; Then
'do nothing
Else
If ActiveCell.Offset(1, 0) = &quot;&quot; Then
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.End(xlDown).Select
ActiveCell.Offset(1, 0).Select
End If
End If
ActiveCell.Offset(0, 1).Select
Next
ActiveCell.Offset(0,-ColCheck).Select

Flag = True
For J = 1 To ColCheck
If ActiveCell.Offset(0, J - 1).Value <> &quot;&quot; Then
Flag = False
ActiveCell.Offset(0, 1 - J).Select
Exit For
End If
Next
Loop

Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, Skipblanks:= _
False, Transpose:=False

ActiveCell.Select 'Just to clear pastespecial highlight
Application.CutCopyMode = False 'Just to clear selection.copy range

End Sub

 

[rob9740]

Thanks a mil tsuji, it works like a charm. Thanks again to Dale and Abi for your help too.