I need to search column C for the text "X". If an "X" is found, I need to copy the text from the cell directly left of it and paste it into a seperate worksheet into cell B2, then go back to the original worksheet and delete the cell with "X" on it and the cell to the left.
View 9 RepliesI've attached a sample workbook in which there are 3 macro-buttons.
The buttons will paste a shape in the active cell. So this means the buttons themselves could be deleted and replaced with a shape.
Since locking and then protecting the cells disables the macros, how can I amend the code to make sure the buttons' cells are protected from the copying and pasting macros? Or, how do I ensure that the macros only work in A1 - E5?
I have a challenge for any macro wizard out there - this is something I really need and I'd be happy to reimburse whomever can help with an iTunes gift card or something.
I regularly receive a dataset such as the one you see here on the tab called 'Data comes in varying # of rows' (either link should work):
[url]
[url]
The dataset comes each time with a different number of rows, and what I need to do is combine all the 'Issue' and 'Issue Description' columns (there are 8 total) into just two columns.
I think this should be relatively easy for someone who has a better knowledge of macros by simply copying and pasting the data on top of itself (as I have done on the next tab called 'Want to change to this format'), and then simply deleting from the second section the 'Issue #1' and 'Issue #1 Description' columns, and shifting all the data left by two columns.
If you repeat this process for the third and fourth sections (for the third, you must delete 'Issue #1' and 'Issue #1 Description' and 'Issue #2' and 'Issue #2 Description' and shift the data left, while for the fourth you must delete Issues and Issue Descriptions 1 to 3 and shift the fourth Issue and Description to the left), you will end up with the columns A, B, C, and D as I have on the 'Want to change to this format' tab. (The extra columns after column D should all be deleted.)
Finally, the 3 extra header rows that are now at the top of the bottom three copied-and-pasted sections should all 3 be deleted.
I am creating a macro for a monthly report of inventory. When I filter out items and the copy and paste them to another sheet, how do I tell the macro to paste it in the next available cell rather than say A20? Because the report has a different number of items and filter results from month to month.
View 3 Replies View RelatedI am having trouble with a project for school. The instructions are: (In Worksheet 1) Type Your Name. Now, begin recording a Macro. Copy and Paste your name from Worksheet 1 to Worksheet 2. Fill in the background and edit the font. End.
Here is an example of the coding.
ActiveCell.Range("A1:E1").Select
Selection.Copy
ActiveSheet.Paste
Application.CutCopyMode = False
With Selection.Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
[Code] .....
Now, according to the instructions I should be able to run the Macro in any worksheet....but anytime I run the Macro in any other worksheet I just get a yellow block without my name there.
i have selected cells from workbookA (b2:b8) , i want those selected cells gets pasted in workbookB in a transpose way.
like B1 cell in workbookA pasted into A2 cell in workbookB , B3 cell pasted into D2 , B4 cell pasted into B2 and B5 cell pasted into G2......
i have code but not working
Code:
Private Sub CommandButton1_Click()
Dim STRFILENAME As String
Dim O As Workbook
Windows("Copy of Bill Schedule Form1.xlsm").Activate
Sheets("Sheet1").Activate
ActiveSheet.Range("b2:b8").Select
[Code]....
So i have sheet 1 with some cells locked and protected (but open to copying) as a template and sheet two will be where people paste one or more of the template formula on an ongoing basis. How do i mantain the locked and protected cells after they have been pasted? I have unlocked sheet two pasted the first template and had to choose protect cells after pasting, and further pasting of locked cells are not protected.
View 1 Replies View RelatedI have a sheet made into a form with some check boxes included. If I select all the relevant columns, create a new sheet, and paste into cell A1 - all of the information carries over just fine. You can still select cells and check the boxes in the new sheet.
However, when I record this action as a macro, using the macro produces a picture of the selected columns instead of actually copy/pasting the information. I can't click on the cells or check boxes. I can only move the picture around within the new sheet.
I have a macro that takes a worksheet and copies it to another workbook. It works, but the graph that is contained in the new worksheet ends up a little screwy; for instance, the Plot Area is larger than it should be (height-wise along the screen), and the chart's legend isn't in the exact same place as in the original chart.
ThisWorkbook.Worksheets(tempSheet).Copy After:=wb.ActiveSheet 'before:=ThisWorkbook.Sheets(1)
RenameTemplate
'unlink copy from template
'must unprotect before unlinking. We'll leave it unlocked cuz we don't care.
wb.ActiveSheet.Unprotect
'cells
wb.ActiveSheet.UsedRange.Copy 'ThisWorkbook.Sheets(tempSheet).UsedRange.Copy
wb.ActiveSheet.UsedRange.PasteSpecial Paste:=xlPasteValues ......................
I have productivity data sheet of employees for a month and want to update in a tracker sheet.Every productivity sheet has 5 columns containing numbers.Since its monthly it would be contain 30-31 rows and.I want to copy this data then go to tracker apply filter with respective employee name and paste it there.Is there a way to do this using vba code?
I have prepared vba code to copy paste individual rows from productivity sheet to tracker.But preparing row by row code makes it way too big.Hence i am looking for another solution.
I want to copy the current region on sheet 3 and paste that into sheet2 starting with cell E4. But I don't want to actually use the copy method. I believe there is a way to do this. I was thinking the following:
[Code] ......
The idea I had was to simply state that cell E4 would be assigned the value of CurrentRegion on sheet 3. Is there a way to bypass the copy method?
If not, how to copy, then paste?
I need to figure out a way to search for a string in column D and then paste the entire row that string is in to a new sheet. I'd like for this to continue until I paste all of the rows of that string into the next sheet. This is what I got so far, and when I run it, it says an error occurs and then it says either press enter or paste. After I hit enter, it pastes the first row to the next sheet, but that's it. Also should I be doing this with a button?
Code:
Sub SearchForString()
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
[Code]....
I have multiple worksheets with part numbers and prices for different computer parts. On each worksheet I have multiple sets of part numbers and prices but I only need one the information from specific part from each worksheet. Luckily, the part number I need is also the name of whatever worksheet contains it. I want to take the part numbers and prices from the parts I need and put them in one master sheet called "PartsNumbersCombined". Currently I haven't even attempted to format the data in PartsNumbersCombined, I am just trying to actually pick up and move the correct data. Here is my code:
Sub harvest()
ShtCount = ActiveWorkbook.Sheets.Count
For i = 2 To ShtCount
[Code].....
The part numbers are in column A and the prices are one row down in column C. I feel like I am close but I can not get anything pasted on my master sheet.
I have been searching the boards long and hard for this solution and have yet to find that applies fully. Every day I need to search through 20 worksheets for transactions occurring on a specific date and copy the row onto a new worksheet. I would like to run a macro that would allow me to search for a specific transaction date in column C of each worksheet and if the date matches it will copy the entire row to a new worksheet.
The issue I am having is that the transaction lists are a running sum of all the transactions for each account. Therefore, the range that the macro needs to search will change daily as well. Furthermore, the date that I would be searching for could potentially appear in column A or B but I only want the row if the date matches that of column C. Also, on some sheets there may not be a transaction at all.
I need help with a macro for copying and pasting of cells. I believe this should not be a problem for the Excel VBA experts, but for someone who can only record macro, I'm really at a loss.
Attached is a sample file, where sheet 'Source' is an example of the sheet from which data are to be copied. The other sheet, sheet 'Final' is an example of the final format that I need. The reason I'm doing this is I'm planning to upload my data into Access and so I need to convert them into a list format.
List of target columns in sheet 'Final' and source cells in sheet 'Source':
Column A: Biz ID - not sure if I really need this, by right it should be listed automatically once I paste the data
Column B: B2 of 'Source'
Column C: B2 of 'Source'
Column D: B1 of 'Source'
Column E: row 6, relevant column
column F: column K
column G: row 5, relevant column
column H: the specific amount
So basically I'm creating an entry for every amount in the table.
I have a macro that copies all my sheets onto a new sheet called combine. It is pasting onto the combine sheet starting on cell A2 instead of cell A1. Following is my macro.
Sub Combine()
Dim J As Integer
Sheets(1).Select
Worksheets.Add
Sheets(1).Name = "Combined"
On Error Resume Next
For J = 2 To Sheets.Count
Sheets(J).Activate
Range("A1").Select
Selection.CurrentRegion.Select
Selection.Copy Destination:=Sheets("Combined").Cells(Rows.Count, 1).End(xlUp)(2)
Next
End Sub
I am trying to create a search function in a database I have made. I have a userform with drop down boxes that people can choose what they want to search for. Once they have chosen and clicked the search button I want it to search only in the relevant column, i.e. search for the word in the dropdown for "engine" only in the "engine" column of the database. Once it has found the appropriate cells with the answer, I want it to delete all other rows that don't include the answer.
Once it's done this it needs to do it again for the other fields, unless they are empty. I have used 'If engine_type <> "" Then' as the starting point so that it only searches chosen fields.
http://www.excelforum.com/excel-prog...problem-2.html
I have a workbook with a macro that searches through worksheets to find certain values and place the location of the value in a worksheet cell. The following line ontains the found cell.
I have a spreadsheet that has a bunch of codes, I want to search all at once in another spreadsheet. If there there I want to copy the data related to that line into my spreadsheet.
Example:
I want to search a long list of items (in column D) in my “work in progress spreadsheet” The program would look into the “Data base spreadsheet” find the “item to search” in the database spreadsheet and copy the “product code” relative to those items, and populate column C with the product codes.
My work in progress spreadsheet
Col C ________________Col D
product codes ________Items to search
....................................G28
....................................G26
....................................F46
....................................J34
Database spreadsheet
Col C __________________Col E
Product codes __________items to search
Product code 16345210 ........J34
Product code 14454685 ........G26
Product code 14587541 ........G28
Product code 15754546 ........F46
I would like to select all cells in column A that begin with the text "SP". Some cells will be contiguous but others will not. For instance in one case, I would like the macro to select cells with the text "SP" which would result in cells A1, A2, A3, A10, A15 being selected. I am working on building a macro that will then do other things to these cells/rows so this is the first step.
The below code will select the first cell with "SP". How would I alter this code to only search Column A and select multiple cells? or totally different code.
Code:
Sub test()
Dim r As RangeSet
r = Cells.Find(what:="SP", LookIn:=xlValues, lookat:=xlWhole)
If Not r Is Nothing Then r.Select
End Sub
I have been asked to fix a macro that is supposed to search column B and find any cells ending with .mov. Any row fitting the criteria is to be copied to Sheet2. The file is a internet traffic log and we are trying to determine the number of downloads we have on our video files, which are all .mov format. Sheet1 can exceed 3000 rows.
Here's what I have so far:
I have the following code which copies and pastes from one row to another
View 3 Replies View RelatedI've got cells which change after I use Solver to do simulations, These cells after the calculation is run I'd lke to paste into a seperate sheet and row for example (in preparating of charting them for example)..
I would like to paste it as folllows for example... .. but problem is, let's say the below list are in column A of the new sheet, than column B would be the first output of the calculation in the other sheet, column C would be the second output from the calculation in the other sheet. and so on..
I don't know the first thing on scripting, but I'd like to click on button where the calculations are made which when clicked on ,will paste to the other sheet in this sort of manner below.
Each calculation will use another column.. so the script must recognise there is other columns to the left already filled, ie. B, C, D, E F, coresponding to the results of calculations already performed.
Gross income
Expenses
Net Tax
I have a sheet named "original" with columns a, b, c. My code checks each cell in column C for a value and then copys columns A,B,C to a sheet named "Order". It works fine except I need it to start pasting the results in row 16 instead of row
Sub PLACEORDER()
Dim lastrow_first As Long
Dim lastrow_second As Long
Dim x As Long
lastrow_first = Sheets("ORIGINAL").Cells(Rows.Count, "C").End(xlUp).Row
For x = 10 To lastrow_first
If Sheets("ORIGINAL").Cells(x, 3) <> "" Or Not IsEmpty(Sheets("ORIGINAL").Cells(x, 3)) Then................
I have a graph, that I would like to be able to copy from one sheet and paste into another, is there away of doing this without dropping the data i.e. every time I copy and paste the graph clears, I don't want to copy the spreadsheet data as well as the graph is purely for a visual
View 3 Replies View RelatedI have attached the file. The row being copied is row "X" for both buttons. I have 2 buttons in the same worksheet to copy a row and insert the copied row below it. I have this macro running for 2 different rows in the worksheet, assigned to the respective 2 buttons. See my code below.
View 4 Replies View RelatedI have a folder with files in it. I want to copy the file names and past them into my Excel spreadsheet. This seems like it should be a simple task.
View 6 Replies View RelatedI have a range of cells I want to copy which has some textboxes and check boxes in it. When I do a copy/insert cells in vba it only copies the cells and their values but not any of the boxes.
.Range(.Cells(Origin.Row + 1, Origin.Column), .Cells(Origin.Row + 12, Origin.Column + 10)).EntireRow.Copy
.Range(.Cells(Ind.Row, 3), .Cells(Ind.Row, 3)).EntireRow.Insert Shift:=xlDown
So I have the code that allows me to search for the a certain value in a cell and then copies a certain cell to the other spread sheets. Here is the code that does that
Code:
Sub SearchMacro()
Dim LR As Long, i As Long
With Sheets("Sheet1")
[Code].....
The code will just need to search for the string "HSFL" and I won't have to hard code all the different variations of HSFL as I mentioned above.