Copy VBA Code From One Workbook To Another ?

[TkdKidSnake]

Hi all,

does anyone know how to copy VBA code from one workbook to another using VBA code and then for that code to run in the destination workbook?

The reason I need to do this is if the code is ran from the source workbook what its doing to the dates is quite bizarre however when its ran in the destination workbook all seems ok and when I compare the detail between the dates its realistic.

Source workbook = OTIF Dashboard.xlsm
Destination workbook = RawData.csv

The vba code is in module2 and is called "Sub RefreshData"

Below is the code

Sub RefreshDates()'
' SelectRawData Macro
'
    '
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False
    
    Workbooks.Open Filename:= _
        "P:\Public User Area\Quality\3 SUPPLIERS\Supplier OTIF\002 - RawData\RawData.csv"
    '
    '
    Columns("P:S").Select
    Application.CutCopyMode = False
    Selection.NumberFormat = "m/d/yyyy"
    '
    Columns("P:S").Select
    Selection.NumberFormat = "yyyy-mm-dd"


    Application.DisplayAlerts = False
    Application.ScreenUpdating = False


End Sub

If anyone can help with this it would be greatly appreciated

Thanks in advance

 

[Joe4]

One way is to use dynamic Workbook object variables, which would allow you to easily bounce back and forth between workbooks, i.e.

Dim srcWB as Workbook
Dim destWB as Workbook

'Capture current workbook with macro as source workbook
Set srcWB = ActiveWorkbook

'Open destination workbook
Workbooks.Open Filename:= _
    "P:\Public User Area\Quality\3 SUPPLIERS\Supplier OTIF\002 - RawData\RawData.csv"

'Save workbook just opened as destination workbook
Set destWb = ActiveWorkbook

Then you can easily bounce back and forth between workbooks in your code like this:

srcWB.Activate

And can also save/close them by referencing these workbook objects.

Note that it looks like your destination file is a "CSV" file and not an Excel file. If you open a "CSV" file directly in Excel, Excel will often do some conversions on some of the number/date data (or what looks like numbers or dates), and may not also do what you want/expect.