Macro Help!

[Yandeez]

Hi and Merry Christmas!

I have recorded a simple macro that copies the values from one spreadsheet and paste the values into a file I have saved on my desktop. The macro worked maybe twice but now when I run it, it paste the values right back into the spreadsheet I copied it from.

Why is this occurring? I tried having the destination file open when I run the macro and I tried keeping the file closed and having the macro open the file from my desktop, then paste the values. Neither works.

I have maybe 9-10 reports that I need to publish during month end and it's nothing more than importing the source data into excel and copy/pasting into template worksheets and doing some simple formatting. I would like to automate so that once I have the source data in excel, I can run a simple macro then file save send.

 

[bobhc]

Why is this occurring?

..No idea as most members are not mind readers.
It would help others if you uploaded so that they can see where the macro has been corrupted.


.

 

[Yandeez]

Do you think it would serve your question better if, instead of being rude and impolite, you actually aimed to help others who are not as excel saavy as you and other ninjas? Perhaps you should learn from Luke.

 

[NARAYANK991]

Hi ,

Can you copy paste the macro here ?

Narayan

 

[Marc L]

Hi,

As a VBA code does what it's requested for,
the problem lies between the chair and the computer !

Without posting your code between tags, no further help possible …
Just read forum rules !
​

 

[Yandeez]

Sub Dailyapps()
'
' Dailyapps Macro
'
' Keyboard Shortcut: Ctrl+Shift+D
'
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
ActiveWindow.ActivateNext
Range("B9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("D17").Select
End Sub

 

[NARAYANK991]

Hi ,

I am not very sure , but I think the problem is because of the ActiveWindow usage ; can you specify the following ?

1. Will the file into which you are pasting always be open or closed ?

2. Will the paste be a single paste or multiple pastes ?

3. Will the copy range be the same for all pastes or will it change from one paste to the next ?

If you can upload the file which will have this macro , as well as at least one other file into which the paste is to be done , then the complete macro can be written. Extending this to cover more files will be an easy job which you can do yourself.

Narayan

 

[Hui]

I suspect that as Narayan said you don't have the correct workbooks already opened,

So instead of the line
ActiveWindow.ActivateNext
Use
Windows("My file name.xlsx").Activate