Is It Possible For A User To Select A File And Cell To Paste In During A Macro
Sep 3, 2008
I was planning to do was have a macro where the document opened and the cell that information was pasted in was decided depending on which cell was selected when the macro was run.
This worked fine, but it does mean that it needs someone who can edit the VBscript every time there is a new starter or someone leaves so isn't really viable.
What I was wondering, is if it is possible for me to start a macro, which then drags in the information I need from a text file, does all the calculations and rearranging, and then brings up a prompt for the user to open the document they want to paste it in, click on the cell where the pasting will start, and then the macro continues from there?
To try to explain it in as clear a way as I can. Say we have three managers, Alan, Brenda and Charlie. In Alan's team there are 10 staff members. Aaron, Bill etc.etc. If we have just run the report for Aaron, the macro will run, automatically drag all the information in and rearrange it, then it will pause while the user opens up the "Alan" spreadsheet, they select the "Aaron" first cell, click OK or something, and then the macro continues on its happy way?
View 9 Replies
ADVERTISEMENT
May 27, 2009
I have two workbooks. I am copying cells data from one workbook A to workbook B with macro - pretty easy. Now, after selecting and copying cell data in workbook A and switching to workbook B I want to be able to pause the macro and wait for the user to chose a correct cell where he/she wants to paste the data. After the selection the macro would automatically paste the data in selected cell. Since I am completely newbie any help would be appreciated. Here is the macro I have so far :
Sub CopyVIN()
'
' CopyVIN Macro
' Macro recorded 5/26/2009 by Pacific Coachworks
'
Sheets("Sheet1").Select
Range("G9:R9").Select
Selection.Copy
Sheets("For copying VIN").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows("Cami's Production Schedule.xls").Activate
' Here I'd like to have a code for waiting for a user selection and pasting the data automaticlly.
End Sub
View 9 Replies
View Related
Aug 15, 2014
I am wanting to pause a macro to allow a user to select a cell and when the user hits enter the macro starts running again. My intention is that a user would select cell B5 or B8 or B55 and then hit enter and the cell would move 6 cells right and enter a value from the macro. The following is what I have tried and even though it allows to select a cell, it doesn't make that cell the active cell.
[Code].....
View 3 Replies
View Related
Oct 17, 2011
I am writing code that requires data from another workbook. Instead of hardcoding the directory and filename (which changes daily), how can I have the code prompt the user to do this for me?
I want to open the file with:
links updated
read-only
Also, is there a way to have an initial directory that appears so the user need not go through a dozen directories every time to get to the file?
View 3 Replies
View Related
Feb 4, 2014
I am trying to find a macro that can search a sheet for any cell that contains the text "Not on AOI" selects a range that contains that cell, 81 rows below, and 2000 columns to the right, then cuts the selection and pastes it 162 rows below the original cell where the text was found. What's hard is that the number of columns between the "Not on AOI" cells is variable.
I'm very new to excel macros and the parts I think I've put together are:
Cells.Find("Not on AOI", After:=ActiveCell, LookIn:= _
xlFormulas, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:= _
xlNext, MatchCase:=True).Activate
[Code].....
View 1 Replies
View Related
Feb 13, 2010
Feedback.xls is the file I want to have my macro run in. By pushing a button to run the macro, it will prompt the user to select where their file is on their computer. This file will have different file names based on the end user. For example sake, I've included CAP.xls.
After the user selects their file, it will prompt them to choose which tab to copy and paste data from. Typically, there will be 30+ tabs on their worksheet. For example sake, I've created 5 tabs on CAP.xls. The tabs in their workbook will be labeled as I have labeled mine, M1 CAP, M2 CAP & M3 CAP. Therefore, it should prompt the user to select which tab to choose from.
After the user selects their tab (M1 CAP, M2 CAP, M3 CAP), the macro should prompt the user to choose which row of information to copy. In CAP.xls, I have a few rows of information to choose from. They all start with FY10A1, FY10A2, FY10A3, but this information could change. Once they select the row, it will copy each of those fields onto the respectively labeled fields on Feedback.xls.
View 5 Replies
View Related
Sep 9, 2013
I have a list of asset data (columns A:Z and over 20,000 rows).
I want a macro to find the word TAG in column F then cut off the entire row for all that meet the criteria and paste in another sheet. So I can see both sheets.
View 1 Replies
View Related
Aug 17, 2009
i have about 80 files that all have the same structure. I also have a general excel file that I need to fill with values from each of the 80 files. I'd like to use a macro that can be applied to each of the 80 files at a time (or at the same time, if that's possible) so I can copy the cells (only the text, not the formula) I need from each individual file to the general sheet.
e.g.,
each file is:
xyz 123
abc 456
ghi 956
I need to copy the "123" to a cell in the general sheet, "456" to a different cell in the general sheet and so on (to be able to choose which cell in the general sheet I am copying to).
View 12 Replies
View Related
Oct 10, 2013
I have a workbook with several sheets. I have written a macro to check if the value in a column is matching with that in another column in another sheet and return the value in a preceding column in the second sheet into a preceeding column in the first sheet. The code looks likes this and seems to be working fine.
Sub Copy_Price_Code()
Dim Rw As Long
For Rw = 6 To Sheet6.UsedRange.Rows.Count
[Code] ....
However, there would be more sheets in the workbook in future and I need to edit the macro in such a way that the user can select the source and destination sheets which I could use in the above macro. I tried to do it as follows but its not working.
Sub Copy_Price_Code()
Dim Rw As Long
Dim Rw2 As Long
Dim Sourcesheet As String
Dim Destinationsheet As String
Sourcesheet = InputBox(Prompt:="Enter source sheet name. (example: Sheet1).", _
[Code] .......
View 3 Replies
View Related
Aug 27, 2009
I have a protected worksheet with a mixture of locked and unlocked cells, and I'd like to provide an option to allow the user to select a large range of mixed (locked and unlocked) cells and, in the unlocked cells only, pre-populate with a standard formula.
I've got most of it working, but what I need is the specific code that allows the user to specify the range of cells they want the macro to act on.
View 9 Replies
View Related
Dec 7, 2006
Using a script but when I use the given Range it is still using the complete WorSheet.
View 9 Replies
View Related
Feb 18, 2008
I would like to Pause a macro to allow the user to select a range on the spreadsheet and then click OK to continue with Macro.
I tried to use Msbbox, but I can't figure out how to allow user to access the spreadsheet to highlight the desired range.
View 9 Replies
View Related
Apr 30, 2008
I cant seem to get to work together in the same macro but that work great seperated. I need them to be in the same macro. The first just simply copy's text from one workbook to another:
Sub Test2()
ActiveSheet. Range("a1").Copy _
Destination:=Workbooks("punchlist.xls"). Sheets("Sheet1").Cells(Rows.Count,1).End(xlup).Offset(1,0)
End Sub
That worked ok but I needed to change it to the "active cell" instead of cell "a1". So then this line of code was made:
Dim userInputCell As Range
On Error Resume Next
Set userInputCell = Application.InputBox("Use the mouse to select a cell on any sheet", type:=8)
On Error Goto 0
If userInputCell Is Nothing Then
MsgBox "Cancel pressed"
Else
Msgbox "You selected " & userInputCell.Address(,,,True)
End If
The second code works just the way I want it but it doesnt copy over the text to the other workbook. I assume the 2 codes need to be together but I cant get it to work without errors. I also need the text to copy over without changing the borders on either workbook.
View 3 Replies
View Related
Mar 6, 2012
I want to create a macro that will select all the worksheets (names and quantity will vary) and saves the file as the current file's name but in PDF. Since I only know how to record a macro it specifies the worksheet names but I need it for various workbooks. The name will vary plus the number of tabs can go anywhere from 3 to 40.
View 9 Replies
View Related
Nov 6, 2011
I use Excel 2003 at work. I'm looking for some code that will Open a folder and then lets the user to select a file then continues to run the macro.
The file name they select will look similar to this K2271011.504 or K3011111.201
View 3 Replies
View Related
Aug 25, 2009
I have an event handler that runs a procedure when a user double clicks a cell. This procedure modifies the contents of this cell and other cells. I want the user to be able to select the cell so they can double click it, but I don't want them to be able to single click it and modify it by hand (accidentally or not). Is there a way to lock/unlock the contents of the cell without locking the cell itself?
View 6 Replies
View Related
Sep 18, 2009
I am trying to develop a macro that allows the user to copy and paste a set of date and time values, move them to the next sheet and increase the month by 1. I need help trying to find out how can I create a counter for the program to keep running without the need to reprogram the macros again.
View 14 Replies
View Related
Jun 26, 2012
I already have code that I believe is supposed to do what I'm trying to accomplish. However, it doesn't fully work. The Macro is below:
VB:
Workbooks.Open Filename:="C:Documents and Settingsplp138DesktopExample 1.xls"
On Error Resume Next
With Workbooks("Example 2.xls")[code]....
Basically, what I'm trying to accomplish is to have a certain range from one file copied, and pasted in the next available (empty) row of another file. While debugging I see that everything is working except for the actual pasting.
View 3 Replies
View Related
Sep 16, 2009
Hoping someone would be able to help me with this. I have a sheet (example attached) and this sheet has a number of varying description types in the W coloumn (usually approx 10,000 rows). This field is manually input so there could be spelling mistakes and/or non standard descriptions.
What I would like, if possible, is a macro that would look at the D column and if this is 'GENERAL LEDGER', it would then look at the W column.
An input box would come up, and would list the different descriptions it found in column W, and number them. It would only list each different description once.
e.g.
1. Bank charges
2. Bank charge
3. Cash
4. Fund Custodian Fees
5. Fund Manager fee
6. Interest income cash account
7. Interest income cash acc
8. Miscellaneous expenses
9. Miscellaneous income
10. Other income
11. Sec lending comm
12. Sec lending commission income
13. Tax Reclaimable - Dividends
14. Withholding tax dividend
The user would then be able to type in the corresponding numbers, if possible seperated by a space, comma or semicolon and the macro would then run through the sheet and delete the entire row if D was GENERAL LEDGER and W was the selected description.
View 9 Replies
View Related
Jun 11, 2003
I have created a user form using the addins that come with excel to create a calendar that will allow the user to select a date and automatically put that in a specific cell. My system is office 2000.
The code is as follows:
Private Sub OK_Click()
Dim i As Integer
Dim myCell As Range
i = 0
For Each myCell In Selection
myCell.Value = Calendar1.Value + i
i = i + 1
Next myCell
Unload Me
End Sub
Now, I gave it to my boss who has xp, and I get the following VB error when she chooses a date and clicks on the ok button:
Method 'Value' of object 'ICalendar' Failed
View 9 Replies
View Related
Feb 12, 2014
write a macro - Condition: When i select "Audit Round" = "Round 1" in B2 and press a button it will automatically copy data from B5:B8 and paste special value in C5:C8. Likewise if I select "Audit Round" = "Round 2" in B2 then it should lookup "Round 2" in "Row 4" and paste special values from B5:B8.
Here an example:
A
B
C
[Code]....
View 1 Replies
View Related
Sep 8, 2006
I am making a macro that requires that user to enter a directory path into a input box and the macro will open the file. Right now it works fine bit i would like to change the code so that instead of using an input box it brings up a box that will allow them to browse files on their computer and then select one to open.
View 4 Replies
View Related
Nov 30, 2008
I have a macro that automatically takes data from one sheet, copies it and pastes it on another sheet, but I want it to select the empty column that is to the right of the last column to contain data (so basically the next empty column). What code would I enter into editting my macro to be able to do this please?
Also, one thing I've tried to do with my macro is that when I select the data, it's suppose to select from the first name to the last. When doing the actions for the macro, I use "Ctrl + Shift + Down Arrow Key" to make the selection select the bottom of the list. However, when running the macro, if the list is longer (or shorter), the macro selects that number of cells instead of the action of just going to the bottom of the list. For example, if during the actions of the macro, I recorded using the Ctrl + Shift + Down Arrow Key that 50 cells were selected...next time if I have 60 cells, only 50 are selected, even though I used an action command which should select all the cells in that group (in this case being 60). Is there some code I can add to that so this would work correctly?
In case its required, here is the code from the macro:
View 11 Replies
View Related
Oct 7, 2007
I have a macro, it imports data from a text file and separates the data from the text file into columns. My issue is that it automatically picks a fixed filename. I would want to manually select a filename from different directories.
Below is my macro: ...
View 9 Replies
View Related
Feb 17, 2009
I have finally come up with a macro and would like to install it to around 10 people's "Personal Workbook" in my department. Let me explain a bit further...
We use web-based software which has an "Export to Excel" option which we all use. The resulting data populates into a spreadsheet automatically.
I would like the user to be able to click on Tools/Macro/Run Macro and then run that particular macro. I assume that this macro should be in the "Personal Workbook" so that the macro will be visible no matter what worksheet they may have open?
Can this be done automatically/with a macro or must this be manually done?
I"m using Excel 2003.
View 14 Replies
View Related
Nov 9, 2011
I have a list box, that I have set MultiSelect to "1 - frMultiSelectMulti"
What I would like to be able to do is select multiple items and have those items appear in a single cell on my spreadsheet.
Maybe something like: WA, CA, OR Forms.ListBox.1 is the name of my object. Or do I need to use a different object type?
View 1 Replies
View Related
Jul 11, 2012
I have an excel file with multiple sheets. I want my HR team to update that file, but one user shouldnt see the entries of other users. I want to make a consolidated file out of three files. This excel template is for salary input.
View 3 Replies
View Related
Nov 15, 2008
I am making a worksheet that I intend to use to track my money. When I first open the worksheet, it opens on a tab where I can click a button to report a type of transaction. For example, if I make a withdrawl from the bank for $50, I click the button, it takes me to the sheet that tracks my bank-related stuff, selects a cell and opens up a form, at which point I type in what the transaction consisted of. However, the sheet also tracks what is in my wallet, so I'd like to finish reporting the bank transaction in the form, and have a button to click that reports the wallet part automatically.
So, essentially what I need to do is select several non-contiguous cells that are in the last row of the bank sheet, copy them, switch to the wallet-tracking sheet, and paste them in a row that is one past the last row of that sheet. The paste should keep the cells next to each other, even if they were non-contiguous when they were being copied.
View 10 Replies
View Related
Jan 20, 2007
I would like to select cell A6 after pasting a large range so that when send to user the entire range is not selected. I tried
wsDestPage.Range("A6").Select
And
wsDestPage.[a6].Select
I receive
Select method of range class failed
How can I select a single cell after pasting the large range?
View 5 Replies
View Related
Jul 22, 2013
An Excel file named CLEARING is availble in thr central server system. it will be accessed by 5 or 6 staffs from some other systems what i need is when one person accesses/edits this file the others should not even able to open in the read only file ,when the file is saved and closed ,then only the next person should access it
View 1 Replies
View Related
