Cleaning Up Offset Copy To Another Worksheet
Apr 26, 2007
This is the current code I am using, it works but from another post it looks like it could be cleaned up.
With Sheets("Web Data")
.Activate
Range(.Range("A6")).Offset(2, 1).Resize(2, 24).Copy
Sheets("MW Forecast Calculation").Select
Range("C9").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
This is the code I am trying to get to work but I am having trouble with the red text.
Set CopyRange = Sheets("Web Data") Range(.Range("A6")).Offset(2, 1).Resize(2, 24).Copy
Set PasteRange = Sheets("MW Forecast Calculation")
Cells ("C9:Z10")
CopyRange.Copy PasteRange.Values
View 5 Replies
ADVERTISEMENT
Oct 10, 2007
As seen in the attached spreadsheet on sheet1 the template is 6 columns and 5 rows. The colors correspond to the information needed on sheet 2. this template includes data from row 3 on sheet 2. I need a macro that will copy this template and return the next row of data from sheet2. this has me stumped. Also the data changes on sheet, so it may have many rows or just a couple.
View 7 Replies
View Related
Feb 13, 2014
I want to do the following:
If cells in column B from Sheet 1 contains ''text'',
Copy Cells from column D and F from Sheet 1 - TO - Sheet 2 in columns C and E -
View 1 Replies
View Related
Dec 22, 2008
I am basically doing some housekeeping and trying to reduce my number of code lines.
Can this bit of code be done a bit simpler?
For r = lrow To 15 Step -1
If Range("N" & r).Interior.Color = vbRed Then
Range("N" & r).Interior.Color = vbCyan
Range("N" & r).Value = "Triangular"
End If
If Range("V" & r).Interior.Color = vbRed Then
Range("V" & r).Interior.Color = vbCyan
Range("V" & r).Value = "Triangular"
End If
Next r
View 9 Replies
View Related
Apr 15, 2007
It has been working perfectly from all sites but for some reason started to enter the wrong data once in a while?
Sub Macro8()
Application.StatusBar = "Updating Data from NOAA"
Application.DisplayAlerts = False
Application.ScreenUpdating = False
Sheets("Web Data").Select
With ActiveSheet.QueryTables.Add(Connection:= _
"URL;http://www.srh.noaa.gov/ifps/MapClick.php?TempBox=1&HumidBox=1&Submit=Submit&FcstType=digital&textField1=32.74&textField2=-96.45&site=fwd&Radius=0&CiTemplate=0" _
, Destination:= Range("A15"))
.Name = _
"MapClick.php?TempBox=1&HumidBox=1&Submit=Submit&FcstType=digital&textField1=33.65&textField2=-95.54&site=fwd&Radius=0&CiTemplate=0_5"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False............................
View 7 Replies
View Related
Apr 27, 2006
I have a formula that uses the OFFSET function so that subsequent row adds will automatically adjust the formulas. For example, cell A1 contains the value 1 and cell A2 contains the formula:
=offset(A2,-1,0) + 1
When I insert a row above row 2, the formula above adjusts to reference cell A3, which is what I want.
What I am running into is that when the workbook is opened, something in the workbook changes, causing the "Do you want to save the changes..." popup to appear even though all I do is immediately close the workbook.
I have traced the problem to my use of the OFFSET function, as when I change the formula to
=A2-1
and repeat the scenario, the workbook closes without the "save" popup.
Does the OFFSET function cause something to change (no cell values appear to have changed) and is there a way to get around this?
View 7 Replies
View Related
Jul 27, 2007
I'm still getting to grips with the Find Command. Basically, I have the below code that looks at a line in a sheet(ws_Site) based on the Line ID Number that is in column A and then Finds that that LineID_Value in a different sheet(ws_main) and copies a value from column I on that line across. Problem is, if the cell I is blank it copies it across and essentially clears the cell if there was data in it.
How can I adjust the below code to only set ws_main.Range(c.Address).Offset(0, 8).Value = LineID_Value if LineID_Value is not blank?
For Each LineID In ws_Site.Range("A7:A" & SiteRows) ' Loop through all the cells in range
LineID_Value = LineID.Offset(0, 8).Value
'Dim c
With ws_main
Set c = .Range("A7:A3000").Find(LineID, LookIn:=xlValues)
'If Not c Is Nothing Then
ws_main.Range(c.Address).Offset(0, 8).Value = LineID_Value
'End If
End With
Next LineID
View 3 Replies
View Related
Aug 31, 2007
I am using VBA to enter customer information into a Microsoft SQL database. I have an excel sheet with thousands of customers listed. I will be using a While loop to process each line of the sheet. Part of the While loop needs to make any format changes necessary before the data moves over to the DB. So here is the issue:
How do I turn the following phone numbers:
(410) 273-9200
910-944-1232
276 623 4254
410 612 1100 Rob
(413) 786-1636 Cindy
1-800-783-4661
304) 842-5491 Sherry
Into a uniform layout? I would prefer ###-###-####
Each of the numbers above is an example from the list I have. There may be more variations (I have over 20 seperate lists to process, with over 10000 customers per list)
I imagine I need some process that will remove all character except numbers and then break the numbers up to add the hyphens back in at the appropriate places.
View 10 Replies
View Related
Jun 24, 2008
I have a database of sales records containing 6 address columns (C-H). I want to extract the country (which may be entered in any of the 6 columns) and place this in a separate column. I have a definitive list of countries on sheet 2 which I have saved as a named range (Country).
I would like to be able to set something up which searches the six address fields on sheet 1 and if a match is found against the Country range, enter this in a separate column (I). A non-code solution would be ideal as I am nowhere near that level yet and want to be able to understand what I am doing as far as possible!
View 9 Replies
View Related
May 15, 2007
I frequently use the record option to start off my macros. I realize this usually results in recorded events which are not absolutely necessary (recording a print macro is one that is full of excess line items). I then try and combine what I have recorded with what I have garnered from this wonderful website. If my spreadsheets are small - and I am not writing code to pass any inspections - is there a need to go back and clean up?
View 2 Replies
View Related
Mar 26, 2014
I was given a task to build a userform and all the info collected from the userform will then transfer to a worksheet named 'Promotion'. I was copying the code from last year's UserForm and with my 0 VBA background.
With the below code (I just extract part of it), the ideal case would be to transfer data to Cell A4 (which is the first blank cell on the worksheet), and when a second form is recored, it will start from A5, so on and so forth. But somehow, the code transfer the data to Cell A1001, and the columns don't match with the info either.
Private Sub cmdAddPromo_Click()
Dim RowCount As Long
Dim r As Long
Const LastRow = 2000
[Code]...
The other thing is I don't really know why there is 'Me.' before each common buttons...
View 9 Replies
View Related
Mar 10, 2009
I'm trying to copy a range, specified by an offset, then PasteSpecial that range to yet another location specified by an offset.
This is the code I have so far, which does not attempt to find the range to copy via an offset.
View 5 Replies
View Related
Jun 22, 2006
Following Dave's rule regarding using the rows in Excel rather than the columns I am trying to re organise some workbooks that have been passed to me. To copy one column of information requires 4 pages of code the first section of which is below.
Sub Copy1()
Sheets("INPUT_SHEET2").Select
Range("F4").Select
Selection.Copy
Sheets("INPUT1").Select
Range("C6").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End Sub
Now that I have the code to copy one column I want to be able to adapt it to move four columns to the right from F to J and using the example above copy from J4 to C166. I am guessing that Offset is the way to do this but can't seem to work out how. ach block of data takes up 160 rows and the data is all pasted into column C. The data to be pasted starts in row F then J then N and so on up to column IZ in some of the workbooks I am trying to revamp.
View 5 Replies
View Related
Apr 13, 2009
look for a certain value in worksheet A and copy that row of data to Worksheet B.
However, it seems to be only copying the row in worksheet A and pasting it. Is there something that a noob VBA scripter has missed out?
PHP Private Sub GetInfo_Click()
Dim r As Long, LastRow As Long, Status As Integer
Dim Message As String, Title As String, Default As String, MyValue As String
Application.ScreenUpdating = False
MyValue = Range("A4").Value
Workbooks("invoice.xls").Worksheets("A").Activate
LastRow = Range("C65536").End(xlUp).Row
For r = LastRow To 1 Step -1
If Cells(r, 1).Value = MyValue Then
Rows(r).EntireRow.Copy
Workbooks("invoice.xls").Worksheets("B").Activate
Rows("8").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Status = 1
Workbooks("invoice.xls").Worksheets("A").Activate
Rows(r).EntireRow.Delete
Exit For
End If
Next r
Application.ScreenUpdating = True
View 2 Replies
View Related
Apr 26, 2014
However I have survey data results and in one of the cells it has multiple values which are separated between ; and some are not separated at all e.g B&Q; The Range; Wicks The Garden Shop
Also there are spelling mistakes everywhere and variation of the word B&Q e.g b+q, B n Q
I need to add count up all of the B&Q, Wicks etc...
View 7 Replies
View Related
Jun 22, 2014
I have an old customer list maintained by my company's previous owner in excel format that mostly stored numbers ###-#### format, but also some in ###-###-#### format.
My new list has always been maintained with numbers in (###) ###-#### format.
I have merged these two lists in a new excel file have begun the tedious process of cleaning up duplicates and bad data.
I have used conditional formatting to highlight duplicate numbers in the appropriate columns (L, M, N, O) in this case. However, it does't find matches in the other formats or perhaps the number with the old area code. I know the area code change could lead to false positives, but they should be minimal as this is a small business with mostly local customers. So what I'd like to find out is a formula for conditional formatting that will compare the last 8 characters in each cell (###-####) so as to eliminate issues of missing or different area codes. I would like it to check all of the columns for duplicate numbers not just within each column or within each row individually.
Also separate but related if possible to highlight a different color any cells with data not in any of these formats or if not in (###) ###-#### format if it's too much for all the formats.
View 8 Replies
View Related
Feb 2, 2012
I wanted to create a target workbook that is a subset of the source workbook
1) I want to specify a list of worksheets in the source workbook:
For Each WSCurrent In Sheets(Array("SheetA", "SheetB", "SheetC"))
Next WSCurrent
2) I want to them copy these sheets into another workbook (don't know how to code this)
3) I then want to hardcode all these sheets (I don't know the most efficient way to do this)
4) Lastly, I want to eliminate certain columns (can be fed through a hardcoded list of Columns to delete e.g. X, W, Z)
View 5 Replies
View Related
Mar 18, 2013
Is there a formula where I can take a zip code, and for any zip codes that have the 4 digit zip code extension, to remove those 4 digits, and to add a zero to the beginning of any zip codes where the zero was cut off. Is there a formula I can run my zips through to do both of those tasks?
View 1 Replies
View Related
Jun 21, 2006
I have several time series in excel pulled from a datasource on a daily basis, however some series include weekends, some dont and some others have a few days missing. What I did is I already have a column set up with only weekdays on the other sheet, what I am trying to do is match the data with the dates.
The obvious choice is vlookup, but given that I have over 50 time series and the time span is over 10 yrs, its not the optimal solution...it will crash the sheet i think. I also tried a combo of match and index...but same...too many formulas. I think I need a macro. Does anyone who analyzes time series have a "cleaning" code that he oe she can share with me?
View 9 Replies
View Related
Oct 23, 2011
Copying the target cell to target.offset(0,-3)
View 5 Replies
View Related
Jun 25, 2006
I created macro that copies cells from one worksheet to another using the copy/paste function. Unfortunately it runs rather slow. Instead of copy and paste I want to use something like this:
ActiveCell.Offset(0,1)= Sheets("Raw Data").ActiveCell.Offset(2, 3)
Can this be done and what is the proper syntax?
View 6 Replies
View Related
Jul 19, 2006
Can i convert this
ActiveCell. Offset(-1, 0).Copy
With ActiveCell
.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
.ClearOutline
End With
So that i can copy/paste the Offset range such as (-1,-2) (-1,1)?
View 4 Replies
View Related
Aug 2, 2006
I am trying to locate specific values in column a of a spreadsheet and I want to be able to move (cut and paste, offset, ???) only those values to column c of the same row. The values I am looking for are text and they all begin <@29>, <@33>, etc. (pagemaker codes). I want to move the cells with the pagemaker codes to column c. and leave the other ones in column a. Does anybody know how to do this using vba? Below is a sample of my column a.
16D
16D
16D
16D
16D
16D
16D
16D
16D
16D.........
View 3 Replies
View Related
Apr 11, 2007
I need to create a macro wheather it be for each row or perhaps a dynamic macro that based on the active cell it can then offset based on my Expense type(Column B) into the respective row.
i had placed a sample of what i need to come up with highlighted in red. I need to copy the data in C7, C8, C9, etc... into D7:N7, D8:N8, etc... I have over 950 rows this code will need to be copied to. Columns do not move.
So if choose "fixed then i need for the macro to copy and paste data in each of those cells for Febuary to December. Should i choose "variable" then it places a "0" for Febuary to December and should I choose "NA" then nothing happens and i dont need code for that. It needs to be running constantly that way i dont need to run the macro so it needs to be in a module i beleive.
My problem is that i have three years of data and i have over 950 rows for expenses. THis is just a morsol. I cant place code in the cells as the expenses go there and it will just over write the code and is useless to me. That is why i need a macro that based on what i choose it then copies January's expense appropriately and for now it doesnt need to be dynamic but i prefer that it be based on the active cell if this is possible as i used over 950 option buttons and it worked but as the file gets bigger it crashes my computer.. 'Not enough Resources" .. gigs of ram and a 3.6 processor..
View 9 Replies
View Related
Jul 7, 2007
My source data generates a sheet full of individual dates and condo rates. We'll call it the "Rates Tab". The dates are not sorted or organized into a single column and to complicate it further the data has blank rows periodically. The only recognizable pattern is: (example) the date is in cell b12, then the condo rate will be 2 columns to the right in d12. On a separate sheet, the user will input a check in date(date A) and check out date(date b). I need to find the rates for all dates from Date A to Date B located on the Rates Tab. how to find a Date across various columns and then return a value 2 columns to the right from the "target"?
View 6 Replies
View Related
Jan 15, 2009
I am wondering if there is an easier way to look up values in a matrix. I use the VLOOKUP with the vertical values as the lookup_value, then another lookup for the col_index_number to find the horizontal values. The example in the attached file is just a sample of my spreadsheet. I need to apply this to a roughly 10*10 matrix (which changes every now and then) and apply this matrix to over 1,200 rows. Is there an easy way to returne theses values or is the way I am currently doing it the best option.
View 3 Replies
View Related
Jan 21, 2009
I would appreciate some ones help to correct a macro I'm trying to write. The macro copies 3 columns from Sheet1 to Sheet2 in a selected location. The 3rd column copied needs to be pasted in a different column in Sheet2.
View 6 Replies
View Related
May 21, 2009
I am attempting to do is have a macro (Via Command button) go through column A to find the word "TOTAL". Once that is found I want it to offset by 3 rows (from the word "TOTAL") to grab the number and place it into column B.
View 2 Replies
View Related
Aug 23, 2012
I am trying to achieve something like this :
Find a specific text in my column B (example : "Proposal ID"), when "Proposal ID" is found, select this cell and offset to the column C (Offset(0, 1)). Then copy this cell value in another sheet.
This will be repeated with different texts (always in the column B), so if the text is not found, I need the macro to continue running.
View 5 Replies
View Related
Nov 27, 2012
using VBA and most of what i know has come from reading through blogs. I'm trying to copy 5 separate pieces of data from one row on our Payroll sheet and paste this in to another sheet call master dump.
The issue that i am having is that the code i have written keeps coming up with a run time error 1004 at the line "a.Select"
What i need the code to do is this: Copy the data from cell A4 and paste this on to another worksheet in to row cell b2, date worked in to d2, pay code in ot f2, hours in to h2 and the cost centre in to ad. all on the same row. i then need it to move on to the next team member (in this case A5) and repeat until there is no emp#. once the monday is done it will need to move onto Tuesday.
Code below.
Sub payroll_data()
'
' Payroll_data_MON Macro
Dim a As Range, b As Range, c As Range, d As Range, e As Range, i As Range, j As Range, k As Range, l As Range, m As Range
Set a = Range("A4")
Set b = Range("I4")
Set c = Range("G4")
Set d = Range("H4")
[code]....
View 9 Replies
View Related
