I'm creating a Master document that has static information on one Source page that feeds to other sheets: Source contains name, address, contact, etc., that feeds to all other pages, budget feeds to invoice sheet, which feeds to contract, etc. Is there a way to copy information from one budget sheet and then automatically insert lines in invoice sheet?
View 1 RepliesOur small family business has a mailing campaign that we track with excel. However, we're very concerned about users opening the macro-filled master spreadsheet. Instead, we'd like a macro to do everything for them.
When a letter comes back in the mail with a bad address, the user types the Street number and street name such as "1234 Main St" into $A2 of c:dropbox eturned.xlsm, presses the macro button, and it should do the following:
Opens and Searches "Sheet1", "column S", in the file c:dropboxmaster1.xlsm, and finds the LAST instance of the address typed.Selects that entire rowCopies the entire row.Pastes the contents into the row of the active cell in the destination spreadsheet, overwriting what was there before. Such as $2:$2 if the address was typed in $A2.In the master1.xlsm spreadsheet, sets the entire copied row color to "gray".closes master1.xlsm and saves changes.
The end result is that the user now has an identical row of information in their spreadsheet, and the master spreadsheet's row is colored gray indicating it has been completed.
Other notes:I'm open to more efficient steps than this if you have them.There are approx 5,000 records to search through in master1.xlsx at any given time.
How would you make a macro that would delete the line if a field in that line say for example K1:K100 had "XX" or "XXX" in it?
View 10 Replies View RelatedNeed vba code for excel that will auto add a formatted row when a button is pushed? I have the button made, but I need it to move down a row each time a user clicks the add line button.
View 3 Replies View RelatedOnce a job is complete on a sales tracking sheet I created, it needs to transfer automatically once I click on a "completed" section of my work sheet.
View 1 Replies View RelatedI am trying to take data from specific cells on PBI_DATA_SORT and paste it into a new line in PBI_DATA_SORT_TRACKING with a date and time stamp in separate cells.
I have been able to get it to copy from one to another but not with a date and time stamp.
Code:
Sub Macro3()
'
' Macro3 Macro
'
Sheets("PBI_DATA_SORT").Range("D139:H139,M139").Copy
[Code]....
In this attached example, this is a portion of a actual work procedure. The yellow highlighted cells are the same work element within the master procedure but one is for one type of machine while the other is for same model but added options. This is a pattern throughout the entire worksheet. Basically 1 model with several different types of options. I don't want to have unnecessary elements in a work procedure.
So what I am trying to accomplish, I want to create a userform or dashboard which will allow selection of model #, model type #, then based off those selections have excel populate a worksheet with all correct procedures based off model type choice.
In this example there is 6 model variations, all share same basic procedure, but all have their own work procedure based on options, so my above approach will allow me to make changes to the master procedure only instead of changing 6 procedures.
I want to end up with is when I type text in any row of the Master sheet, I would like the entire contents of each row to automatically be repeated on the worksheet of the same name (as per column O). For example row 5's contents from the Master sheet would duplicate into the sheet named Accommodation. In addition, should the details ever change in the Master, this would need to be reflected in the individual sheets also. My only concern is that I'd like the individual sheets to fill from the top rather than match the exact row number position from the Master, ie row 5's contents to be placed into row 2 of the Accommodation sheet seeing as it's the first occurrence, and row 6's would go into row 3 etc.
View 2 Replies View RelatedI want to end up with is when I type text in any row of the 'Master' sheet, I would like the entire contents of each row to automatically be repeated on the worksheet of the same name (referred to by text entered in column O). For example row 5's contents from the Master sheet would duplicate into the sheet named 'Accommodation' as O5 contains the word Accommodation.
In addition, should the details ever change in the Master, this would also need to be reflected in the individual sheets. My only concern is that I'd like the individual sheets to fill from the top rather than match the exact row number position from the Master, ie if row 5 had the first occurrence of the word Accommodation in column O, the contents of that row would be placed into the next available first blank row of the Accommodation sheet, which in this case would be row 2 as the headings are on row 1.
I'm looking for a formula that will paste an entire row from sheet 2 into sheet 1 of the same workbook when only 1 cell is typed.
I am trying to come up with a property access log for my guards so when they enter a tag number from(sheet 2 column B) into (sheet1 column D) , it will auto fill sheet1 column C and E with the entries from sheet 2 column A and C.
I want to do the following -
Whenever I enter a value say "X" in Sheet1, ColumnA I want the value to be autofilled into sheet 2, ColumnA if and only if value does not exist in sheet2, Column A.
How to achieve this in Excel 2007 ?
i have a sheet called 'sample database'. it consists of 56 columns, each with a specific title in row 1. i.e. name, surname, mobile_number, and so on.
from row 2 onwards the data has been populated for roughly 200 rows.
i have another 'Capture Sheet' which has the same titles as 'Sample database' except it's in a different format. its a printable form that is given to new employees when they start. once they have completed it it gets captured into the 'Sample Database' sheet.
my wifes boss now wants 1 new sheet for each row in the 'sample database' sheet based on the format/layout of the 'capture form'.
how can i get the 'capture form' to auto populate the data from the 'sample database' sheet and create a new sheet for each row?
i know this is possible i just dont know how to do it.
I am trying to auto populate one spreadsheet from a master sheet
What I am trying to do: I have a master sheet where information on 25 countries is entered. Sheet has multiple columns for data entry I have 25 additional identical spreadsheets that correspond to one of the 25 countries so can drill down to country specifics
From the master - is it possible to select a country from a drop down menu so when I start entering data on the master it auto populates the corresponding sheet by the country selected.
I have a master list. I want to create an excel sheet that will follows when I key in the code number according to the master list all the rest of the value in the master list will automatically populate accordingly into their respective sequence into the created excel sheet.
Eg.
Master list
A B C D
0001 Lucy 8 Walton primary
0002 Rachel 9 Dermouth primary
The created excel sheet
Code 0001
Name Lucy
Age 8
School Walton Primary
I want to key in the code 0001 and the rest of the value will auto populate from the master list into the created excel sheet. The master list data go in accordance row by row. That means the master list is listed in a sequence where all the related value for the particular code into one row only.
I am trying to create a transfer of data from one worksheet to another, where Column G is where new data will be entered. I have a macro to sort the data (works great!!), but it is just getting it from one worksheet to another without having formulas in the cells - which can be deleted by my co-workers - to pull the data from one sheet to another.
I do not know if this can be done automatically or not, or if info is updated on one worksheet vs another - if then it will transfer the text.
Example:
(A2)LAST NAME (B2)1ST NAME (C2)RES# (D2)HOUSE (E2)DOB (F2)JOB (G2)SPECIAL
(A3)DOE (B3)JOHN (C3)112233 (D3)1 A2 L (E3)10/10/75 (F3)YES (G3)DR APPT
There are over 300 entries - one row new record. The CommandButton1 works great that I have for compiling and deleting unused rows.
But now I need something to get the data to the blank worksheet to compile data from Column G without formulas in the cells. This was one I found online, tried to adjust it to fit what I need, but I cannot get it to work.
Sub CopyRow(ByRef Target As Range)
Dim DstEnd As Range
Dim DstRng As Range
[Code].....
This should be a simple one, But I can get it to work. Could someone advise me as to how I can change this line of code to pick sheet(2) as it is now it picks sheet(1) . and throws the formula off. In A nut shell the formula is looking at sheets(1) C3 When it needs to look at sheets(2) C3. I always get messed up with the & and "" and!
View 3 Replies View RelatedIm looking to use this code to move the selected rows of a workbook over to a new worksheet in the sameworkbook. It works fine however for some reason it pastes the row over the last row.
how to modify it so it pastes the row on the first empty row?
Sub MoveSelectedRows()
Application.ScreenUpdating = False
Dim strSheetName, strCellAddress As String
strSheetName = ActiveSheet.Name
strCellAddress = ActiveCell.Address(False, False)
Rows(ActiveCell.Row).Cut
Sheets("Closed Projects").Select 'Change sheet name to whatever consolidated tab name is.
Range("A" & Range("A65536").End(xlUp).Row).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("A" & ActiveCell.Row).Select
Sheets(strSheetName).Select
Range(strCellAddress).Select
Rows(ActiveCell.Row).Delete
Application.ScreenUpdating = True
End Sub
Also, as a side note, how do I put a prompt in so that once I press the button to move the row the user is prompted asking if they are sure they want to move the row? 'Are you sure you wish to move row X?' Yes/No.
If yes - run rest of script
If no - abort script
I have 4 sheets called Data, xxx, www and yyy. The sheet names xxx, www and yyy are also words that are existing in the sheet Data in Row C. Am I able to copy paste all the lines existing in the sheet Data including i.e. xxx in Row C into the sheet xxx? This should also work for the words www and yyy.
View 9 Replies View RelatedI am trying to copy multiple rows (ie: rows that have data in them) over to another sheet on the next available line.
I have the code to go through and filter the data that I want to use. Now I have anywhere from 1-50 rows of data all sequentially together that I need to either copy or move to another sheet ("Data") on the next available line.
I have this code so far:
Worksheets("To Be Worked").Range("B5:K5").Value = Worksheets("Data").Range("A1").End(xlDown).Offset(1, 0)
But this only includes 1 row so I either have to loop the VBA or expand the range to include multiple rows. Both of which I don't know how to do. And that code doesn't work too well either.
How can I get this code to loop through every line on sheet?
Code:
Selection.Copy
Windows("SheetA.xlsm").Activate
Cells.Find(What:="Part Number", After:=ActiveCell, LookIn:=xlFormulas _
, LookAt:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, _
MatchCase:=False, SearchFormat:=False).Activate
[Code] ..........
I have to do a large amount of routine work with a large number of sheets. I have tried to record some macros to do the job.
First of all I would like to insert 2 lines under line 6 in the active sheet.
I then need to drag the formulas found in line 6 down over the two new lines.
Thirdly i would like to change the text string "xxx" in the formulas found in line 7 to "yyy"
I tried to make something to find a certain value (from a userform) in a range, and then copy the line partially to another sheet. Then go forth to the next found item and do the same. But... I cannot seem to make this loop.
With Sheets("Data input").Range("N5:N1100")
Dim FoundRange As Range
Worksheets("Data input").Activate
[Code].....
1- code that will use to show position (1st, 2nd, 3rd....) based on two criterion in the attached sheet
A-the Student must has 49.5%
B-the student must get a "PASS" remark
2- How can i auto sort the position in descending order in another spread sheet.
I have a summary sheet with a numerous data sheets to the left of it in a single workbook. Every time I add a new data sheet I have to type it's name on the summary sheet. How can I automate this?
View 9 Replies View RelatedI want to create a dynamic line graph using week and year numbers stated in another sheet.
e.g.
Start Year - 2012
End Year - 2014
Start Week - 3
End Week - 12
The top 2 rows above my graph data are as below:
Year - 2012 2012 2012
Week - 5 6 7 etc.
This works fine if the start and end year are the same but if it's greater than one year, it doesn't recognise that.
I have the data to import / read in a pdf, in a doc, or in an Excel worksheet whichever is easier to use. I need to import the data, parse it into the correct cells for that row and then repeat the import until the end of the file. Not all the cells are in each group of data to import, so those cells will be null for that row. Some of the data for one cell may be in up to 14 lines in the data file. I have be concatenating these data rows into one cell. There are 48,000 lines in the file to import or I would do this manually. I am assuming that doing this in VBA would be the most efficient method.
View 11 Replies View RelatedI have about 180 workbooks which I need to compile into a Master Schedule.
All the tab 1's are different, these feed into tab 2, which the data has the same formatting throughout. The 2nd tab has the same data for A:F 1 but cells A:2 - F:2 down to row 9 are populated from tab 1, therefore different in each. I am trying to get a Master schedule that lifts the data in the fed cells into a line by line spreadsheet?
Is there a way I can get excel to look at a folder, then every workbook in it, the at the 2nd tab in every workbook, then list the cells as described above? I am not after a consolidation of this data, but a full list?
I'm trying to combine 2 cells that both have text on multiple lines separated by line breaks. I want to keep the formatting so that when both cells are combined, line one from cell 1 and line 1 from cell 2 are on the same line in the new combined cell. This is easy to do when there is only 1 line in each cell but how do you do this for cells with multiple lines? Here is an example of what I am trying to do:
A
B
C
1
First Name:
Last Name:
Address:
John
Smith
123 Harold Street
First Name: John
Last Name: Smith
Address: 123 Harold Street
I need to read or clear all the line in specific range which will be increasing dynamically. Problem is "Selected File List" table range is not specified. New file chosen with browse will be added to "Selected File List" dynamically. Number of file can't be predicted.
vbaStk.JPG
What I've tried so far is, keep track of the browse button click and add the file path to arraylist. After that, (Row number 9 which is start line + arraylist size) to get the number of line of end of the table. But due to some requirement, I want to read the file from excel file. Something like -Read until found blank line or border bottom or something.
I'm new to VBA and I'm not so sure what I'm doing with vba codes
I have this function that works on line 3 and if the conditions are met, the result is 1
=SUMPRODUCT(--(IfColor(B3,$A$76)*(SUMPRODUCT(--(D3D4)))))
Here how it works, if B3 is the same color as the reference cell $A$76 and D3 is different than D4 then the result is 1
I would like this function to work from line 3 to line 60 and return the total of lines where the conditions are met. I'm thinking of a =COUNTIF function but can't get something to work. If there is a simpler way, it's even better. The IfColor is a function I wrote in VBA,