Select File, Specific Tab, Then Copy/paste
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
ADVERTISEMENT
Mar 25, 2013
I have a current macro that saves each sheet as their own file. The first 10-15 sheets are not really necessary, so I typically delete them once the macro has run. I am looking to add two things to my current code:
1. Add a tab called Start. Make the macro look for 'Start' tab and then just save each sheet to the right of that as its own file.
2. Save each sheet in the macro as just a copy/paste value. They current have a lot of links and it makes each file close to 3MB. That makes it incredibily difficult to paste all in one email.
Here is the current code:
Sub CreateWorkbooks()
'Creates an individual workbook for each worksheet in the active workbook.
Dim wbDest As Workbook
Dim wbSource As Workbook
Dim sht As Object
Dim strSavePath As String
On Error GoTo ErrorHandler
[Code] ..........
View 1 Replies
View Related
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
View Related
Nov 28, 2012
I have been trying to write VBA which basicly search for specific value in column A and copy values from that cell to down and two columns next. Select row and columns down after specific value in column A was find.
Like this
A B C
... ... ...
X 100 115
... ... ...
up to last row
I try with VBA below but it do not works.
Sub Select_Rows_GK()
Dim LR As Long, i As Long
LR = Range("A" & Rows.Count).End(xlUp).Row
For i = 1 To LR
If Range("A" & i).Value = "9000" Then
Range("A" & Rows.Count).Offset(0, 1).Select
Do While Not IsEmpty(ActiveCell)
ActiveCell.Offset(0, 1).Select
Loop
Exit For
End If
Next i
End Sub
View 3 Replies
View Related
Aug 23, 2012
I am trying to achieve something like this :
Find a specific text in my column B (example : "Proposal ID"), when "Proposal ID" is found, select this cell and offset to the column C (Offset(0, 1)). Then copy this cell value in another sheet.
This will be repeated with different texts (always in the column B), so if the text is not found, I need the macro to continue running.
View 5 Replies
View Related
Mar 4, 2014
Sheet1 Cells A1:J18 contain my data and fill down according requirement Where column I find always with end data filled.
ABCDEFGHIJ1DATENumPartyQtyStockQty PendingWhole Sealer-RetailerRemark215-02-141ab1250101000A-M12316-02-142ac1001270B-N125417-02-143ad32017100C-O312518-02-144ae70024D-P720619-02-145af2005E-Q9720-02-146ag1061F-R15821-02-147ah17710G-S23922-02-148ai277H-T271023-02-149aj10I-U321124-02-1410ak120J-V371225-02-1411al140K-W1221326-02-1412am142L-X1121427-02-1413an150a15rr16bb17cc18ddSheet1
Considering reference last data cell of column I I want to copy 15 last row and paste them in the same sheet with cells M1:T16 as shown
MNOPQRST1PartyQtyStockQty PendingWhole Sealer-RetailerRemark2ad32017100C-O3123ae70024D-P7204af2005E-Q95ag1061F-R156ah17710G-S237ai277H-T278aj10I-U329ak120J-V3710al140K-W12211am142L-X11212an150a13rr14bb15cc16ddSheet1
View 2 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
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
Aug 7, 2013
I need a vb code which would open different workbook automatically, autofilter the first row and select data and copy and paste in the other workbook.
View 1 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
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 29, 2012
I'm in the middle of building a code that will look at the value in Sheet: "DATA" column "J", for each row that matches a criteria that I pull from a cell - Sheet: "Test" Cell: "C1".
The number of rows varies. I want to paste columns: "I2, K2:P2, U2:AJ2" when the value in Sheet: "DATA" column "J" matches the cell "C1".
I'm using a Command button to click every time I want to generate the filtered data. And I would prefer the code to always copy data starting at Sheet: "Test" Cell: "K2".
I've copied, below, my test code that I have so far which only copies row 2 from the Sheet: "Data", and copies it into Sheet: "Test", starting at K2.
VB:
Private Sub CommandButton2_Click()
If Sheets("DATA").Range("J2").Value = Sheets("Test").Cells(1, 3) Then
Sheets("DATA").Range("I2,K2:P2,U2:AJ2").Copy
Sheets("Test").Cells(2, 11).PasteSpecial Paste:=xlValues, Operation:=xlNone
End If
End Sub
View 3 Replies
View Related
Jan 21, 2012
I need a code that will copy the format AND formulas of the entire row that I have selected (or activatedwith my cursor) to each and every blank row - until it gets to the last row of data on this spreadsheet.Since any employee could have more than 1 row of data - I am using a code that inserts1 blank row after each NEW employee name.
NOTE: This report is initially sorted by employee name so that each occurrence is grouped together.REPORT SPECIFICS:1) This report reflects typical paryoll information.2) Certain columns have data that is either in text, general, or number ($) format3) The number of columns may vary depending on which PR report is being worked4) For the sake of simplicity - we can assume that the column titles will always be across row 15) Each employee name on this report may repeat several times depending on how many weeks they worked,so the SUM() formula should adjust automatically to capture all the rows of data to add up for each employee
HERE IS MY SPREADSHEET:
texttexttexttexttextformulaformulaformulaDeptDivEmployee NameEE#Period EndHrs WorkedPTOAccrd LiabilityCSDINSEWilma Wilsont4561/7/1280.5$ 100.00 CSDINSEWilma Wilsont4561/16/12121$ 200.00 CSDINNECage Nick2581/7/1281$ 600.00 CSDINNECage Nick2581/16/12245$ 25.00 CSDINNECage Nick2581/23/12323$ 25.00 CSDINWPolly Cracker1781/7/12856$ 60.00 CSDINWPolly Cracker1781/16/12242$ 654.00 ARVIPWPolly Cracker1781/23/12322$ 2.00 ARVIPWPolly Cracker1781/28/12161$ 5.00 ARVIPWPolly Cracker1782/6/1284$ 3.00 CSDINSEDim Sum6871/7/1284$ 65.00 CSDINSEDim Sum6871/16/12126$ 5.00
HERE IS MY CODE TO INSERT 1 ROW
Sub InsertRowAtNewNameONE()Dim LR As Long, i As LongLR = Range("C" & Rows.Count).End(xlUp).RowFor i = LR To 2 Step -1If Range("C" & i).Value Range("C" & i - 1).Value Then Rows(i).InsertNext iEnd Sub
NOTE: See below: I will format and add formulas where I need on the 1st blank row that was createdand I need a code that will copy this particular row to all blank rows to the last.
recordtexttexttexttextcountformulaformulaformulaDeptDivEmployee NameEE#Period EndHrs WorkedPTOAccrd LiabilityCSDINSEWilma Wilsont4561/7/1280.5$ 100.00 CSDINSEWilma Wilsont4561/16/12121$ 200.00 2201.5$ 300.00
View 5 Replies
View Related
Jul 18, 2013
I have a code which is copy/pasting the selection specified number of times
VB:
Sub CopyNtimes()
Dim i
For i = 1 To Application.InputBox("How many times do you want to copy the selection?", "", 1, Type:=1)
With Selection
.Copy .Offset(i * .Rows.Count)
End With
Next
End Sub
How to modify this code so that it can paste the selection specified number of times leaving specified number or rows
For Example:
Selection is A1:Z10
need to copy paste 2 times...
Rows to leave: 3
So it should paste in the range A14:Z24 and then A28:Z38
View 5 Replies
View Related
Dec 27, 2013
I have dates in Column I (for the whole year) and i would like to paste them into specific cells in three different Columns A,B,C (ie A2, A5, A8, A11 etc) Same Numbers different letter for each respective column. I can get it to go from one cell to another but when i try to specify specific cells
This is what i have so far:
Sub Test()
Range("I1").Select
Selection.Copy
Range("A2").Select
ActiveSheet.Paste
End Sub
View 4 Replies
View Related
Mar 13, 2014
I create a macro to copy cells for data starting from A4 to N4 from sheet - Register to 'Case History' when the cells in column N are 'Yes'. Also, would like to copy specific range from A-N and not the entire row. Please refer to the attachment.
Tracker Dept..xlsx
View 2 Replies
View Related
Oct 28, 2009
This Macro is supposed to get certain totals for me from diffrent pages. Instead of selecting an entire row I want to select specific cells, so when it finds the word total on the sheet, whose location can change often it will return the value two cells away.
View 3 Replies
View Related
Sep 4, 2006
I have workbook "Sheet1" that has many rows and columns with values. Could I have code to find the cells value "plant2300" in column A and copy all the rows in column A that has the cell value "2300" into workbook Sheet2 (also copy with headers from Sheet1 to Sheet2 row 1).
View 3 Replies
View Related
Aug 29, 2007
I have a cell that contains various data but in the cell there will be a string of characters that will begin with either ON or TN followed by numbers. What I would like to do is have the sheet look at that cell and if there's a TN12345 (or any combination of numbers) or an ON123456 (same idea as the TN) I would like the sheet to pull just the ON or TN number out and paste it in a specific cell. The ON would have it's own cell to be placed in as would the TN.
Here's an example. C5 contains various data, a mix of text and numbers but it will have either an ON or a TN or both. I would like the sheet to automatically pull the TN number out and place it in A5 and the ON in B5. I would like this process to repeat all the way through the sheet to the max amount of rows since I have no way of knowing how many rows I'll need. Can this be done with a simple IF formula or does it have to be done in VBA or is it not possible at all.
View 7 Replies
View Related
Nov 29, 2007
I am attempting to copy a range from one worksheet and paste it to a new worksheet when the system clock hits a specific time. It needs to run continuously and identify that a previous colum has been filled and paste into the next open column. I am just getting my feet wet. I have some code to begin with but I am getting a 1004 error.
'Application. ScreenUpdating = False
Sheets("mysheet").Select
Range("E3:E10").Select
Selection.Copy
Sheets("CmyOtherSheet").Select
Range("B1:B8").Select
ActiveSheet.PasteSpecial xlPasteValues
'rTime = TimeValue("12:31:00")
'Application .OnTime rTime, "RunThisProcedure"
View 4 Replies
View Related
Aug 14, 2014
I am working on a macro, which asks the required field from multi select list box.
User can select required field, for example some user may be interested in
Region ID, Circle, BSC ID, Cell ID, Sector ID, City/Town.
Some user may be interested in Circle, BSC ID, Cell ID, Sector ID, City/Town.
Some user may be interested in BSC ID, Cell ID, Sector ID.
In sort, Selection on a multi select list box copy and paste that field from any folder.
That folder may have any location, and contains file which have.
Date
Vendor
Region ID
Circle
BSC ID
[Code] .....
View 1 Replies
View Related
Apr 18, 2014
I need copy duplicate data to another sheet, but paste only column(1,2,4,5,6,13,14)
[Code] .....
Cross-Post : [URL] .....
View 3 Replies
View Related
Sep 27, 2009
Yeah it is 1:33 am where I am.
I'd like to filter a column and paste only 2 columns to another sheet in the first available row.
I found a code that was posted by Tom Ogilvy and made a few adjustments: ....
View 14 Replies
View Related
Dec 23, 2011
My problem is as such; each month I receive an application for payment. This will contain around 20-30 worksheets. However I only need to interrogate around 3-4 specific ones. The worksheets I require contain data describing the plant/equipment a company has purchased, or materials purchased over a period of time. Each month the worksheets are updated with the previous months data appended to the bottom. I am required to established if the equipments have been purchased at the correct rate.
The worksheets are all protected thus in order to interrogate them I am required to copy an paste their contents into a new work book in order to format them and insert my new "assessment" columns. I need to keep the work sheets separate in the new work book as they have different layouts however they have the same layout and work sheet names each month.
I needs a macro which would allow me to open up a new book them copy the specific worksheets from a specific file into the new work book. I then have a separate macro which re-formats them into the layout I require.
View 9 Replies
View Related
Jan 3, 2013
Is there any way to copy a column that has a specific range
=COUNTIF(B10:BE10,"*p")
=COUNTIF(B12:BE12,"*p") etc
to copy that to a column lets say 5 columns to the right without my range changing?
=COUNTIF(G10:BJ10,"*p")
=COUNTIF(G12:BJ12,"*p")
I need the same range in all columns, and I cant seem to accomplish this.
View 3 Replies
View Related
Aug 11, 2013
I use the following codes to copy a cell which contains a picture and paste it to another worksheet. I think these codes copy more than just the picture.
Since pictures in Excel are not associated with cells, how do I copy just the picture in a particular cell and how to determine the size of the picture?
These codes are in a for loop.
Code:
tempCell.Offset(0, -1).Copy
Workbooks(fileName & ".xlsx").Activate
Workbooks(fileName & ".xlsx").Sheets(1).Cells(cell.Row, cell.Column - 1).Select
Sheets(1).Pictures.Paste
Workbooks(fileName & ".xlsx").Sheets(1).Cells(cell.Row, cell.Column - 1).RowHeight = tempCell.RowHeight
View 3 Replies
View Related
Nov 15, 2006
I have a excel file with two worksheets. I need to find a specific row in column g, cut the entire row, and paste it into sheet2. I have started off by using this code, that I got off of some site. It works wonderfully in finding the specific cell, but just copys and moves the specific cell into sheet2. Whereas I need it to cut and paste the entire row where it finds the cell that begins with a 3 in column G.
Sub Copy_To_Another_Sheet_1()
Dim FirstAddress As String
Dim myArr As Variant
Dim Rng As Range
Dim Rcount As Long
Dim i As Long
View 9 Replies
View Related
Apr 30, 2006
Is there a way to copy then paste specific cells from closed .xls files in one directory ( more than 500 files ), and how, if all files are with one known password protection( my own files ).
View 9 Replies
View Related
Jul 22, 2007
I enter a part number in "D1". The number of components required for that part number is returned in "G1" based on a VLookup. I would like a macro that will copy and paste the part number I enter in "D1" in the first empty cell in column "D" and will paste it once for each component (G1) required. For example, if the number of components required is 4, and the first empty cell in column "D" is "D10", I would like to paste the part number in cells "D10", "D11", "D12", and "D13".
View 2 Replies
View Related
Mar 22, 2013
I've got folder of 44 files - all copies of each other apart from the data entered in the cells - which have 7 worksheets in them all named differently (Each one is a different day of the week - this is the work of someone else that I have to work on !).
I want to copy the data in the columns A-R of each worksheet from row 2 to the last one with data in column M into a single file so that I can then then work on all that data in a single file..
View 9 Replies
View Related
