Simple Copy / Paste Formats & Values

Toballou

New Member
Joined
Apr 21, 2019
Messages
2
I have scoured the internet, and can't find anything that I can decipher that is even close to what I need, although all of it seems to work for others.

This is what I have:
Sub App_Mig_Status()
Workbooks("Recieving.xlsm").Worksheets("Recieving_Data").Range("A2:AS2001").Value = Workbooks("Sending.xlsx").Worksheets("Sending_Data").Range("C9:AU2008").Value
End Sub


First let me explain that I am pulling from a "Report" that is generated by someone else, and that takes about 2 hours to run, so I am not wanting to re-invent that wheel.
The above code copies the values, but there is formatting in each of the fields that I need to be able to filter on.
I can manipulate this to do a straight copy, however something in their code breaks the auto filter option's I have setup, and the one's I am trying to setup by Cell Color. (I have that code also)

However I can't figure out how to manipulate this to do a copy / paste of Values & formulas so I can try and do the auto filter that I need to do.

Help - Please!

T.
 

Some videos you may like

Excel Facts

Save Often
If you start asking yourself if now is a good time to save your Excel workbook, the answer is Yes

Logit

Well-known Member
Joined
Aug 31, 2016
Messages
3,794
.
First of all, you have a spelling error that may be affecting your code :

Code:
[COLOR=#333333]"Recieving.xlsm"[/COLOR]

Should be : RECEIVING




To receive assistance on the issues, you'll need to at least post your code. However, having the workbook to download
from a cloud site (DropBox.com, etc) is the best way to accomplish it.
 

MARK858

MrExcel MVP
Joined
Nov 12, 2010
Messages
13,597
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
  2. Mobile
@Toballou, what happens with the code below? Please note that I left the spelling mistake as is in case your sheet tab and Workbook name do also have the spelling mistake.

Code:
Sub App_Mig_Status()
    Application.ScreenUpdating = False

    Workbooks("Sending.xlsx").Worksheets("Sending_Data").Range("C9:AU2008").Copy

    With Workbooks("Recieving.xlsm").Worksheets("Recieving_Data").Range("A2")
        .PasteSpecial xlValues
        .PasteSpecial xlFormats
    End With

    With Application
        .CutCopyMode = False
        .ScreenUpdating = True
    End With
End Sub
 
Last edited:

Toballou

New Member
Joined
Apr 21, 2019
Messages
2
@MARK858 This worked PERFECTLY!! Thank you for the simple code. This did exactly what I needed, and now I can move on to doing all the other things I need to do to this sheet. Again Thank you!

As to the typo, it was an error made in haste, due to needing to rename things and the fact that I suck at spelling.
 

Watch MrExcel Video

Forum statistics

Threads
1,118,398
Messages
5,571,896
Members
412,423
Latest member
monbri9931
Top