Macro For Moving Data

Sep 1, 2007

I need to move a lot of data from what was originally a txt document into an easily readable form.

I have used Macro's before but only for very basic routines (so please treat me as a bit dumb when it comes to Macros).

I have Include Screen shot of the data highlighted in a colored box and the relevant colored cell I need to move it into.

This needs to be repeated many times with data that is consistent in its layout.

View 12 Replies


ADVERTISEMENT

Moving Data On Closing Using Macro

Jul 9, 2012

I am looking to take the information from a selected cell in workbook a, and on closing the spreadsheet - moving that piece of data and adding it to the bottom of a list in column d of workbook b....

View 2 Replies View Related

VBA Coding - Macro Moving Data With Multiple Variables?

Jul 17, 2013

I'm working on a complex spreadsheet and I'm working on a complex spreadsheet system for pulling and measuring data. My VB programming skills are about minimal/average, so you may see me on here asking various questions . In any case, what I'm trying to do is create isolation macros for "Kickback" data (erroneous). I'm trying to remove data with certain criteria and isolate it on a separate "kickback" sheet for one for taking a second look at. I've made the easy macro of creating a new spreadsheet:

Sub Create_Kicbacks_Sheet()
' Create_Kicbacks_Sheet Macro
' Creates "Kickbacks" sheet for invalid information.
Sheets.Add After:=Sheets(Sheets.Count)
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "Kickbacks"
Sheets("Kickbacks").Select
End Sub

This coding works correctly. The problem area I'm finding is the sorting data. My goal is to look at Columns A and B for certain criteria and either leave it alone, move it to the "Kickbacks" sheet or delete (due to not being necessary in data calculations). Basically, here's a synopsis of what I'm looking for:

if Column A = Y and Column B = Mandatory -> Leave Alone
if Column A = Y and Column B = Best Efforts -> Move Row to Kickbacks
if Column A = Y and Column B = Empty Cell -> Move Row to Kickbacks
if Column A = Empty Cell and Column B = Mandatory -> Move Row to Kickbacks
if Column A = Empty Cell and Column B = Best Efforts -> Delete Row

Here's the code I have in excel (modified from one I found online)... Which only is doing some of what I want it to do:

Sub Moveto_Kickbacks()
Dim r As Range, LR As Long
With Sheets("Data")
LR = .Range("A" & Rows.Count).End(xlUp).Row
Set r = .Range("A2").Resize(LR - 1)
.Range("A1").AutoFilter field:=1, Criteria1:=""
.Range("B1").AutoFilter field:=2, Criteria1:="Mandatory"

[code]....

View 2 Replies View Related

Moving And Deleting Entire Rows Between Tabs In Workbook And Moving Them Back If Needed

Sep 23, 2013

I have an excel work book with 6 tabs. I would like to have Excel move an entire row from one tab to another tab (removing the row and inserting it in the other tab). I.e. Example I have a tab with items that are marked as "Open Actions" so if I were to change the drop down to close. Excel would move that entire row of actions to the tab with the "closed actions" and insert into the next available row. Now if someone were to come back at a later date say no it should be reopened than I would change the drop down to open and excel would move that row back to the open actions tab into next available row. I tried a PIVOT table and no good I played with few macro and not.

View 5 Replies View Related

Macro PasteSpecial But Should Not See Moving?

Jun 6, 2014

Not sure how to say that, but I'm trying to write a simple macro to copy some data and pastespecial (Transpose) it somewhere else on the same sheet. But I don't want to see the data "moving".

Here is an example:

Code:
Sub Macro1()
Set SCOPE = ActiveSheet.Range("D2:G6")
Set THERE = ActiveSheet.Range("M240")

[Code]....

View 2 Replies View Related

Moving Workbook With Macro

Jun 4, 2007

I have a spreadsheet that is finally working great. I made a custom tool bar with 5 buttons and each tied to a seperate macro. On my laptop ( where I developed it) it works great.

I put the whole sheet on on a thumb drive and moved it to another computer. When I load the sheet on the new computer , the tool bars are there but they error saying something about they can not find it.

I went into each button and changed it to put the macros in this workbook, saved it and when I put it on the other computer I had the same issue. I reloaded the original sheet and it went went back to all open workbooks.

I think what I did is saved them to all workbooks and they must be stored on the original computer. I thought just changing the button link would do it but I guess I am wrong.

My question is how do I get the 5 macros to follow the workbook? I need to move this to another computer in the morning.

View 9 Replies View Related

