Copy a row with formulas and formats to a specific row

[menajaro]

Hello everyone
Suppose I have "Main File" workbook where I would like to put a code that enables me to Copy a specific row with the formulas and formats
For example... I want to copy this row from row 4 to row 10
How can I achieve this....thank you for your support

[Doc.AElstein]

... to Copy a specific row with the formulas and formats
For example... I want to copy this row from row 4 to row 10
How can I achieve this....

Hello menajaro.
This requires only very basic VBA Knowledge.
If you want to copy in Excel, with VBA, and want to maintain formats, then usually, people use two things.

_ 1 ) Use the Rng.Copy Method ‘ https://docs.microsoft.com/de-de/office ... range.copy" onclick="window.open(this.href);return false;
This will copy to many different clipboards in different ways simultaneously . Lots of different infomation is then held in the different clipboards.

_2 ) Now you use the Rng.PasteSpecial Method ‘ https://docs.microsoft.com/de-de/office ... stespecial" onclick="window.open(this.href);return false;
This allows you to paste the copied range, Rng, in various ways.
You can choose how you want to paste out: - ( https://docs.microsoft.com/en-us/office ... lpastetype" onclick="window.open(this.href);return false; )

For example, copy, and paste formulas, formats and number formats

Code: Select all

Sub CopyRws()
' Range Copy method
 Rows("4").Copy          '  Row 4 is now copied to many clipboards in many ways

' Range Paste Special Method
 Rows("10").PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
 Rows("10").PasteSpecial Paste:=xlPasteFormats
End Sub

_._________________________________________________________________________________

Note:
_a) This macro will work on the active worksheet if placed in a normal code module. If the macro is placed in a worksheets code module, then it will work on the worksheet to which the worksheets code module belongs

_ b) Rows(“4”) , as used here, is just a range. It is
Range(“A4:XFD4”) ‘ For Excel 2007 and higher versions
Range(“A4:IV4”) ‘ For Excel 2003 and lower versions
So for Excel 2007 and higher versions, this is same macro again

Code: Select all

Sub CopyRws()
' Range Copy method
 Range("A4:XFD4").Copy
 
' Range Paste Special Method
 Rows("10").PasteSpecial Paste:=xlPasteFormulasAndNumberFormats
 Range("A10:XFD10").PasteSpecial Paste:=xlPasteFormats
End Sub

_.____________________________________________

Ref:
http://www.eileenslounge.com/viewtopic. ... 34#p262007" onclick="window.open(this.href);return false;
http://www.eileenslounge.com/viewtopic. ... 02#p193871" onclick="window.open(this.href);return false;



Alan

[menajaro]

Thanks a lot Mr. Alan for giving the code
I thought that my idea difficult .... Thank you very much for this useful information. I appreciate that a lot
Kind Regards