I want a macro that if a certain cell on a row in column F on my worksheet is "6" then copy the cell in columns B and H on the same row are to be copied into an array and then when the loop is done, display the copied cells in a msgbox.
Is there a simpler way to do this without fidgeting with an array?
I am attempting in the code below to copy all the worksheets from a specific folder into an array (for later manipulation), not to a single worksheet, The files open correctly, but the reading of the worksheets into the array is my downfall....
Sub FindOpenFiles2()
Dim FSO As Scripting.FileSystemObject, folder As Scripting.folder, file As Scripting.file
Dim directory As String
Dim wksht As Worksheet, i As Long, wkshtnames() As Variant
Dim wbNew As Workbook
directory = "C:Users"
Set FSO = CreateObject("Scripting.FileSystemObject")
Set folder = FSO.GetFolder(directory)
For Each file In folder.Files
Workbooks.Open file
Next file
For Each wksht In ActiveWorkbook.Worksheets
i = 0
i = i + 1
ReDim Preserve wkshtnames(1 To i)
wkshtnames(i) = wksht.Name
Next wksht
I have a protected template and unprotected source worksheets - - - what I would like to happen is for the macro to start and if the source worksheet cell B3 equals "Report Total" then stop - otherwise copy template worksheet then copy 6 specific cells from the source to paste values to specific cells on the newly created worksheet (B_ to C7, D_ to I7, E_ to C9, F_ to K9, A_ to C11, M_ to K11 and then K13=F13-30)
After that then start all over again unless the next row’s cell (B4, B5, B6, . . .) is "Report Total" then stop - - - the row count could be from one to a couple hundred.
Here is what I have so far but I know that with each copy the name will change and as it goes down the source file each row will change and I also need help with that.
Sheets("ee template").Copy After:=Sheets(3)
ActiveSheet.Unprotect
Selection.ClearContents
Range("I7").Select
Selection.ClearContents
Range("C7").Select
Sheets("source").Select
ActiveCell.Offset(0, -11).Range("A1").Select
I am trying to use the below code to fill a combobox with unique values. It works great except I can't figure out a way to replace BLOCK 1 with BLOCK 2. I want to use BLOCK 2 to populate mgNames so that only specific values are filled into mgNames. Currently BLOCK 1 just fills mgNames with an entire range without any conditions. how I can get BLOCK 2 to work in the way i'm intending it to?
Code:
Sub findNames()
Dim mgNames As Variant
Dim myCollection As New Collection
[Code]....
I have created one-dimensional array and now I try to copy the data from array to some specific Cells in worksheet. But it seems impossible all the time!
Sheet2.Cells(56, 3 + m) = LossLocationInt(m)
NB: LossLocationInt(m) is an array containing value in it. But, this doesn't copy to the Sheet2.Cells(56,3+m). For clearness: the array has type variant
I need the macro to look at cells B9:B84 on the Sheet1 tab of the Cost Template. If it finds an x I need it to copy the 3 cells to the right of the x and paste them in a template. For example if it sees an x in cell B9 it would copy cells C9, D9 and E9, open the Purchase Order to the Detail tab, then paste it to cells B3, C3 and D3. It would continue looking for an x down to B84. So if it found 5 cells with x, it would give me 5 instances of the Purchase Order with 3 cells pasted into each.
I've attached my Cost Template and the Purchase Order it needs to copy to. In the Cost Template is a macro called Create_PO. This is what I was trying to alter to make this happen. I can't seem to get it right! FYI in case it matters, I had to change the Cost Template from .xltm to .xlsm in order to upload it on this site.
I'd like to search for a specific value like (1) in an array of cells like (A:A). The result needs to be the average number of a different column (B:B) in the same row as the searched value (1). And the resulting number should be rounded up to the nearest 10.
Example below: (Find value = 1 in A:A) ... 1 is found 4 times.
A | B
1 | 295
3 | 123
1 | 400
5 | 425
1 | 354
1 | 400
In the example above there are 4 (1's) found. By adding all the values in column (B) of the same row, we get 295+400+354+400 = 1449, dividing that in 4, the average is 362.25, rounding that up to the nearest 10... THE RESULT I'M LOOKING FOR IS: 370
{=AVERAGE(IF(('Sales Reports'!$A$1:$A$5000=$A3)*('Sales Reports'!$C$1:$C$5000=$C$1),'Break Reports'!$D$1:$F$5000))}
and am copying it to other cells using this
Sub copyformula()
With Sheets("Sheet2")
.Range("B3:B5000").Formula = .Range("B3").Formula
End With
End Sub
It does the copy but removes the { and } thus rendering the formula useless to me. How can I accomplish this task and keep the formula as an array formula?
I tried to drag the formula down and recorded it as a macro but when it runs it takes far to long.
I'm creating a simple program that copy one or more specific cell values and place it on a specific cell in another sheet using loop to make it easier... I'm having a difficult time trying to figure it out..
Example
from sheet1 A1:A5
Sheet1
A | B | C
1 P45
2 P46
3 P47
4 P48
5 P49
and place P45, P47 and P48 on another cell, to be specific in C1,C2 and C3, in a different sheet
Sheet2
A | B | C
1 P45
2 P47
3 P48
4
I'm trying to copy entire row from sheet "source" to sheet "output".
Condition: If cell or cells in range (E7: lastcoll, lastrow) value is "A" then copy entire row.
Find the excel template in attachment.
My problem is that my macro is copying particular row, as many times as many "A" finds.
I want to copy entire row just once doesn't matter how many cells with "A" are in particular row.
VB:
'function to find last column a change letter of column to number
Private Function ColLetter(LastCol)
ColLetter = Split(Cells(1, LastCol).Address, "$")(1)
End Function
[Code] .....
copyROW.xlsm
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
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
copy specific cells from Sheet2 to Sheet1. How would I change this macro if I wanted to copy all of Column B from Sheet2 to Sheet1
View 2 Replies View RelatedHow to copy the content of cells from an excell->sheet1 to an string array
View 2 Replies View RelatedI want some VBA code that will:
1) automatically check the active row to see if it contains specific text (i dont' care if there is other stuff in the cell or cells), such as "truck 1 blahblahblah" and checks for specific font color, such as green.
2) if the row does, then I want it to copy ALL the cells in the active row EXCEPT Column A (which contains the date), and then paste them twice, one 21 rows down from the active row, and one 35 rows down from the active row.
3) Once copied and pasted, modify the pasted cells slightly. The first pasted cells need to say Truck 2 blahblahblah in Column C and be in blue font (instead of the original Truck 1 blahblahblah in Green Font), the second needs to say Truck 3 blahblahblah and be in yellow font. Everything else that was pasted will be the same EXCEPT they will be in blue or yellow font.
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 RelatedI am seeking a formula which can be “dragged” which will copy certain cells upon a row onto specific cells on another tab, when i drag at present it skips rows from tab 1?
Eg;
Tab1 B12 to Tab2 C8 & Tab1 E12 to Tab2 C12 & several others
Tab1 B13 to Tab2 K8& Tab1 E12 to Tab2 K2 & several others
Tab1 B14to Tab2 S8& Tab1 E12 to Tab2 S2 & several others
Example spreadsheet attached
Simple for all you Excel genius’s, I am only just getting to grips with excel & cant wait to know as much as all of you!
i have created a form in excel sheet1 (not a VBA form) and there are 10 cells with headings in the form which need to fill the data but i want to copy certain cells (suppose 6 different cells) from my form which is in sheet1 and paste to another sheet2 to certain columns horizontally.
View 1 Replies View RelatedI have around 200 excel spreadsheets/workbooks with identical ranges but each with different data. There is a total of 5 columns and 225 rows in each spreadsheet/workbook. Looks something like this:
Workbook 1:
Title 1
Title 2
Title 3
Title 4
Title 5
A2
B2
C2
D2
E2
[Code] ....
The Cells I need to copy are in BOLD. I am trying to paste them onto another workbook as follows
Main Workbook:
Workbook #
File 2
File 3
File 4
A2
Title 5
A3
Title 5
[Code] ....
As you can see, each workbook has identical A2 and Title 5 columns, so they only need to be copied once onto the Main workbook where data from the B and E columns are different for each Workbook. So not only I need to copy and paste from a Workbook onto the Main Workbook, but the code has be able to paste it onto a new row in the Main Workbook (where each row in the Main Workbook will correspond to the data copied from Workbooks 1-200.
I am new to VBA but I tried a code where I would copy and paste one cell at a time onto the same workbook and not onto the Main Workbook, and then how to copy and paste from one workbook to another, but as you can imagine that would take a long time:
VB:
Sub dAmacro()
Sheets("Workbook1").Select
Range("G1") = "Workbook #"
Range("H1") = "File 2"
Range("I1") = "File 3"
[Code] .....
It seems like I need some kind of loop, where it would copy and paste a set of cells and repeat the entire process until it reaches the end of the Workbook 1 while making sure when doing same thing for workbook 2, that the data pastes onto a new row onto the Main Workbook. I feel I have the logic down, but its in the syntax where I am failing.
I have spreadsheet of data, I need to extract any rows that have blanks cells in columns F or P or T.
If possible I would like a macro I could run that would cut all of the rows that meet the above criteria and paste them in to a separate sheet.
Basically, I need to copy and paste upon certain condition (references) and paste related-datas in another sheet corresponding with these references. Every references are organized by lines with indicators on columns.
In attached file, see:
- worksheet ANALYSIS:
* datas in E2, G2, D18, D19, D20, D21, D22 that needs to be copy based on reference in C2,
* then need to find this reference in worksheet called GROUP SAVE,
* and based on this reference, paste above cells value in corresponding "Indicators" column number
THEN, I will have to do pretty much the same with subgroup data. But I can figure out for this second step that request same manipulation.
how to work macros or VBA
I have a workbook with multiple sheets named by month and year that I use to keep track of loans I work with at a bank. In these sheets I have info such as:
Column B = due date
Column D = loan# A
Column E = loan# B
Column F = status
Column H = followup needed
(Columns A,C, and G aren't important for the current need)
What I am trying to do is create a main sheet (TRACKER) that all I will have to do is press a macro button and it will pull info for each loan that is in a pending status.
I need a macro that will search column F (Status) on all sheets and find each instance of "Pending" and once that is done, copy rows B,D,E, and H in each "Pending" instance and copy them to their designated area on my TRACKER sheet. After that is done I need it to continue to the next "pending" instance and do the same on the next available line on my TRACKER sheet.
The overall goal would be that everyday I can press the macro and it will repopulate the sheet with the current pending items (as each day I will change pending status' to complete and no longer need to track it the next day).
I already have the tracker sheet set up and ready to go with the spaces as follows:
Column G&H = Merged cells where due date will need to go
Column I&J = Merged cells where loan# A will need to go
Column K&L = Merged cells where loan# B will need to go
Column M thru S = Merged cells where followup needed will need to go
Essentially I need to copy the first 8 cells in a row in one sheet (for example: A3:I3) when the word "Actuals" is entered into A3 from a drop down list. Then the copied data needs to be pasted to a another existing worksheet in the same workbook in the next available row. The data includes mostly values, but there is a formula in column H that creates a hyperlink out of the content in column G, friendly name in column I.
I am not stuck on the idea of having "Actuals" entered in column A as the trigger or change event and there will be times when a new copy/paste of the same data will need to be done more than once at a later date.
For further information, column B contains a serial number/productID number.
I'm trying to find vehicle make and model in a cell containing a lot of text and then display that in the formula cell. For example if A1 is a paragraph that contains somewhere within it "Ford" & "Ranger". I want B1 to display "Ford" and C1 to display "Ranger". I have a list of vehicles makes (column A) and models (column B) on a seperate sheet.
View 2 Replies View RelatedI have a named range "Lines" (created using OFFSET fuction) in my worksheet. This named range is dynamic as it is created using OFFSET function and points to a particular region in column A only (so its just a 1 dim array). The named range works fine (it changes automatically as I change a dropdown list).
What I want is to display the contents of the array using MsgBox separating the contents using a newline character.
So suppose if the named range "Lines" points to A1:A4 and the contents of it are A1=A, A2=B, A3=C and A4=C, Then I want a VBA code to show:
A
B
C
D
I have tried codes like below, but got errors:
MsgBox Join(Lines, ", "), 0, "Debug"
display workbook not found in Array set without Filepath, As for workbooks found we can use Dir function, but it is not work for workbook not found. The Dir() will return Blank.
e.g. in my filepath only has Book1.xlsx.
[Code] ....
MsgBox CountFound = 1
MsgBox CountNotFound = 3
MsgBox FileFound = Book1.xlsx
MsgBox FileNotFound = Blank
I am trying to figure out how to make a userform to display the contents of a 2-D array which has a variable number of rows. I want the userform to height of the userform to correspond with the number of rows of data to display.
I don't have much experience with userforms, but here's what I was thinking:
VBA code which would find the # of rows of data and then adjust the height of the userform and the length of the lable (which the data would go in).
I create a dynamic array. I want to output all the values in my dynamic array in separate rows.
MsgBox ("the values of my dynamic array are: " & vbCr & _
myarray(1) & vbCr & _
myarray(2) & vbCr & _
myarray(3) & vbCr & _
....
myarray(i))
I used a Fuzzy lookup to match the data that was shared between my two tables. I was able to run 3 different Fuzzy attempts to increase my accuracy because the source data had a lot of spelling errors and such.
Now I am at a point where I need to analyze the 3 possible Fuzzy matches. I am kinda stumped on the best route to do this and thought that an approximate match Hlookup would be the best route but I can't seem to get it to work.
Here is a pic of my table:[URL]
I am trying to match the cell outlined in red against the 3 cells outlined in green, when the best suited match is found I need to copy the green cell and the orange adjacent cells to it's left and right (ID# and Similarity). It would be best if the match threshold was in the low 90% range.
formula to tell me 9 values. These values are on 9 separate tabs/worksheets. They are always at the bottom row in a specific column - but the row changes every week. I have a totals tab in this workbook. I would like to stop manually going to each tab and pressing Control+End to see the total and manually going back to the totals tab to enter the value. Is there a formula to find these 9 values automatically and display them on my totals tab?
View 6 Replies View Related