Moving Down A Cell Within A Macro

Apr 23, 2007

What I need to do is have the macro move down a cell each time the data is pasted -

Sub saleschartnew()
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("G3").Select
Selection.Copy
Sheets("Monthly Sales Chart").Select
Range("E10").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Sheets("Entry form").Select
Range("C13").Select
Application.CutCopyMode = False...............

I did search and found this: activesheet.cells(row,Col).offset(0,1).value = " Properties"

However, my knowledge of VBA is basically zero so I am not sure how this would be implemented into the macro.

View 3 Replies View Related

Excel 2007 :: Moving Data From One Worksheet To Another Keeping Data And Formatting?

May 5, 2014

I am working on a project that has 5 worksheets. I have been able to figure out everything else I need to do but this has me stumped. I have data in Sheet1 A6, that i want to place in Sheet2 A6, Sheet3 A6, Sheet4 A6 and Sheet5 A6 and keep data and formatting(BOLD AND UNDERLINE). So I change Sheet1 A6 and the other 4 sheets change also. I'm using Microsoft Excel 2007.

View 1 Replies View Related

Macro For Moving Rows To Other Sheet

Nov 29, 2006

I'd like to write some code to do the following: When the first cell of the row contains an "Y", move the entire row to another sheet (in the same file), keeping in mind that this sheet already contains some rows (so add the row on the first empty row in the sheet). Afterwards the original sheet contains no longer any rows in which the first cell is filled with an "Y".

View 4 Replies View Related

Macro For Naming Tab And Moving It To The Beginning

Mar 17, 2014

I have the code below to add my new sheet and give it todays date (coming from the menu sheet). However I can not figure out how to add the before code so it will save the new sheet at the beginning of all of the sheets every time.

[Code] .....

View 7 Replies View Related

Moving Rows And Columns With A Macro

Jun 5, 2009

I have a massive data file with thousands of lines of data. Each line is a pair, for example

View 6 Replies View Related

Insert A Row With A Macro On A Moving Table?

Jan 27, 2010

I'm trying to create a macro that will allow me to insert two rows to extend two separate tables. The problem is that one table needs to be directly below the other. So if the number of additional rows exceeds the margin between the two tables, the macro will just be inserting two rows into the top table.

I guess I'm looking for a way to get the macro to insert a row, not at a specific row number, but at the first row of the bottom table, which will change as rows are added to the top table.

View 3 Replies View Related

Automatically Moving Cells With Macro

Jan 7, 2008

I have a list of diagnostic procedures, some of which are for the same patient. I need a macro that does the following: move every second procedure (a couple of cells per row) of one patient number (column B) next to (11 cells to the right and 1 row up) the first procedure. But only if there's less than 3 months between the procedures (dates are in column G) and if both procedures were on the same side of the body (left or right, column L)
Is there a macro that can do this for me?
Is there a macro that just moves a couple of cells in a row 11 cells to the right and 1 row up?

View 9 Replies View Related

Error After Moving Macro To Personal.xls

Jun 3, 2009

The following macro does what I need when run from the active workbook however I need macro to exist in 'personal.xls' so it can be easily applied to raw reports received in daily e-mails.

Sub Add_Sundays_Data()
Dim bk As Workbook
Dim bSave As Boolean
Dim lRow As Long

On Error Resume Next
Set bk = Workbooks("C:Template.xls")
On Error GoTo 0
If bk Is Nothing Then
bSave = True
Set bk = Workbooks.Open("C:Template.xls")
End If

View 9 Replies View Related

Dynamic Range - Moving With A Macro

May 18, 2006

I am not sure if my problem is in the dynamic range or not. I have attached a small sample file. I tried to create a dynamic range by creating a named range of my "LabelRow" with the following as the refers to: =OFFSET($C$32,0,1,0, COUNTA($32:$32))

When I set my cursor on this, Excel makes an outline on the whole row 32 where there is data, which gives me some clue that it understands what I am trying to define. Next, I try to pick this range up and paste it using named ranges in a macro (CopyRange). It gives me an error like it doesn't understand the named range. I do similar macro code in many other places in this project and it works with non-dynamic ranges. Here are some other details on this project. I need a dynamic range because I insert new data into the dataset, which may be 2 quarters up to 20 quarters. This insert causes a regular range to be messed up. My ultimate goal is to graph selected rows of data with the same labels, but I need to have it starting at Q1, which is pushed to the right as new rows are added.

View 2 Replies View Related

Reference Moving Range In Macro

