Modify Loop To Copy A Range Not Whole Columns
Nov 21, 2013
I have the following code that rearranges the data based on the values in the first column (cannot post attachments). Currently, the code is grabbing the whole columns when performing cut and paste and with larger sets of data I am getting "Excel cannot complete this task with available resources" error. Need to modifying the code so it only grabs the used range as opposed to the whole columns.
Code:
Sub ArrangingColumnsByNumber()
Dim Count As Long
Dim CurrentNumber As Long
Dim CurrentColumn As Long
Count = 1
CurrentColumn = 1
CurrentNumber = Cells(Count, CurrentColumn)
[code].....
View 4 Replies
ADVERTISEMENT
Sep 6, 2009
I need to copy several columns (G:L) underneath (A:F)
I got this going using the recorder, and copy the action several times in the macro (See below).
I obviously would like to have this in a loop.
Range("G1:L35000").Select
Selection.Copy
Range("A65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("G:L").Select
Application.CutCopyMode = False....................
View 9 Replies
View Related
Feb 22, 2009
I'm working on a spreadsheet to compile and print checks. All the check information except the date and first check number is contained in a Wins sheet. I also have a Checks sheet which contains a master check. I used text boxes on the master check to contain individual check information. Each text box is filled in from the Wins sheet by means of formulas. The problem I'm having is how to modify the formula entries in the following code to cycle or loop through all checks required. A sample spreadsheet is attached.
View 2 Replies
View Related
Apr 19, 2012
What I'm trying to do is open a closed workbook from an active one, then go through a range looking for cells that has a different value than "noone", and copy the rows that does have something else than "noone" into the first worksheet.
What I've got so far is:
Code:
Private Sub CmdHent_Click()
Dim vikar As Range
vikar = Range(I12, I42)
Application.Workbooks.Open Filename:="C:Documents and SettingsoybMine dokumenterook1.xlsm"
Windows("book1.xlsm").Activate
Sheets("Sheet1").Select
View 9 Replies
View Related
Jan 2, 2007
I'm trying to find a certain word in a range in a sheet and then copy all the data from the same row into another sheet. It needs to loop so that all occurrences of the word are found and the data copied. Below is the code i have tried to use. It doesn't fall over but it doesn't work either!
Private Sub CommandButton1_Click()
Dim Class As Range
Dim Cell As Range
On Error Resume Next VBA:
Set Class = Selection.SpecialCells(xlConstants, xlTextValues)
For Each Cell In Class
If Cell.Value = "Math" Then
Cell.EntireRow.Copy '1
Sheets("Sheet 3").Range("B2").PasteSpecial '2
Application.CutCopyMode = False......................
View 2 Replies
View Related
May 22, 2014
I'm trying to use the following loop to increment the column width of a range of columns thus:
Code:
NewWidth = 0
WidthIncrement = 0.5
For MyCount = 1 To 26
NewWidth = NewWidth + WidthIncrement
Cells(1, MyCount).EntireColumn.ColumnWidth = NewWidth
Next
If I set WidthIncrement to be an integer value, the code works correctly. If, however, I make it something like 0.1, all the column widths that the loop acts on are set to 0. I suspect it's something to do with how I'm declaring the NewWidth and WidthIncrement variables - they're currently set to Double. I know that I can have decimal values for column widths (e.g. 8.43)
View 5 Replies
View Related
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
Aug 21, 2007
I am importing some external data and I need to move/modify columns using VB. My spreadsheet looks like this: ....
View 9 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
Jul 27, 2012
Here is my situation:
Background:
- Excel VBA 2007
- I have a macro that inserts a set of vlookups in range D3:D8.
- When D3:D8 has been filled, I need the macro to COPY the Vlookups in D3:D8, skip down one cell, and paste in the next set of open spaces.
- I have a Do statement that isn't working, and and IF...THEN that quits the loop when a blank cell is found (This means the range of vlookups is no longer needed).
Objectives:
- Fix Do...Loop so it copies D3 throuh D8, skips a cell below D8, and Pastes in the next section? - There will sometimes be only one section to paste in, and sometimes 20. It is dynamic.
Code:
Sub InsertVLookup2()
' This macro inserts the VLookup into cell B2
Dim lastcolumn As Long
Range("D3").Formula = "=VLOOKUP($A2,INDIRECT(""'"" & B$1 & ""'!A:I""),9,FALSE)"
Range("D3").Select
[Code] .......
View 1 Replies
View Related
Sep 30, 2013
I have a rand () which provides me a different number upon calculate. I would like to run a loop for 1000 times and take the value and copy and paste in a range.
The below sorta works, but at times it stops or doesn't work at all. I have a loop for the counter and the for each to place the value into the cell and have it run over and over and placing the value in the next cell underneath. I have the counter at 10 so I dont get stuck in some endless loop before I know it works.
Code:
Sub montecarlos()
Dim MCs As Long
Dim c As Range
Dim lCount As Long
Dim lNum As Long
lCount = 0
lNum = 1
Worksheets("MonteCarlo").Activate
[code].....
View 6 Replies
View Related
Apr 7, 2009
Thanks everyone that has helped me with this so far. I am very new to VB so I definitely appreciate all the help.
I have the following code to select rows that meet 2 different criteria and copy them to a new sheet.
The problem is I only want to copy the values not all the formulas - the formulas don't work in the new location as they are referencing cells that are no longer there.
View 6 Replies
View Related
Aug 21, 2014
i need a code line to modify in the code that when find duplicated string (found in 2 sheets) Copy to a next empty Column of a 3rd sheet
how code work: The code is working Fine (excellent!), Its compare 2 given range from 2 sheets( "sheet-1" and "Page-1") and if duplicated string is found on such Ranges, with condition of a min/Max of 5 to 12, then these string are copied on a new sheet called "Intercepted". The problem (is my mistake ) is that the small report is copied, BUT is not copy continuously to next empty column.
I tried to modify my self but not success because i don't know which line delete and where to add the the new code Line.
View 3 Replies
View Related
Feb 15, 2014
I have a workbook that has 2 worksheets. Sheet1 has a data table that is filled from an SQL database. I would like the first column of the data table to have each cell be a hyperlink. The destination of the hyperlink is Sheet2, cell A2. I would like to modify the hyperlink, so that when the user clicks on the hyperlink, the text-display of the hyperlink is copied into sheet2 cell A2. Initially, I thought this was simple. I added the event code shown below:
[Code] ........
This worked, however there was a problem. I manually added the hyperlink to all cells in column1 of sheet1. However, this made the TextToDisplay property the value of the first cell for all hyperlinks. So, solution to add hyperlinks to all cells in the first column, with TextToDisplay property set for each individual cell. What is the best way to do this with the fact that the number of rows in column1 will vary as the size of the SQL query changes?
View 1 Replies
View Related
Dec 14, 2011
I am using the following code to copy the contents of a DDE feed.
Code:
Private Sub Worksheet_Calculate()
Worksheets("Sheet2").Range("A1").EntireRow.Insert Shift:=xlDown
Worksheets("Sheet1").Range("A3:F3").Copy
Worksheets("Sheet2").Range("A1:F1").PasteSpecial Paste:=xlPasteValues
Application.CutCopyMode = False
End Sub
What I want to do is only copy the row when the columns contain data, as it stands at the moment it's copying blank rows to sheet 2. Is there a way that I can do this?
View 3 Replies
View Related
Mar 7, 2012
I 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.
View 2 Replies
View Related
May 29, 2013
I need to use the valve in the last row in column 27 and subtract the value in column "Y". Looking to modify this "R37C27" to dynamic range
Range("AG1:AG" & lr).FormulaR1C1 = "=IF(R[2]C[-13]=""Nanner Table"",R37C27-RC[-8],RC[-6])"
View 1 Replies
View Related
Jul 25, 2007
i have a list of countries in a column and have to go through this column ensuring they are in the correct format. I'm struggling to figure out how to iterate through each row of the column, test it's value and then change if necessary...
for example
column a --> column A(modified)
Usa --> USA
Ukraine - Mobile (Umc) --> Ukraine - Mobile (UMC)
Uk --> UK
Uruguay --> Uruguay
so values in column a becomes like those shown above.
View 9 Replies
View Related
Feb 7, 2008
I have some numbers in a column that I need to copy 12 times (each one) into another column. The problem is that I got like 200 records that will be converted in 15000 aprox. I've uploaded an example of what I need,
View 3 Replies
View Related
Jan 3, 2013
Is there any way to copy a column that has a specific range
=COUNTIF(B10:BE10,"*p")
=COUNTIF(B12:BE12,"*p") etc
to copy that to a column lets say 5 columns to the right without my range changing?
=COUNTIF(G10:BJ10,"*p")
=COUNTIF(G12:BJ12,"*p")
I need the same range in all columns, and I cant seem to accomplish this.
View 3 Replies
View Related
Dec 7, 2012
Here's my macro:
Code:
Sub CopyRow()'
'Copies row to new sheet, highlights it, marks column 'A' as copied.
'
Dim cCell As Range
Set cCell = Selection.Cells(1, 1)
Selection.Copy
Sheets("Sheet2").Select
Rows("2:2").Select
[Code] .....
Is it possible to modify it to paste into a different workbook called c:filesDestination.xlsm, instead of the existing workbook (Source.xlsm)? The destination sheet name is the same (Sheet2). It's OK if both workbooks are open at the same time.
View 2 Replies
View Related
Aug 16, 2014
I want to perform double_click event for combobox, but only for cells in certain columns and rows.
this is code :
[Code] ....
I have to loop through columns 6 to 67 with step 1, and rows 19 to 409 with step 5, but don't know how . I attached sample, coloured cells needs that double_click event.
Attached File : Loop_Column_Row.xls
View 12 Replies
View Related
Apr 15, 2008
I am having some problems in setting up a macro that is intended to do the following
Run for every cell in a collumn a check if cell =2
If true then B1 is copied to that cell.
If false then nothing
This seems rather simple but the loop should go to collumn B, C, D, E, F,... until the last collumn.
Every time the collumn shfits the cell to be copied also shifts so is B1, C1, D1, E1, F1,...
View 14 Replies
View Related
May 12, 2011
I have a data set that I need to reorganise
Currently the data runs across the columns as:
COL A:C = Descriptions
ROW 1:1 = Week End dates
ROW 2:2 = Week Number
and D3:BC122 = Values
AccData
[code].....
What I need to do is create some VBA that will loop through E:BC moving the data in the column to under the data in Col C each time (and also A:C)
I can create loops that add 1 to row number but am stuck adding 1 to column in VBA.
View 7 Replies
View Related
Apr 5, 2013
I have two columns of data J2:K1000, I need loop through both columns at the same time and copy the data into cells into E1 and F2, then run through the remainder of the code. The code below I have adapted but cannot get it to work correctly.
HTML Code:
Sub Test()
Dim LastRowColJ, LastRowColK As Long
Sheets("Sheet1").Select
LastRowColJ = Range("J65536").End(xlUp).Row
For Each c In Worksheets("Sheet1").Range("J2:J" & LastRowColJ).Cells
Sheets("Sheet1").Range("E1").Value = c
For Each d In Worksheets("Sheet1").Range("K2:K" & LastRowColJ).Cells
[code]....
View 4 Replies
View Related
Aug 21, 2009
my worksheet has columns e1:il100 populated with data. what i am trying to do, is to insert a column to the right of column 'e', copy the contents of cell 'e1', to rows 3:100. then i want to cut e3:f100, and place it in the next available row in columns b:c, delete columns e:f, and then goto the next populated column, which i believe is now column e, and start over, until all columns have been moved to columns b:c.
View 9 Replies
View Related
May 3, 2007
I have a dataset with has 12 columns of data followed by 49 columns of values (one for each product) What I need (to be able to pivot it) is one column of values. I had written a quick for each loop but this doesnt work for colums:
Sub Adjust()
' Find last cell populated by data
Range("A65535").End(xlUp).Select
Last_Cell = ActiveCell.Row
'Rename a column heading
Range("M1").Value = "Partner"
Range("M2:M" & Last_Cell).ClearContents
'Loop through each column and copy data values
For Each Column In Range("N:BJ")
' Find first empty cell
Range("N65535").End(xlUp).Select
Start_Cell = ActiveCell.Row + 1.....................
View 2 Replies
View Related
Apr 30, 2012
i would like to create loop throug among columns, is xlup or xldown methods will make sensne ?
View 2 Replies
View Related
Nov 17, 2008
I've been working on coding a summary sheet that pulls data from
worksheets in the active workbook.
I'm almost done, but am stumped at writing some code that will loop
through the columns that I have copied and total them. I recorded a
macro, but nothing happened when I ran it.
What I have now is this code snippet that I found which works
perfectly when I put my cursor in the active cell.
I am not sure how I should write it into my sub.
code that I found that works:
Set rng1 = ActiveCell.End(xlDown)
Set rng2 = rng1.End(xlUp)(2, 1)
rng1(3, 1).Formula = _
"=Sum(" & Range(rng2, rng1).Address(False, False) & ")"
I tried this:
For Each col In NewSh
ColNum = ColNum + 1
Range(RwNum, ColNum).Select
Set rng1 = ActiveCell.End(xlDown)
Set rng2 = rng1.End(xlUp)(2, 1)
rng1(3, 1).Formula = _
"=Sum(" & Range(rng2, rng1).Address(False, False) & ")"
Next col
View 9 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