Forum Discussion

antonio souza's avatar
antonio souza
Copper Contributor
Jul 22, 2020

MACRO TO COPY DATA FROM ONE FILE TO ANOTHER AS VALUE

Hi All,

 

I am a new user and needs some help in writing a macro. I do a task of copying data from one file to another involving large data.

 

First Macro.

I am copying the batch No which is in leafew file in column D highlighted In red colour in another file- request file and every time I paste this red colour appears under column D using copy and paste command as value which takes time . Could you write a macro to paste as value without showing red colour as the one I tried it gives me error.

 

Second Macro

I need to copy the qty value from leaflet file to the request file under column E which sometime contain sum formula. I want to copy the qty total as value in request file. A simple macro can help as it can directly paste them as value without showing red colour.Pasting s value takes times

 

Third Macro

I want a macro to check if total is ok in leafnew file. for qty example row 7 under column I and under column K instead of using + and -

 

 

Best Wishes

 

Antonio

Macros and VBA

2 Replies

  • mathetes's avatar
    mathetes
    Silver Contributor
    Jul 22, 2020

    antonio souza 

     

    I'm not a believer in macros or VBA routines, except as a last resort. So here's what could be a different way to bring that data across, making use of the recently available FILTER dynamic array function. I added a criterion column to your leafnew sheet, to identify the rows in question.

     

    You don't describe how this request of yours fits into a bigger picture....and I don't pretend that this could be a solution for all situations, so it may be that a macro is needed, and I'll let others who are good at that chime in. My point is that a fairly simple formula can bring over those rows--I only wrote two formulas...the results "spill" down into adjacent rows, however many are required. No need to copy the formula, no need to copy and paste....

     

    So I just wanted to make sure you're aware of these new functions.

Resources