better way of copy from one sheet and paste to another?

[Oxidised]

Hi, this is a portion of code that is contained within a loop, where "data" and "mailmerge" have beend defined as worksheet names.
It feels very clumsy to go select sheet, select cell, copy then select sheet, select cell, paste...
is there a better way of doing this?

' copy paste customer
data.Activate
Cells(RowCustomer, CurrentCol).Select
Selection.Copy
mailmerge.Activate
Cells(CurrentRow, ColCustomer).Select
Selection.PasteSpecial Paste:=xlPasteValues

 

[Smallman]

Hi Oxidised

Good question. In my opinion the following method is the best way to copy and pastespecial values. Sheet names change all the time so using them in code is a potenial for an error to appear if you don't change the vba in line with the sheet name change. In order to avoid this potential pitfall use the Sheet Code name.

See below the sheet code name for your Data tab is Sheet1. Conversely the Sheet Code name for your MailMerge sheet is Sheet2.



So with this in mind the following vba will replicate your code above.

Sub CopyIt()
   Sheet1.Cells(RowCustomer, CurrentCol).Copy
   Sheet2.Cells(CurrentRow, ColCustomer).PasteSpecial xlPasteValues
End Sub

Where naturally you have both declared your variables and assigned them to a number.

Take care

Smallman

 

[Marc L]

Hi !

Another way :

Sheet2.Cells(CurrentRow, ColCustomer).Value = Sheet1.Cells(RowCustomer, CurrentCol).Value
​

 

[dan_l]

arrays!

dim sourcerange as range
dim outrange as range
dim sourcearray as variant

set sourcerange = sheets("whatever").range("a1:f100")
sourcearray = sourcerange

set outrange = sheets("outputsheet").range("a1")
outrange.resize(ubound(sourcearray,1),ubound(sourcearray,2)) = sourcearray

 

[Oxidised]

@dan_l
While the array option looks handy for a direct tranpose, I am mixing and matching data around to get it into the layout I need. I think this would make it a bit too complicated.

@Marc L
Thanks, I do like only having one line of code for this step! I take it by using the .value attribute, only the "value" is pasted, and not the formatting etc?

@Smallman
Marvellous, thanks . I get the logic, it is just getting my head around the syntax of VBA macros. Given I am doing quite a few copy/paste steps, that has cleaned up my code a lot... and using Marc's suggestion will do so a bit more.

A further question on the sheet code name. I understand that doing a direct reference to "sheet2" is better than using the sheet "name", cos then it doesn't matter if the sheet name is changed, however I was finding it very useful to have the sheet name as a variable, as it makes reading the code a bit easier... I don't have to remember that "sheet2" is my data sheet, and "sheet4" is the output sheet etc...

Can I change this portion of code to define the variable in terms of "sheet2" rather than the sheet name "data"?

' define worksheets
Dim data As Worksheet
Dim mailmerge As Worksheet
Set data = ThisWorkbook.Sheets("data")
Set mailmerge = ThisWorkbook.Sheets("mailmerge")

 

[Oxidised]

oh... maybe it as easy as???

' define worksheets
Dim data As Worksheet
Dim mailmerge As Worksheet
Set data = Sheet2
Set mailmerge = Sheet4

 

[Smallman]

Hi Oxidised

I don't have to remember that "sheet2" is my data sheet, and "sheet4" is the output sheet etc...

dim shDta as worksheet
set shDta= sheet2 'Sheet2 is the Data sheet

Looks like the best to get around your issue. I would not use a variable name for sheets when the above method is presented.

Take care

Smallman

 

[GCExcel]

@Oxidised
You can go in the properties (shortcut = F4) and change the sheet codename to whatever you want.
Example: Sheet1 >> shData
and then in your code :

Sub Sub CopyIt()
    shData.Cells(RowCustomer, CurrentCol).Copy
    shMailMerge.Cells(CurrentRow, ColCustomer).PasteSpecial xlPasteValues
End Sub

Note that in the properties, you must change the "(Name)", not the "Name".

 

[Marc L]

@Marc L
Thanks, I do like only having one line of code for this step! I take it by using the .value attribute, only the "value" is pasted, and not the formatting etc?

Yes, value only …
​