Copy and Paste Non Contigous columns

[rob nobel]

Greetings all.
I have 2 workbooks exactly the same as each other except that one is the
workbook with the info whilst the other is in the form of a template.
In the first workbook I would like to copy values and paste them to the
Template file; to the same location (Sheet name, columns, etc.)
I can't seem to do this in one hit by either:
1. Copying sheet to Template file. As it copies all the fomulas and links
them back to the workbook.
2. Copying the columns in one hit. As when pasting them, they are pasted
contiguously, overwriting other columns.
As I'm copying about 5 columns I'd like to know if there's a better way to
do this then by the following code (for each column).
'TRANSFER VALUES TO NEW BOOK
'Copy Closing value from old to opening value in new.
Windows("Records (CURRENT YEAR).xls").Activate
Sheets("Depreciation").Range("J5:J31").Copy
Windows("Records1").Activate
Sheets("Depreciation").Range("J5").PasteSpecial Paste:=xlValues
So, is there a better way please?
PS. It's only important if the code would be a lot shorter than the above
code (x 5), as this works fine, but I just want to reduce the size of the
code.
Rob

 

[Don Guillett]

Windows("Records (CURRENT YEAR).xls").Activate

Sheets("Depreciation").Range("J5:J31").Copy
Windows("Records1").Activate
Sheets("Depreciation").Range("J5").PasteSpecial Paste:=xlValues

if you are doing it from the Records1.xls and the sheet you want it on then
try this, making sure the ranges are the same. ie: Dont make it j5:j31
values to i6 or i6:i8 make it to i6:i32.

Sub copyvalues()' can be one line without the space and underscore
Range("B1:B5").Value = _
Workbooks("yourworkbook.xls").Sheets("Sheet3").Range("a1:a5").Value
End Sub

or specify worksheet
sheets("mysheet").Range("B1:B5").Value = _

 

[Ken Wright]

Another option is to have the macro hide all the columns that don't get pasted
across, do a goto special / visible cells only, copy that region and then
pastespecial as values into the other sheet, thereby doing it all at once. Then
just have the routine unhide the hidden columns.

 

[Don Guillett]

Good One. Your thinking cap is on today.

 

[rob nobel]

This code for each column works great and has saved about 20 line of code!
Workbooks("Records1").Sheets("Depreciation").Range("F5:F31").Value = _
Workbooks("Records (CURRENT
YEAR).xls").Sheets("Depreciation").Range("J5:J31").Value
(Records1 is the template the workbook opens.)
Thanks Don
Rob

 

[rob nobel]

Thanks for that suggestion Ken.
But I'll stick with Don's method as it's easier for me right now and works
great. Though it may be a way to go in another application I have.
Rob

 

[Don Guillett]

glad to help but you should look at Ken's for efficiency.

 

[rob nobel]

OK Don. Good suggestion. Trouble is, when you're developing a workbook
and something works well , having to change that is sometimes tedious
because you then need to make that work as well. (I've found they don't
often work first time without some modifications.)
Rob