Microsoft Office Forums

Go Back   Microsoft Office Forums > >

Reply
 
Thread Tools Display Modes
  #1  
Old 10-04-2022, 02:46 PM
et_33 et_33 is offline Copy Specific Text String To Clipboard At End Of Macro Windows 11 Copy Specific Text String To Clipboard At End Of Macro Office 2021
Novice
Copy Specific Text String To Clipboard At End Of Macro
 
Join Date: Oct 2022
Posts: 4
et_33 is on a distinguished road
Lightbulb Copy Specific Text String To Clipboard At End Of Macro

Hello all,

I need to manually input some data from each day of the year by saving certain excel files all under the name "Test Slide Distribution" and I am tired of typing in the SaveAs window that phrase hundreds of times. Is there a way I can add the specific phrase "Test Slide Distribution" to be copied to the clipboard so I can simply hit control V and paste in the SaveAs window after I run my Macro's needed to get data compiled each day?


This is my current code, and I would like to add the phrase to the clipboard after this final macro:

Sub Get_WTP_Data_Feb22()
'
' Get_WTP_Data_Feb22 Macro
'

'
Sheets("Data Today").Select
Range("B37:F64").Select
Selection.Copy
Sheets("WTP").Select
ActiveWindow.ScrollRow = 6386
ActiveWindow.ScrollRow = 6366
ActiveWindow.ScrollRow = 6315
ActiveWindow.ScrollRow = 6255
ActiveWindow.ScrollRow = 6225
ActiveWindow.ScrollRow = 6174
ActiveWindow.ScrollRow = 6144
ActiveWindow.ScrollRow = 5234
ActiveWindow.ScrollRow = 4931


ActiveWindow.ScrollRow = 4658
ActiveWindow.ScrollRow = 3436
ActiveWindow.ScrollRow = 3062
ActiveWindow.ScrollRow = 2981
ActiveWindow.ScrollRow = 2769
ActiveWindow.ScrollRow = 2557
ActiveWindow.ScrollRow = 2314
ActiveWindow.ScrollRow = 2244
ActiveWindow.ScrollRow = 1193
ActiveWindow.ScrollRow = 1051
ActiveWindow.ScrollRow = 607
ActiveWindow.ScrollRow = 536
ActiveWindow.ScrollRow = 354
ActiveWindow.ScrollRow = 294
ActiveWindow.ScrollRow = 223
ActiveWindow.ScrollRow = 203
ActiveWindow.ScrollRow = 31
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 1
Range("A1").Select
Selection.End(xlDown).Offset(1).Select
ActiveCell.PasteSpecial xlPasteValues
End Sub
Reply With Quote
  #2  
Old 10-05-2022, 02:13 AM
Debaser's Avatar
Debaser Debaser is offline Copy Specific Text String To Clipboard At End Of Macro Windows 7 64bit Copy Specific Text String To Clipboard At End Of Macro Office 2010 32bit
Competent Performer
 
Join Date: Oct 2015
Location: UK
Posts: 221
Debaser will become famous soon enough
Default

Why don't you just include the saving in the macro?
Reply With Quote
  #3  
Old 10-05-2022, 06:29 AM
macropod's Avatar
macropod macropod is offline Copy Specific Text String To Clipboard At End Of Macro Windows 10 Copy Specific Text String To Clipboard At End Of Macro Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

What's with all the Selecting and ScrollRow? For example, all of:
Code:
Sheets("Data Today").Select
Range("B37:F64").Select
Selection.Copy
can be reduced to:
Code:
Sheets("Data Today").Range("B37:F64").Copy
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #4  
Old 10-05-2022, 07:47 AM
et_33 et_33 is offline Copy Specific Text String To Clipboard At End Of Macro Windows 11 Copy Specific Text String To Clipboard At End Of Macro Office 2021
Novice
Copy Specific Text String To Clipboard At End Of Macro
 
Join Date: Oct 2022
Posts: 4
et_33 is on a distinguished road
Default

You're right I could definitely shorten it down this is just a macro from a previous employee so I haven't really messed with it. Still does not solve the copying of text string "Test Slide Distribution" to the clipboard at the end however.
Reply With Quote
  #5  
Old 10-05-2022, 07:50 AM
et_33 et_33 is offline Copy Specific Text String To Clipboard At End Of Macro Windows 11 Copy Specific Text String To Clipboard At End Of Macro Office 2021
Novice
Copy Specific Text String To Clipboard At End Of Macro
 
Join Date: Oct 2022
Posts: 4
et_33 is on a distinguished road
Default

