I am looking for VBA code to help with a tracking spreadsheet I have created.
I'm still a beginner in VBA, but I know what I need it to do, just not how to execute the command. I believe it's something that would be simple for someone more familiar with VBA and Excel.
What I would like is when a Completed Date is entered in column J on Worksheet 1 (named Work in Progress), that row entire row of information would automatically be cut and pasted into Worksheet 2 (named Work Complete).
Sounds simple enough right? lol But I can't find code online that would do this and the edits I've tried to any code close to what I'd want do not work.
I am copying data from worksheet "Microsoft" to another workbook and paste in sheet1, i want the cell G1 to auto input the worksheet name "Microsoft" where i copy the data from,
How to have G1 show the worksheet name after i copy and paste the data from worksheet name "Microsoft".
I'm having an awful time specifying the sheet I want to: a) derive the data from b) put the processed data into
Suddenly (never had this problem before) Excel keeps switching the definition of Application.Sheets(1) and Application.Sheets(2) so I have no way of controlling where the data is going.
How do people usually specify the sheets they want? Obviously my method is unreliable, but I'm not sure which one I should use.
I need to to use VBA to copy a worksheet (which i've managed to do!), but I need the new worksheet to have a Worksheet_Change event. Now when I copy the worksheet, the event doesn't copy over (obviously as its a cut and paste jobby). Any ideas on what code I need to add in the Worksheet_Change event just after the new worksheet is automatically created?
When the command button is clicked, the code runs, and leaves me on the "summary" sheet, but I would like the code to take me back to the original sheet where the command button is: "File Preparations".
I created a outstanding task worksheet in excel and I would like to move the completed jobs from this sheet to the second worksheet titled 'Completed Tasks'. The first worksheet is called 'Outstanding Tasks'.
I came up with a code to do this (see below). As you can see when I type "Y" into column G it moves the row into the second worksheet. It does this; however it puts it to the bottom of the table on the second worksheet where I would like it to be at the top. I also would like it to delete the row once it has moved it does this but then deletes the other row of information above it leaving blank rows on either side.
I simply want a code that moves the row of information to the second worksheet when I type Y into column G and then delete the row without messing with other information around it.
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range) Dim rng As Range Set rng = Target.Parent.Range("G5:G1000") If Target.Count > 1 Then Exit Sub If Intersect(Target, rng) Is Nothing Then Exit Sub Select Case Target.Text Case "Y" Target.EntireRow.Cut Sheets("Completed Tasks").Cells(Rows.Count, "A").End(xlUp).Offset(1) Selection.ListObject.ListRows(1).Delete End Select End Sub
I am trying to find a way to protect and close column groupings of a number of worksheets on workbook_open procedure by looping the worksheet codenames instead of just the worksheet names in order to prevent potential problems with renaming the sheets.
This code didn't work...
Code: For i = 6 To 25 With ThisWorkbook.VBProject.vbcomponents("Sheet" & i) .Protect "rbse" .Outline.ShowLevels columnlevels:=1 End With Next i
I have seen many examples posted here that are close to what I need, but I am not experienced at writing code, so I am not sure how to make the changes to this code that apply to what I need. So I will try to explain what I am looking for & hopefully not be too long winded:
I have an excel workbook that has several worksheets within it - 10 of the worksheets are identical as far as the formulas that are in each of the cells, however, they are all VLOOKUP cells that refer to another excel workbook (used as a "database")which lists all of our projects - there is a cell in each of the 10 sheets that can be changed that will allow that particular worksheet to access the information in the "database worksheet" for the particular project name that is entered in this cell.
There is currently an event worksheet code for each of these worksheets which allows for a picture to be displayed "floating" above cell (L13) based on the project name that is showing in this cell- however it is based on all of the project pictures "living" in each worksheet (the picture that is called up by the project name is displayed while the rest are hidden - as per the formula)- the code is shown below: Option Explicit
Private Sub Worksheet_Calculate() Dim oPic As Picture Me.Pictures.Visible = False With Range("L13") For Each oPic In Me.Pictures If oPic.Name = .Text Then oPic.Visible = True oPic.Top = .Top oPic.Left = .Left Exit For End If Next oPic End With End Sub
However - this option works fine when there are 5 or 10 pictures/projects - but we are looking to grow our project database. So, I was hoping to be able to store the pictures in another location (such as another worksheet or in a file on the server - I would also appreciate input if anyone has an opinion on which would work better?) and have some type of worksheet event code that can be written in to each worksheet that would access the picture in this "central" location and have them appear in cell (L13) of each worksheet based on the project name displayed.
Basically, I have a worksheet of client info on a row by row basis.
Each client has a ref number (eg A4 is the ref for the first client. A5 the next, A6 the next etc etc)
I would like to know how to copy and paste each ref number into worksheet 'Proforma Template (2)' every 11 rows down, until the end of the client list is reached.
So, copy/paste value of cell A4 into 'Proforma Template (2)' cell E9, then A5 to 'Proforma Template (2)' E20 etc (every 11 rows)
(I've read up about various types of loops but not sure where to go with it)
Once I have this on a macro, I'll use vlookup to take the rest of the data across. (im more familiar with using vlookup so should be ok there)
Currentley I have a worksheet(2)containing data updated from another woorksheet(1). I have a command button which runs some code for a filter ( recorded macro). can I run this code every time the worksheet(2) is activated instead of using the command button?
i am trying to make a VBA that will find a certain value (BELCS), select the value, and copy it to another worksheet. this is what i have so far:
For Each BELCS In ActiveSheet. Range("B2:B" & ActiveSheet.Range("B51161").End(xlUp).Row) If BELCS.Value = "BELCS" Then BELCS.Select Selection.Copy Sheets("BELCS").Select ActiveSheet.Paste End If Next BELCS
i am trying to get it to select the whole row when it finds that value but when it runs it says that i can't make a selection of the BELCS,
I currently download a report and it has a different worksheet name everytime. Is there any way using a macro to change the worksheet name? (My macro extracts data from worksheet a and copies into worksheet b) Hope I've provided enough info.
Is it possible to copy a "Worksheet_change event" macro to a new worksheet by macro? Like when I insert a new worksheet, a certain macro, for example "run macro on data entry", to be already written in its worksheet_change event.
For Each kopSheet In ThisWorkbook.Worksheets If Left(kopSheet. Name, 8) = "Inddata-" Then kopSheet.Copy ActiveWorkbook. SaveAs ThisWorkbook.Path & "" & kopSheet.Name ThisWorkbook.VBProject.VBComponents("Kopier1").Export filNavn Workbooks(kopSheet.Name & ".xls").VBProject.VBComponents. Import filNavn ActiveWorkbook.Close End If Next kopSheet
I copy 6 sheets, named "Inddata-*", and i wan't to copy a module named "Kopier1" with it. I know that i can use an add-in, but that is unfortunately not a good idea in this project. It does export the module "Kopier1", but, it doesn't import it to the newly created workbook!
I need a VBA code to reference the cell value to activate a particular worksheet name. In the sheet "Ini", Range ("B4"), there is a text of "Rawdata" This text is used to reference to the specific Sheet name and activate it.
However, I am unable to get my code to work. Thus, I changed it with a ' with green highlights for the following lines:
I'm using the following VBA code that highlights a selected cell or range of cells when selected (to more easily see what cells I've selected).
[Code] .......
I'd like to use this code on every worksheet within every workbook that I open. Right now I've only inserted the code in "This Worksheet" in a single workbook and that's the only place it works. I tried creating an Add In with the code so that it worked in all workbooks, but it doesn't work.
I have two workbooks: Workbook1 has two sheets – Cash Register and Suppliers; and Workbook2 has one sheet – Clients. The users of Workbook2 do not have access to Workbook1, and information entered in Workbook2 is required (and has to be manually re-entered) in Workbook1.
I tried IFs and VLOOKUPs to pull the data, but due to the large number of cells, the workbook is awfully slow – taking a very long time to save (even with Automatic Calculation/Recalculate before save turned off).
I am using Excel 2007.
This is what I am trying to achieve:
When a user enters an ID number, which begins with either “S” (e.g., SB-00010) or “P” (e.g., PADA-012-034-0567), in column E of Cash Register:
(1)
If that ID number begins with “S”, I want the code to look up that ID number in column A of Suppliers and copy the corresponding cell from each of the following columns:
I am trying to copy worksheet between 1 workbook to another. But even the worksheet was successfully copied but the code inside wasn't. Do I need to copy the VBA module manually (create new proc/sub) ? Or there is a setting during Worksheet copy process?
I need a code that will allow me to move data from a worksheet to a worksheet from which I came from. e.g. worksheet "Sheet 6" opens "Sheet 10", then after filling data in "Sheet 10" I need to move this data back to "Sheet 6" in the cells in range "F12 to F56". Please note that the data in "Sheet 10" is in the same range as in "Sheet 6"
Code: Sub EraseWorksheet() ' ****THIS ERASES ALL THE ITEMS FROM THE ESTIMATING SHEET**** ' IT ADDS A 1 VALUE TO ALL THE ITEMS Except, Floor Type, Prep, and Removal With ActiveSheet .Unprotect On Error Resume Next With .Range("B4:B202").SpecialCells(xlCellTypeConstants)