Sep 4, 2007

I want to search a word in my Data sheet and want to know what is the column position. Example: I have a word "LastName" as a Column header in my Datasheet whose column position can be anywhere in the datasheet based on the table selected. So i want to write a generic code which give me COLUMN position for this word in the datasheet.

So if "LASTNAME" header is coming as 7th column it return me 7
if "LASTNAME" header is coming as 9th column it return me 9.

View 5 Replies View Related

Adding Numbers And Moving Total In A Macro

Jan 27, 2014

I was given a spread sheet with a number of payments on it, I was asked to take the "four" potential payments and only show one total payment. There are 2900 lines in my file and as you can see from the example the scenario repeats it self with the "four" payments all the way to the bottom. I would like to be able to add H2 to H5, total that number in J2 and delete row 3,4 and 5. This then has to be continued all the way down to line 2878 where I could then run a macro I have to delete blank rows cleaning my spreadsheet up. I would take a looping or user controlled macro (ctrl +) so I don't have to do this manually.

Sample_total.xls

View 4 Replies View Related

Macro - Shortcut For Moving Between Sheets In A Workbook.

Jan 13, 2009

Could some one amend the VBA below to allow a continous loop on moving to the previous sheet within a workbook. Currently when I move to the first sheet if I use the shortcut key I get a debug error.

View 6 Replies View Related

Macro To Copy And Paste A Moving Range

Mar 9, 2009

I have a macro that copies and pastes into another sheet. When I add a row of data into my spreadsheet I want the macro to be able to change the range size and copy and paste the add row with the prior data.

View 2 Replies View Related

Macro - Moving Active Cell To Column B Of Next Row

Feb 14, 2012

I use this macro to open a hyperlink in "column B" of the next row. However, it only works if I begin the macro from "column N" on the line above. (the hyperlink is always located in column B)

I want to be able to run this macro from any cell on the line above. How to modify it?

Code:
Sub Open_Hyperlink()
'
' Open_Hyperlink Macro
'
' Keyboard Shortcut: Ctrl+o
'
[Code]...

HTML Code:

ABCDEFGHIJKLMNO
1ActiveURLWhatDateFirst NameLast NameOtherOther2Other3Street1CityStateZip
2XLinkData112/21/2011BobSmithData2Data3Data4123 MainMooresvilleNC28117
3XLinkData112/22/2011LarryJonesData2bData3Data4456 MainMooresvilleNC28117
4XLinkData112/23/2011MaryAkinData2Data3Data4789 MainMooresvilleNC28117
An example would be to run it while Cell "I2", "J2", or "K2" is selected and have it open "B3"

View 2 Replies View Related

Macro To Paste In Certain Column, Moving Over Each Time

Mar 31, 2007

I have this macro listed below that finds all these files, copies cells from stat sheet and places them in sheet1 in another file. It keeps looping till all is found and done.

My problem is when it goes to paste in sheet 1 I want it to shift over a row each time.

Right now during the first pass it pastes in column B, I want it to start in column D

The issue seems to lie right under where it says "Put data into workbook"

Public Sub PullData()
Dim wkb As Workbook
Dim lngStore As Long
Dim strDate As String
Dim strName As String
Dim Book As String
Dim Sheet As String
Dim week As String
Dim Store As String

Dim IngRow As Long
Dim strCol(0 To 15) As String

strCol(0) = "b"
strCol(1) = "c"
strCol(2) = "d"
strCol(3) = "e"
strCol(4) = "f"
strCol(5) = "g"
strCol(6) = "h"
strCol(7) = "i"
strCol(8) = "j"
strCol(9) = "k"
strCol(10) = "l"
strCol(11) = "m"
strCol(12) = "n"
strCol(13) = "o"
strCol(14) = "p"

View 9 Replies View Related

Moving Rows Into Multiple Spreadsheet Using Macro

Jan 20, 2008

Is it possible to move rows of data in a spreadsheet to multiple spreadsheet accordingly?
I had lists of tasks in a single spreadsheet and i need to segregate the tasks for all my staff in serial while no duplication among all of them. For instance, i got 4 personals in my department and i need the 1st 4 tasks to be distribute to each of them and next 4 tasks accordingly. This is due to all tasks are equip with due date and i need to calculate how much time i need to accomplishing them. i'm used to manually move it and found it time consuming, so i was wondering if someone would instruct me where or how to achieve it by using a simple macro.

View 9 Replies View Related

Moving Things To The Right (cross Match Up) With A MacrO

