Loop Through Range From Another Worksheet
Nov 17, 2006
I created an array from data on one sheet...then when using the array (in a loop) on a different active sheet, half the values in the array are lost. However, if I run the code and keep the focus on the sheet with the array data, it works. I can't understand why.
I'll include my code.
aDepartments = Worksheets("sheet1"). Range("A5", Range("B5").End(xlDown).Address)
For Each iZ In aDepartments
MsgBox iZ
Next iZ
If i run this while sheet1 is the active sheet, it works fine - all 15 values (15 in each dimension) are there. If I click on any other sheet and then run the code, only 7 values are in the array (7 in each dimension).
View 4 Replies
ADVERTISEMENT
Mar 15, 2014
Creating a worksheet to consolidate his financial data. I've been able to muck my way through most of the VBA code by looking at examples on the forum. However, I cannot figure out how to add the last loop or where to place it. I can copy the values from the P&L sheet to the DATA sheet but I cannot discover a way to also pull the values from the BalSht sheet to the Data sheet. I think the BalSht loop will be almost identical to the "P&L sheet loop, and be placed just below it - but I'm not successful in multiple attempts and days of trying. How can I accomplish this?
Here's the VBA code:
VB:
Sub NewISCopy()
On Error Goto errorHandler
'*************************************************************
Dim CopyCol As String
Dim PasteCol As String
CopyCol = "G"
[Code]...
Excel file attached.
FI-LOOP-5.xlsm
View 7 Replies
View Related
Mar 14, 2014
I'm attempting to create a worksheet to consolidate his financial data.
How to add the last loop or where to place it.
I can copy the values from the P&L sheet to the DATA sheet but I cannot discover a way to also pull the values from the BalSht sheet to the Data sheet.
I think the BalSht loop will be almost identical to the "P&L sheet loop, and be placed just below it - but I'm not successful in multiple attempts and days of trying.
How can I accomplish this? Here's the VBA code:
[Code] .....
View 3 Replies
View Related
Jan 31, 2014
I need to loop through worksheets in a workbook and copy every first cell value(A1) and then paste into a new worksheet.
I have tried various loops. some have copied first value for the first sheet and then pasted in the new sheet. while others have been not so good.
This is the code I have so far and this does not work at all.
Code:
Sub Check()
Dim ws As Worksheet
Dim lr As Long
Dim treg As Worksheet
[Code]......
View 1 Replies
View Related
Feb 28, 2013
I'd like to extract a particular selection of text from an excel worksheet and paste this in an outlook draft. The problem is, there are multiple worksheets with different data and I'd like data from each worksheet to paste in a separate outlook draft. Therefore, I would have as many email drafts as there are worksheets.
The problem with the code below is, only the data from Sheet 1 is pasted onto multiple email drafts in outlook.
Code:
Sub send_range_as_table()'''''''''''''''''' tools -> refrence -> Microsoft outlook
Dim ws As Worksheet
Dim olApp As Outlook.Application
Dim olMail As MailItem
Dim mailbody As String
' used to insert a line ( press enter)
' create a table using html
[code].....
View 5 Replies
View Related
Nov 22, 2006
I am OK to set values to the third row of "Loop Folder" from a column in " T" but I haven't figured out how to capture the remaining 22 columns of this worksheet.
Workbooks("Loop Folder.xls").Sheets("Sheet1"). Range("A3").FormulaR1C1 = Workbooks("B .xls").Sheets(" T").Range("C5").FormulaR1C1
Workbooks("Loop Folder.xls").Sheets("Sheet1").Range("B3").FormulaR1C1 = Workbooks("B .xls").Sheets(" T").Range("C6").FormulaR1C1
Workbooks("Loop Folder.xls").Sheets("Sheet1").Range("C3").FormulaR1C1 = Workbooks("B .xls").Sheets(" T").Range("C16").FormulaR1C1.............
View 3 Replies
View Related
Jun 25, 2014
I do have macro which populates sheets based on given list.
I want to paste all data in newly created sheets from "Template".
I do not want to loop 2 sheets ("DATA" & "Template").
Data must be pasted with format & validation. Validation exists on Template sheet only. No other sheet is referred for validation.
View 2 Replies
View Related
Mar 1, 2012
I am trying to do payslips. Basically I have one worksheet with about 7 columns. I have a 2nd worksheet that is like a template payslip.Worksheet 1 has hours worked and pay etc.
I want to find a method to loop through worksheet 1 and using worksheet 2 as a template create more worksheets and have the values come from worksheet 1 in the new worksheets.
View 2 Replies
View Related
Jan 27, 2014
I am trying create a macro which asks for the Sheet name as an input for a Sub. I've gotten it to work but there is just one problem: The Cancel button does not work on the MsgBox. Everything else works just as I want to: it shows a dialog box to enter a name for the worksheet and if the worksheet does not exist, it loops and shows a message saying that it does not exist.
However, the cancel button does the same thing as entering nothing in the box and it does not end the process. I would want it so that the cancel button kills the process. Here is the code:
Code:
Option Explicit
Function WorksheetExists(WSName As String) As Boolean
On Error Resume Next
WorksheetExists = Worksheets(WSName).Name = WSName
On Error GoTo 0
[Code] .......
View 2 Replies
View Related
Mar 23, 2014
This is the code I am using, I can not remember where I got it from.
Code:
Sub PDF()
' Saves marked sheets as PDF file.
Const PDF_path = "c:
eports"
Dim Snr As Integer
Dim Name As String
'Process all sheets in workbook
[code]....
This outputs all worksheets that have a specific value for A1, "Y".
My problem is it always prints exactly one too many, an extra copy. I think it may have to do with a broken loop?
View 1 Replies
View Related
Jul 8, 2009
I'm trying to loop thru the worksheets(which are imported from a web form in Outlook) and delete row 31 until the text "PARTS" is reached.
I need to do this to format each sheet exactly for export to an Access database. The code below works when I step thru it but when I save and run from a button it gets stuck in an endless loop. Any help would be greatly appreciated.
JB
Sub DeleteRow31()
Dim w As Worksheet
Dim rng As Range
For Each w In Worksheets
w.Select
Do Until Cells(31, "B") = "PARTS:"
Range("B31").Select
Range("B31:K31").UnMerge
Range("B31").Select
If Not Cells(31, "B") = "PARTS:" Then
Selection.EntireRow.Delete
End If
Loop
Next w
End Sub
View 9 Replies
View Related
Jun 22, 2006
I'm looking for a way to loop through the ActiveX controls (option buttons) in one sheet so that I can have these option buttons reflect what I select on the front sheet.
View 2 Replies
View Related
Feb 15, 2007
Finding the value "OK" in a range of data in Worksheet(1) out of Range("Product"). Ones the value "OK" is found, the entire row is cut and then pasted into a new worksheet 'Range("A3")'. Then the loop sets in and finds the next value "OK" in the range untill it reaches the end of the predetermined Range("Product").
The only problem I have is that the code I have written already performs the process, but when pasting the data into the new worksheet, paste's all of the found rows into the same row. So what you are left with in the new pasting sheet (Worksheet2), is only the last found row because it keeps overiding previously found data. What I need the Macro to do is find the next availible blank row in Worksheet2 and for all values cut out of Worksheet1. Now there was a simular posting to this on the forum, but when I tried it in my code it would not work...
Sub FindAndPaste1()
With Worksheets(1).Range("Product")
Set c = .Find("OK", LookIn:=xlValues)
If Not c Is Nothing Then
firstAddress = c.Address
Do
c.EntireRow.Cut Destination:=Worksheets("Sheet2").Range("A3")
Set c = .FindNext(c)
Loop While Not c Is Nothing And c.Address <> firstAddress
End If
End With
End Sub
I also attach the Excel spreadsheet called Product Macro.xls
View 4 Replies
View Related
Nov 15, 2008
I have anywhere from 3000 to 10000 rows to delete in a a number of worksheets, but would like to avoid using a loop as even with onscreen update turned to off like this:
View 3 Replies
View Related
Jun 13, 2014
I need to loop through a worksheet to find the following product ID's: 100805, 6950000, 853000 and 20994000. Each time I find the product ID I have to execute the same code in that part of the worksheet to extract data.
I just do not know how to set up the macro to loop through each ID.
View 9 Replies
View Related
Sep 14, 2013
I have 12 Workbooks (each for every month) name Jan, Feb, Mar, Apr, May, Jun, Jul, Aug, Sep, Oct, Nov, Dec
These workbooks contains something like the following
Name
Days of Vacation
Something Else#1
Something else#2
Something else #3
[Code] .....
I want something to loop through the months and copy all rows for mary in a summary sheets and sum the Days of Vacation from Column B.
Note that The first Workbook has some data, the 2nd Workbook is the Jan and the 13 Workbook is the Dec, the 14t is the Summary Workbook
VB:
Sub SearchForString()
Application.Calculation = xlCalculationManual
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
LCopyToRow = 2
[Code] .....
View 7 Replies
View Related
Oct 6, 2008
How would one loop through all the workbooks in a network folder and put all of the worksheet names from all of the workbooks into the cells of the current sheet (a local file).
View 6 Replies
View Related
Dec 15, 2008
I need to query a master spreadsheet and pull out all the rows on it that have the same name as I have in cell A1 of my spreadsheet
e.g.
Myworkbook, sheetname = queries
Cell A1 = "ABC"
The Macro queries the "test Data" workbook (c:myfolder estdata.xls) "sheet x" and looks in column A for ABC, if it finds ABC it will copy the row and paste it into Myworkbook, sheetname = queries. There are often several rows of data that need to be copied that all have "ABC" in column A.
Also, in column B of the test data workbook, is a number, the highest value denotes the "version" of the data, the highest number is always the latest version of data that should be retrieved, e.g. if 9 the highest value in column B then all data that has ABC in column A and has 9 in column B should be retrieved, all other records should be ignored.
I guess this is a sort of a macro loop but not too sure how to do it.
View 10 Replies
View Related
Oct 25, 2011
I have a multi-sheet workbook. The first sheet is a summary of results from the rest of the workbook. I would like this summary sheet to auto-refresh itself each time the sheet is activated. The VBA code triggered by the Worksheet/Activate event feeds some parameters out to other sheets, then copies back the results to the summary sheet of the workbook.
While doing so, it keeps "reactivating" the first sheet, causing it to get into an endless loop that is triggered by the Worksheet/Activate event. Essentially, I'd like the Worksheet/Activate event to go dormant for 15 seconds or so each time it is triggered.
View 3 Replies
View Related
Sep 5, 2006
I have an excel sheet being used as a mini database table.Rows = records, columns = fields. I have some VBA to create a copy of base template in the workbook, then populate the new template with the data from a row/record in the db. I currently have about 100 records. After about the 57th record I recieve RT error 1004. "Copy method of worksheet class failed". I think this is becuase excel is running out of memory. My laptop has 1gig of ram, and i have closed all other apps when running the macro.
Is there a way to free up memory while the vba is running, without clearing my "for" or count position which tells the macro to create a new sheet and which row/record in the db to populate the data in the new sheet.
View 5 Replies
View Related
Aug 26, 2012
I wanted to know if there is any way possible to get vba to insert a formula in a cell to each worksheet in each workbook in a folder and then using loop to extract all the info from each worksheet of each workbook in same folder into a master workbook?
View 1 Replies
View Related
Jun 30, 2006
I am trying to figure out a way to condense my code (improve my code) Right now I am using a Macro in Excel to go through a workbook by selecting each column individually and if there is something in the header then formatting that column as text. The problem with the way I am currently doing it, is that due to size limitations on the VBA code, I can only select columns A to Z, and if there is more data in the worksheet it is not formatted. Also I would like the code to check the header and if the word date is in there, format it as a date instead. Also if there is a way to delete any invalid ranges in the workbook
Sub Format()
Dim VarFileName As String
Dim VarPath As String
Dim VarSavein As String
Dim wsheet As Worksheet
VarSavein = Sheets("sheet1").Range("C2").Value
VarFileName = Sheets("sheet1").Range("A2").Value
VarPath = Sheets("sheet1").Range("B2").Value
Workbooks.Open VarPath & VarFileName
For Each wsheet In ActiveWorkbook.Worksheets
Sheets(wsheet.Name).Select
Columns("A:A").Select
Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, FieldInfo _
:= Array(1, 2), TrailingMinusNumbers:=True..........................................
View 3 Replies
View Related
Jun 21, 2012
Code:
For lead = 1 To 20
CurrentFile = ActiveCell
Workbooks.Open Filename:=CurrentFile
ActiveWindow.Caption = "Graboid"
I have a file I use to grab information off a website I subscribe to.
I get daily emails containing a list, then I use that list to extract additional information for each item in the list.
View 8 Replies
View Related
Jan 12, 2013
I am trying to do Loop through Range I have workbook and it has two sheet (sheet1 and sheet2) sheets ("Sheet1").range(A1:K30") hold the Doc_ID (E.g. 78002)
What I want to do........if Range(A1:K30) = Inputbox("Enter Your Doc_Id Number") Then Copy that Cell Only and paste it to in Sheets("Sheet2") -Column A
View 6 Replies
View Related
Jan 12, 2014
I have a range of letter values. The values vary between A, a, and B. I have a "For Each" loop that doesn't do what I'd like. The way it's written, I'd think it'd delete every column that doesn't contain the letter "A".
Code:
For Each k In rngMyRange
If k.Value "A" Then
k.EntireColumn.Delete
End If
Next k
It seems to miss entire columns. Do I need to initialize k?
View 8 Replies
View Related
Nov 2, 2007
I have percentages in each row. Let's say from A4 to Z4. Each cell can be 20% at maximum In Visual Basic I want to write a macro that sums the cells up. So when the sum is less than 100% I want to distribute the remainder among the cells that are below 20%. I want to do this until the total is 100%. So the new percentages have to be entered in the appropriate cells.
How can I do this? I need some sort of loop but I can't get anything to work. With a 'for each cell' statement I always get Value#.
View 9 Replies
View Related
Aug 8, 2013
I am trying to loop through a range, checking for a cell to contain one of two characters and then put a value in a cell to the side (well 3 away) of it. Im getting an error for Type mismatch on line:
VB:
If CellContent = "h" Or "v" Then
the data in the cell is an along the lines of '12.34.56.43 som-thi-vh-ng1'
VB:
Dim MyRange, MyCell As Range
Dim CellContent As String
Sheets("Sheet1").Activate
[Code]....
View 1 Replies
View Related
Sep 30, 2013
I tend to use a lot of For Each loops when I want to go through a range looking for something but this time I'm not so sure it will work.
I have 9 databases each containing over 400 rows with different pieces of client information, these databases are shared between around 40 people (I've also used the 'Allow users to edit ranges' utility to restrict access). What this means however is that the users cannot delete an entire row, instead they can only 'clear contents' on individual cells. This is fine until I try to generate statistics from the databases using autofilters which stop at the first blank row.
So I need to write a backwards for each statement that starts at the bottom and deletes all the blank rows on the way up to the top. I've tried similar things to this before using the For Each but when you use it to delete a row it sometimes skips the next row as it continues at the next one.
I've not managed to get it to work before, I'm just looking for a little guidance really as to how to do this correctly.
VB:
For TopRow = 1 To BottomRow = Range("A1").SpecialCells(xlCellTypeLastCell).Row Step -1
'If is blank delete row.....
Next
View 1 Replies
View Related
Sep 15, 2014
I am working with loans, and I'm trying to program a routine that adds every installment a client owes, so I can know the total amount owed, depending on how many unpaid installments she has. Let say for a client I have every installment in column A (rows 1 to 5), and in column B, I want to add them all. The output should be
Cell B1: "sum(A1:A5)"
Cell B2: "sum(A2:A5)"
Cell B3: "sum(A3:A5)"... and so forth
Since I have many loans, I need a dinamic routine to that sums every installment, where amount of installments vary from client to client. My proposal is (adding from the last installment to the first one):
VB:
months = 4 'an example
For j = 0 To months - 1
initial_row = ActiveCell.Row 'because I run this several times. In this example I am in row 18[code]....
For some reason this is not working, and what I see in Excel after running it is: SUM('AJ18':'AJ18'), which doesn't work.
View 1 Replies
View Related
Apr 28, 2014
I'm trying to understand a code from work and I can't get it. I copied only a part of the code here so please don't run it. Also, I have manual inputs in columns L and M (nodes labels such as 1, 2 and 2,3) and section labels on column N (such as BarFT3, BarFT4 etc):
[Code] ..........
What is this loop doing? I just get the first For loop: goes through every lable on colum N (from 1 to ne) but then what?
What is happening to range nudo(n,j)? How works this ">" sign between nudo(n.j) and nn?
View 1 Replies
View Related