If I knew how I would! Unfortunately every file is named a different date something like xxxx_xx_9-11-22
xxxx_xx_9-12-22
And so on..
Reply With Quote
  #6  
Old 10-05-2022, 02:27 PM
macropod's Avatar
macropod macropod is offline Copy Specific Text String To Clipboard At End Of Macro Windows 10 Copy Specific Text String To Clipboard At End Of Macro Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

For example:
Code:
ActiveWorkbook.SaveAs Filename = ThisWorkbook.Path & "\Test Slide Distribution " & Format(Now, "mm-dd-yyyy") & ".xlsx", FileFormat:=xlWorkbookDefault, AddToMRU:=False
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
  #7  
Old 10-10-2022, 02:07 PM
et_33 et_33 is offline Copy Specific Text String To Clipboard At End Of Macro Windows 11 Copy Specific Text String To Clipboard At End Of Macro Office 2021
Novice
Copy Specific Text String To Clipboard At End Of Macro
 
Join Date: Oct 2022
Posts: 4
et_33 is on a distinguished road
Default

Unfortunately I am using a macro in a separate workbook to pull data from each slide distribution day workbook, but the data will only pull if a workbook is titled Test Slide Distribution on the right path. The previous owner has many different cells tied to the specific naming conventions and it would take even longer to go through and change all of the necessary cells, also the cells that need to be copy and pasted must be changed occasionally to reflect new employees and so on. I ended up just manually resaving each one after pulling the data I needed. Thank you for the help though! If anyone else has an idea of a vba function at the end of a macro to copy something specific to the clipboard as text please reply!
Reply With Quote
  #8  
Old 10-10-2022, 02:49 PM
Guessed's Avatar
Guessed Guessed is offline Copy Specific Text String To Clipboard At End Of Macro Windows 10 Copy Specific Text String To Clipboard At End Of Macro Office 2016
Expert
 
Join Date: Mar 2010
Location: Canberra/Melbourne Australia
Posts: 3,932
Guessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant futureGuessed has a brilliant future
Default

I still don't think it is the best approach to load this info to the clipboard but there are examples which can be found via google where people are putting text into the clipboard via VBA. eg
How To Use VBA Code To Copy Text To The Clipboard
__________________
Andrew Lockton
Chrysalis Design, Melbourne Australia
Reply With Quote
  #9  
Old 10-11-2022, 06:24 AM
macropod's Avatar
macropod macropod is offline Copy Specific Text String To Clipboard At End Of Macro Windows 10 Copy Specific Text String To Clipboard At End Of Macro Office 2016
Administrator
 
Join Date: Dec 2010
Location: Canberra, Australia
Posts: 21,956
macropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond reputemacropod has a reputation beyond repute
Default

Quote:
Originally Posted by et_33 View Post
The previous owner has many different cells tied to the specific naming conventions and it would take even longer to go through and change all of the necessary cells, also the cells that need to be copy and pasted must be changed occasionally to reflect new employees and so on... If anyone else has an idea of a vba function at the end of a macro to copy something specific to the clipboard as text please reply!
That still doesn't warrant trying to put anything into the clipboard. If the save names occur in particular cells, it's a simple matter to insert those cell names into the save string without involving the clipboard. This can be done with any string you can put into the clipboard. However, since you really haven't told us anything much about what you're trying to achieve - instead of fixating on a particular approach - all we're doing is guessing.
__________________
Cheers,
Paul Edstein
[Fmr MS MVP - Word]
Reply With Quote
Reply

Tags
macro, vba

Thread Tools
Display Modes


Similar Threads
Thread Thread Starter Forum Replies Last Post
WORD MACRO COPY/PAST from clipboard rachidlea Word VBA 0 11-16-2021 09:38 AM
Need to copy specific formated text from Word and paste from clipboard into a console application Userx Word VBA 0 10-09-2020 05:58 AM
Search for specific data in a text string teligence Excel 1 05-20-2017 11:51 AM
Copy Specific Text String To Clipboard At End Of Macro Find & selected paras with highlighted text & copy to clipboard marceepoo Word VBA 1 09-14-2012 08:20 PM
Copy Specific Text String To Clipboard At End Of Macro Mark text in a text box and copy to clipboard (with button) ArthurM PowerPoint 4 02-19-2012 11:33 AM

Other Forums: Access Forums

All times are GMT -7. The time now is 09:13 AM.


Powered by vBulletin® Version 3.8.11
Copyright ©2000 - 2024, vBulletin Solutions Inc.
Search Engine Optimisation provided by DragonByte SEO (Lite) - vBulletin Mods & Addons Copyright © 2024 DragonByte Technologies Ltd.
MSOfficeForums.com is not affiliated with Microsoft