May 12, 2009

Sub Moveit2()
Dim Quantity As Range
Dim Cell As Range
Dim Cell2
Dim Breaks As Range
Set Quantity = Range("a2", "a21")
Set Breaks = Range("g1", "s1")
For Each Cell In Quantity
For Each Cell2 In Breaks
If Cell2 = Cell Then
Intersect(Cell2.EntireColumn, Cell.EntireRow) = Cell.Offset(0, 1)
End If
Next Cell2
Next Cell

End Sub

I have this macro to move things to the right. Sort of like a vlookup in a way. However, it runs using one range @ a time. For example it will look up the quantity in A2 versus what's in Row 1 and if they match, data from B2 would be moved into correct column. However, I'd like to run for multiple columns at the offset so I can don't have to keep changing data in the macro. Like want to run for these ranges at the same time as running the first range.

Set Quantity2 = Range("c2", "c21")..........

View 9 Replies View Related

Command Button Moving After Macro Runs

Jan 25, 2008

My command buttons are moving location after I run a macro. I have a worksheet with 5 command buttons from the forms toolbar. When the macro is run, another command button is moved into the cell that the macro ends in, how can I stop this?

View 7 Replies View Related

Adding Formula To Macro Based On Moving Cell?

Jan 14, 2013

I am having difficulty expressing a formula so that if a cell is greater than zero, it adds the value of that cell to a String figure. I have got as far as the following, the 'problem' area is in red.

Sub Share_Sales3()
Dim Prompt As String
Dim Caption As String

[Code].....

View 3 Replies View Related

Data Validation Rules Not Moving With Data

Feb 24, 2014

creating a spreadsheet for work which is almost working a treat Unfortunately, when a row of data moves from one sheet (Queries) to another (Archive), data validation is lost. Initially I thought I daidn't have it set up on the Archive sheet, but on moving it back to queries (by use of a macro) the validation is still not working. how to keep validation rules WITH data when it is moved please? I will upload my file when I get home from work....network restrictions prevent me doing it here!

View 8 Replies View Related

Moving Rows Of Data Into 1 Row

May 20, 2014

I'm essentially getting a lot of data at the moment, which has a few orders people have made on my website.

Essentially, think order id, address etc and then all the products the customer has ordered.

However, the part which includes what the customer has ordered creates multiple rows of data, with the order ids etc duplicated. What I need to do is consolidate this into 1 row. So to add additional columns instead of rows.

The reason fro this is I want to mail merge the data into an invoice and mail merges work of 1 line of data at a time. I've attached an example, any way to do this?

(Attached to this post / or linked here: [URL] ....)

View 1 Replies View Related

Moving Data To Another Cell

Dec 11, 2009

I have 3 columns Z, AA, AB. The heading for Z1 is A, AA1 is B and AB1 is C
In column Z2:Z2000, there is a mix of A, Bs and Cs. I want A to stay in Z1 column, Bs to goto AA1 and Cs to goto AB1, also I want this added to a macro that I previously created, so everything happens with one push of a button

Now for save, not sure if this is possible or not, if I can have this added to the macro as well that would be great. When I push my macro button, the file saves to Dec (todays date) DB (81).xls The number 81 is the total count of A, Bs Cs, this # will change depending on how may A, Bs and Cs there are. I really hope there is a way of doing all this

BEFORE

A B C
B
A
A
B
C
C
C
A
A
B
C

SHOULD LOOK LIKE THIS

AFTER
A----B-----C
----- B.....

View 4 Replies View Related

Moving A Row Of Data Into One Cell

Dec 13, 2012

Essentially, there are two columns I am dealing with. One is "Sales Rep" and it lists all of the sales reps employed by the company. The other is "Zip Code" and that will list all of the zip codes that sales rep is responsible for.

Now, I have a row of data, all of those zip codes listed out, that each rep is responsible for, but my supervisor wants all of the zip codes listed in one cell, in that second column. Example: (02018, 34098, 16711).

The commas are not necessary, but is there any way to get this done other than manually entering them?

View 6 Replies View Related

Moving Data With VB Code

Jul 24, 2007

I have a workbook with 5 Tabs. One of these tabs is "Completed" (for completed work) The other tabs are names of Managers and the tabs contain information about who is doing what work for the Manager and information about it.

What i would like to do is in column F on every sheet is the "status". I would like when the "status" is changed to completed, to have VB code move that entire record to the Completed tab.

I think its possible i just don't know how to do it.

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved