I have to copy and paste each group of "Control Accounts" from column A into a separate workbook. I want to create a macro that can open as many new workbooks as there are unique records in column A and copy paste each group of unique records into those different workbooks.
View 7 Repliesthis code "Creates a New Workbook" for each item listed in an excel table, then, copy all unique values in their respective Workbook and save it. So, the problem is this. instead of creating a "New Workbook", i want to open a previously created Workbook (template) and do the procedures listed above. Is this posible?
Sub Copy_To_Workbooks()
Dim CalcMode As Long
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim WSNew As Worksheet
Dim rng As Range
Dim cell As Range
Dim Lrow As Long
Dim foldername As String
Dim MyPath As String
Dim FieldNum As Integer
Dim FileExtStr As String
Dim FileFormatNum As Long
Set ws1 = Sheets("Sheet1") '<<< Change
If Val(Application.Version) < 12 Then
'You use Excel 97-2003
FileExtStr = ".xls": FileFormatNum = -4143
Else
If ws1.Parent.FileFormat = 56 Then
FileExtStr = ".xls": FileFormatNum = 56
Else
FileExtStr = ".xlsx": FileFormatNum = 51
End If
End If..............
I have a test sheet thus :
+ABCDEFG
1Bob11234
2Sally2
3Ann3
4Emily4
5Lexi3
6Bill2
7Josh1
8Jacob2
9Mary3
10Gary4
11
In D2:D11, I want a non-duplicate list of all the "1" names, i.e., Bob and Josh. In E2:E11, a list of all the "2" names (Sally, Bill & Jacob), and so on. Sorted would be nice, but not necessary. When new names and their numbers are added to A1:B200, they need to automatically show up in D2:G:201 as appropriate. I've looked over VLOOKUP, INDEX, MATCH, etc., but all seem to simply return the first and not every match. Maybe I'm just missing it.
Is this possible without VBA? Not that I have any objection to it, but delving into and learning it is a little outside the available time I have now. If it takes VBA I'll address it when I can free up some time.
I want to end up with a list of unique items (nothing repeated) from a variable length list starting in cell B5. That unique list is then placed in cell D3.
I am using the code below (found on Ozgrid) which almost works. But it seems to be dependent on what’s in column A (which I wish to ignore) for it to work.
How can I modify the code so that the results are based on column B only?
Dim Rng As Range
Dim fRng As Range
Dim lRow As Long
Dim Dest As Range
lRow = Range("B" & Rows.Count).End(xlUp).Row
Set Rng = Range("A5:B" & lRow)
Set Dest = [D3]
With Rng
. AutoFilter Field:=1, Criteria1:="1*"
Set fRng = .Offset(1, 1).Resize(.Rows.Count - 1, 1).SpecialCells(xlCellTypeVisible)
With fRng
.AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Dest, Unique:=True
End With
End With
I have a workbook with about 10000 rows of data for about 100 suppliers in Sheet1 and about 15000 rows of same Suppliers payment details in sheet2.
What I am doing now is:-Filter every supplier names in Column A of sheet1, copy all the rows and paste in sheet1 of a new workook & Again copy the Payment details of a same supplier from master file sheet2 and paste the same in sheet2 of this new workbook then save the files with the supplier name as file name in my documents folder code, that filters each suppliers data from sheet1 & 2 and paste in new workbook sheet1 & 2.
I am trying to seperate singlr column information into seperate column based on the name in the Data 1 column.
Ex.
Data1 Data2 Result 1 Result 2 Result 3
one100one100two200three1
one350one350two450three2
one500one500two600three4
two200
two450
two600
three165
three236
three450
I would like to know how to combine multiple cell value from each column to create a unique value. for example column A will have values (a,a,b,c,d,e,e,f,i,j) and B will have (1,2,1,1,3,4,5,5,6,7) and C has ( xxx,yyy,zzz,xxx,yyy,zzz,xxx,xxx,yyy,zzz).
i need to create a list of unique possible combination of data into column D.
I have placed below example and how to create a similar code and what functionality does this.
A
A Code
B
B Code
C
C Code
D
D Code
[code]...
i'm creating a spreadsheet with records of repair jobs on vehicles that my company is/has performed. the problem is that, the way our system works, there are currently no unique identifiers for jobs, meaning i cannot create a master list with various vlookup tables with only the information relevant for our engineers on them.
is it possible to create a macro or use some other system, that will create a new, unique code when a line/cell is filled in? because we end up sorting the list by different criteria on a regular basis (to find specific jobs) the system can't be relative (i.e. +1 to the code above).
I have a combobox on a Userform, when the userform initializes i want it to populate Combobox1 with all the Unique values from SHeet 1 Column A (variable length to the column)
I dont know how to select a unique record only.
*thinking a bit further ahead i have a second combobox that i need to populate with unique values from column B on sheet 1, where in column A matches what was selected in combobox 1
I am trying to filter a list where the number of occurences of unique cost codes for each emp No is more than 1. What is needed is the emp no's who have only one costcode to have '0' in the 'no of codes' column. If more costcodes per empno, I need the first costcode to have 1, second code 2, etc in 'no of codes' column. The idea is at the end to be able to filter and delete all those with '0' in, as I only need to work with emp no's that have more than 1 'cost code'. See attached file. The records are in col A, the costcodes assigned to them in B, the formula in C, at the moment is:
=IF(AND( COUNTIF($A$1:A2,A2)>1, COUNTIF(B1:B2,B2)>1),C1, IF(AND(COUNTIF($A$1:A2,A2)>1, COUNTIF(B1:B2,B2)<2),(C1+1),1))
I have a spreadsheet will a large amount of invoice numbers, some of which are multiple occurrences of the same number. I need to count the duplicates as one unique record and sum but I need to sum the total $ amount of each amount attached to each occurrence.
Please look at the sample to see what I mean.
W234678 has three amounts that are added to give a total amount for that number and it is added to the count as one record.
I had this code kindly borrowed from someone else which helped me find the duplicates but it is not meeting my needs.
Dim rCell As Range, rRng As Range, vKey, lrow As Long
Set rRng = Range("F2:F199")
With CreateObject("Scripting.dictionary")
.comparemode = vbTextCompare
I would like to combine values from multiple records into a single record using a unique identifier. In the example below 'ID' is the unique identifier.
For example:
I have a table where I have dropdown menu for selecting data and a vlookup for filling other fields.However, I would like to create totals at the bottom of the sheet. But I dont know how many rows I will need because the data to this sheet are added from another sheet in the workbook. I would like to create something like this :
No. Name Weight Amount Total Weight Unit Price Total Price
1. Product 30 2 60 100 200
then I will not have no. 2 , but add new record button that will create a new row and will copy the dropdown menu and the Vlookup function so the format will be the same for product no.2 as it is for the product no.1 ... thus I will not have any unfilled rows in the table and after the last row there will be a totals row that will sum up the whole table... How to do that "add new record button" so that in the table will show up only those rows, that are actually filled with data?
I was going through a thread that is open now about a similar subject: Create Workbooks & Worksheets For Each Group In Table. I tried getting it to work myself but was unsuccessful. I want a code that will create a new workbook for every new Vendor. I tried doing it myself but was getting compile errors... If this is not what I should be using let me know, it works the way I want with just worksheets, i thought it would be an easy conversion to workbooks
Sub PagesByDescription()
Dim rRange As Range, rCell As Range
Dim wSheet As Worksheet
Dim wSheetStart As Worksheet
Dim strText As String
Set wSheetStart = ActiveSheet
wSheetStart.AutoFilterMode = False
'Set a range variable to the correct item column
Set rRange = Range("A1", Range("A65536").End(xlUp))
'Delete any sheet called "UniqueList"
'Turn off run time errors & delete alert
On Error Resume Next
Application.DisplayAlerts = False
Worksheets("UniqueList").Delete
'Add a sheet called "UniqueList"
Worksheets.Add().Name = "UniqueList".....................
How do we merge changes that were made by two developers, back into one spread sheet? We have a massive spread sheet with 30 work sheets and each work sheet has 5,000 rows. We are in the process of applying budget taxation changes and have two actuarial developers who are updating the same spread sheet. Unfortunately we cannot even separate the work sheets that they touch, as the tax implications flows through most sheets. This means one tax change can impact 10+ work sheets and as much as 150 different rows. In some cases it is only a formula change, but mostly it is also adding new rows and or removing existing rows. Is there any way that we can use a tool to merge these two developers changes into 1 spread sheet?
View 3 Replies View RelatedI'm having trouble while trying to use a variable in a cell reference. I have a sheet that is being used to record the time and date of data entered into the workbook. This sheet is divided into columns, each designated to a specific type of data. I have written a macro to enter the current time in the correct column, but that macro is embarrassingly long and complicated because I used a series of if statements to handle all the different data being entered. I'm trying to simplify by using vlookup, and assigning the column value to a variable, and inserting that variable into the cell reference.
Here is the line I need to insert a variable into:
Sheets("Current").Cells(Rows.Count, columnstart).End(xlUp).Offset(1, 0).Value = Now()
Where columnstart is my variable. It is an integer, but it's value will change depending on the type of data being entered.
i have an excel database regarding about 1000 different schemes having the details of their initiation date, budget allocated , up to date expenditure, this month expenditure and many others too. this is a shared workbook and relevant officers enter their data in their relevant columns and rows. the file is shared. now i want that any changes done by mr. A, for eg. at record No. 09 could be intimated to Mr. B who is working at record No. 560. may be via msg box or any other source.
View 1 Replies View RelatedHow is code or functions kept unique to a workbook? for instance i create a few toolbars that are relevant to "work book 1", however when i open another workbook "2" the toolbars do not function properly or are removed by the opening/closing of the work book, similarly other macros seem to struggle with more than one workbbok open at a time.
View 5 Replies View RelatedI seek advice on using the value of NOW() as a record ID in an address book program. Question #1: Do Excel developers often use a record ID? Question #2: What record ID schemes are fequently employed besides date/time? I have decided to create an Excel address book as an exercise to increase my knowledge of VBA, and also as a useful application for work.
I realize that a record ID is not essential in Excel in the way that it is essential in Access, but I feel the need to have some unique ID associated with each address, so that I may have different worksheets, with data related to a given Contact, sort and manipulate it, if necessary, but have the record ID as a way to restore the relationship of rows to a given Contact, and also, as a handy way to examine the data in the date/time sequence in which it was entered. I have experimented with the following code, to assure myself that I can access the number returned by the NOW() function, manipulate it as a string, and format in various ways if necessary.
Dim n As Double
n = Now()
sn = Str(n)
p = InStr(sn, ".")
first = Left(sn, (p - 1))
l = Len(sn)
d = l - p
S = Mid(sn, (p + 1), d)....................
I am creating a worksheet in excel 2003. Want to use this for attendance log.
As we input numercial data in column B, we would like in column I to create a formula that automatically inputs the actual time that info was entered in column B.
I am to transpose a column to the next sheet as and when I add a new record at the end of the Column. I have only one Column A.
View 9 Replies View RelatedThe code below pops up two input boxes asking the user to enter the location number and the vendor number, and then saves the worksheet as a text file to the desktop based on their input:
View 5 Replies View RelatedUsing a formula,
How can i get excel to create a custom unique number using the first three (or two) characters from another field and then adding four unique numbers to the characters.
basically heres the info
Field H2 contains the characters "ACS" so i would want another field to create a unique code to include something like "ACS1234" and to continue to the next row would be the first three characters of field H3 then 1235.
I have a list of references and I need to pull from a database. The database contains more than one row with my reference, so I try doing something like this:
Sub ()
Range("B11").Select
Do Until IsEmpty(ActiveCell())
If ActiveCell = _
"7501" Or _
"7507" Or _
"7508" Or _
"7509" Or _
"7618" Then
But it does not work, besides I need to add more references. Could some one know a better way to do this?
I am creating an asset management sheet. For the formula I am trying to work out there uses 3 fields : ID, start date, and end date.
What I want to do is be able to show if the ID is duplicated within another record with an overlapping date. So an item is flagged if it is in the list within the same dates as another record. I tried a few countif formulas but with no success.. I may just be approaching the problem incorrectly though.
I have some raw data of employment records on one workbook and from this i need to autopopulate there information into another workbook.
I need to split the records up by the different departments they work . I.e so what i am after if possible is -
If The department name in the raw data matches column A1 for example then it auto populates down in B2 all the names of people who work in that department .
I have a Column, say column A, setup with a lot of repeated data. Now what I want is to pick only a single value from each set of data and put them into a separate column. for example:
Col A
1
2
3
2
4
1
5
4
1s
a2
1s
a2
a2
6
What I want the excel to do is to pick from the above data only a unique value and put them into a separate col, like this:
Col C
1
2
3
4
5
1s
a2
6
I have a master sheet with allot of columns. I want to be able to cut the data in seperate tabs.
I would like to have a formula to create a unique IDs to easily pull the information I need. If column Type and Vendor are the same I want the number in the Unique ID to repeat. The info on the master sheet can be sorted at any time.
Unique Id Type Vendor
Commission-1 Commission Microsoft
Commission-2 Commission Google
Commission-1 Commission Microsoft
Commission-3 Commission Netflix
Commission-2 Commission Google
In column A I have values starting at row 4, ranging up to row 1004.
in coulumn Q (starting at row 3) I'd like a list of unique entries from column A
I know this line is my problem
If Range("A" & I) Range("Q4:Q30") Then
but i'm not sure how to compare against all values in a range
Sub FilterSymbol()
Dim I As Integer
Dim X As Integer
X = Range("O2")
I = 4
Do
If Range("A" & I) Range("Q4:Q30") Then
Range("Q" & I) = Range("A" & I)
I = I + 1
Else: I = I + 1
End If
Loop Until I >= X
End Sub
I need to create 50-60 bingo cards for my sister's bridal shower. I wanted to create these using excel. I need to create unique cards so that not everyone bingos at the same time (I only have so many door prizes to give away!)
The basic setup is this: under "B" there are 5 spaces, and each space must have a unique number between 1 and 15 (no repeats). Under "I" there are 5 spaces, and must use a unique number between 16 and 30, "N" has 5 spaces using a unique number between 31 and 45, "G" has 5 spaces using a unique number between 46 and 60, and "O" has 5 spaces using a unique number between 61 and 75. A sample card is below:
BINGO
1416375862
217455773
830366066
1122394761
1223435668
I have tried to use the "randbetween" function, but this creates repeats and I would have to go in and manually change the numbers that repeat.