Free Microsoft Excel 2013 Quick Reference

Macro to move data to different cells in worksheet

My excel workbook contains a single column of data, arranged as follows:

Column 1 Data 1
Column 2 blank
Column 3 Data 2
Column 4 blank
Column 5 Data 3
Column 6 blank
Column 7 Data 4
Column 8 blank
Column 9 blank

and then it repeats.

I need a macro to automatically rearrange each group of data into a single row of 4 columns as follows: Data 1 Data2 Data3 Data 4, then go the next row and display Data 5 Data 6 Data 7 Data 8, etc.


Post your answer or comment

comments powered by Disqus
I would like a macro to move data from B5 and look for the last empty cell on column D and enter it there and clear B5 for the next entry. I need the macro to fill column D cells from the bottom instead of inserting at the top.

Thanks

macro to move data between worksheets, between Sheet 1 - 10

anyone know of a macro to copy only visible cells in a range?

Help me with this please: I need a macro to find first blank cell in a column cut the value from left adjacent column and paste in that first blank cell and continue routine until last blank cell and stop

Thanks

Hello,

The objective of this macro is to run through all worksheets (except for the first two) and delete data in selected cells, then select cell A1 before going on to the next sheet. At the end, it should land on sheet 1.

The code below does not run through any sheet except for the active one, which happens to be one of the first two sheets that I DON'T want it to run on.

Sub ClearData()

Dim wSheet As Worksheet
   
For Each wSheet In Worksheets
  Select Case wSheet.Name
                Case "Index1", "Index2"
                Case Else
                     Range( _
       
"E6:F33,R6:S33,K6:N6,K10:N10,K14:N14,K18:N18,K22:N22,K26:N26,K30:N30,X6:AA6,X10:AA10,X14:AA14,X18:AA18,X22:AA22,X26:AA26,X30:AA30,F35:H35"
_
        ).Select
                     Selection.ClearContents
                     Range("A1").Select

           End Select
     Next wSheet
     
     Sheet1.Select
End Sub
I'm not sure what I'm missing.
Any help would be greatly appreciated.

Hi, I need to copy across data that is entered into 20 cells in one worksheet into a new row in another worksheet on the click of a button, which then clears the data of said cells so new data can be input, new data that is input is then added into the next row down of the other worksheet as i dont want to loose the previous data but am confused as to how to do this. please could someone give me some advice as i am not very good at excel or macro's.

Thanks :-)

Here is the file that i'm working on it may help give an idea of what i'm trying to do incase i have not explained it properly.

Hi, as my name implies I am new to VBA/Macros/automation

I have a single workbook with five spreadsheets. My goal is to move data along a path (or work flow) from one sheet to the next by using a "trigger" pulldown menu choice.

Sheet1 = Prospects
Sheet2 = New Sale
Sheet3 = Upgrade Sale
Sheet4 = Won
Sheet5 = Lost
Sheets 1, 2, and 3 use the same data layout for column A - K.
Sheets 4 and 5 have the same A - K as above and also have columns L - R in common.
The last column in sheet 1, 2 and 3 is a pulldown menu (New, Upgrade, Won, Lost).

Data rows on each sheet start at row 7.

The goal is to use the pulldown choice to remove the data from the current sheet (ex: Prospect) and add it to the next open row in the required sheet (ex: New or Upgrade). I also need to be able to make a similar move from New/Upgrade to Won or Lost.

I can do this manually, but don't know where to start to automate, rather eleminate the "copy/paste" which is my goal. Any help would be greatly appreciated.

I have an excel workbook with a worksheet with material prices, part numbers, and vendor info, which I use as my database. I also have another estimating workbook sheet with formulas to add up columns.

I am looking for a macro to move a few cells in selected rows in the database to open rows in the estimating sheet.

The formulas of the estimating sheets would then finish the calculations.
When estimate is finished I can save the estimating workbook and still allow the database to be changed for future estimates. right now I am doing cut and paste, but there must be a better way.

Thanks

I need help designing a macro that can take data that comes in looking like this...

A - B - C - D - E - F - G
123 - 5 e. main st. - Anytown - IL - 60635 - emp1 - john smith1
123 - 5 e. main st. - Anytown - IL - 60635 - emp2 - john smith2
123 - 5 e. main st. - Anytown - IL - 60635 - emp3 - john smith3
123 - 5 e. main st. - Anytown - IL - 60635 - emp4 - john smith4
123 - 5 e. main st. - Anytown - IL - 60635 - emp5 - john smith5
321 - 1 e. main st. - Anytown - IL - 60635 - emp1 - john smith6
321 - 1 e. main st. - Anytown - IL - 60635 - emp2 - john smith7
321 - 1 e. main st. - Anytown - IL - 60635 - emp3 - john smith8
321 - 1 e. main st. - Anytown - IL - 60635 - emp4 - john smith9
321 - 1 e. main st. - Anytown - IL - 60635 - emp5 - john smith0

and moves and filters it so that it looks like this...

H - I - J - K - L - M - N - O - P - Q
- - - - - emp1 - emp2 - emp3 - emp4 -emp5
123 - 5 e. main - town - IL - zip - smith1 - smith2 - smith3 - smith4 - smith5
321 - 1 e main - town - IL - zip - smith6 - smith7 - smith8 - smith9 - smith0

So in the initial column F, the employee #s 1-5 will be constant throughout every set that comes in. Each set is 5 rows, and the number of sets varies from day to day. So I need the macro to move the data from the first 5 rows, and then the next 5 rows after that, and the 5 after that one, etc... basically until there is no more data (upto the blank row)

I have a basic "Copy this, paste it transposed here and advanced filter this and paste it here" kind of macro, but as it stands now, it repeats the code 60 times so it can handle 60 sets of data. As you can imagine, this is very slow.

If you need to see that code, i can post it, but i'd rather start from scratch on this one.

Thank yoU!

If it's possible, I would like to use a command button or checkbox to
trigger moving data from cells in a column (b34-b41) on one worksheet to the
next available ROW on another worksheet.
Thanks in advance.

I am trying to figure out how to use a macro to move the data that is stacked up under the company name to the same row that the name is in.

So, move data from C1R2,R3,R4,R5 into C1R1,C2R1,C3R1,C4R1 and C5,R1.

The attachment has a few lines of the data for an example.

I have tried it on my own, done some searches, but macros don't like to work for people that don't know what they are doing.

Thanks for any help.

I want to do a Trim on non-formula cells in specific columns. Currently the
Trim macro (which works, but is slow because of hitting all cells), looks
like this:-

Sub Trim_Text()
Dim cell As Range
On Error Resume Next
For Each cell In ActiveSheet.UsedRange
With cell
If .HasFormula = False Then
.Value = Application.WorksheetFunction.Trim(cell.Value)
End If
End With
Next cell
End Sub

and I want it to work only on cells in these columns

Range("B:B,E:E,H:H,K:K,N:N,Q:Q,T:T,Y:Y,AB:AB,AE:AE,AH:AH,AK:AK,AN:AN,AQ:AQ").
_
Select
Range("AQ2").Activate

(recorded using the macro recorder), but don't know how the new macro
should be written - all help and suggestions very gratefully received. And
if there's a way to use SpecialCells in the column range B:AQ, I'd like to
see how THAT might be done (columns C, D, F, G, I, J etc contain formulas,
and I want to work only on the columns between, which contain data that may
need trimming). I have looked at Dave McRitchie's TrimALL macro, but don't
understand the syntax etc. sufficiently to be confident in messing with it
:-)

TIA,

Dan

--
Dan E
webbie(removethis)@preferredcountry.com

Hi, I'm sure what I'm about to ask is very simple but I just can't seem to find the answer anywhere. I have written a Macro that works in one cell but I can't seem to copy it so it works on all the other cells in a column. I am using the macro to hide multiple columns when a user selects a value from a validated list, the idea being that if they select a product, say 'Chest of Drawers', then they will only be able to see columns relevant to data you might apply to describe a chest of drawers. That works fine, but once they have applied data for the Chest of Drawers they will then need to do the same for lots of other furniture pieces. So they will then need to select the next piece of furniture on the next row and I want the macro to do the same thing and hide irrelevant columns and show relevant ones. I'm assuming I need to tell the macro to run on active cell but I need help. Can you help please? This is my code:

Private Sub Worksheet_Change(ByVal Target As Range)

If Target = Range("ar1") Then
If Range("ar1").Value = "Wardrobe" Then
Columns("AT:BC").EntireColumn.Hidden = True
Columns("AS").EntireColumn.Hidden = False

End If

If Range("ar1").Value = "Dressing Table Stool" Then
Columns("AS:BA").EntireColumn.Hidden = True
Columns("BB").EntireColumn.Hidden = False

End If

If Range("ar1").Value = "Chest of Drawers" Then
Columns("AS").EntireColumn.Hidden = True
Columns("AU:BB").EntireColumn.Hidden = True
Columns("AT").EntireColumn.Hidden = False

End If

If Range("ar1").Value = "Bedside Table" Then
Columns("AS:AT").EntireColumn.Hidden = True
Columns("AV:BB").EntireColumn.Hidden = True
Columns("AU").EntireColumn.Hidden = False

End If

If Range("ar1").Value = "Dressing Table" Then
Columns("AS:AU").EntireColumn.Hidden = True
Columns("AW:BB").EntireColumn.Hidden = True
Columns("AV").EntireColumn.Hidden = False

End If

If Range("ar1").Value = "Underbed Storage" Then
Columns("AS:AV").EntireColumn.Hidden = True
Columns("AX:BB").EntireColumn.Hidden = True
Columns("AW").EntireColumn.Hidden = False

End If

End If

End Sub

Hi there I am trying to write a macro that will do a bunch of stuff then go to the next blank cell in a particular column.

The rest of the code for the macro is irrelevant I just don't know how to code it to find the next blank cell in the column. It could be anywhere from cell A2 to A1000000. Basically I want the macro to select the cell that is next on the list to enter data into.

I'm looking for a macro that will search through E2:IV2 and take me to the first empty cell in that row.

I've tried a couple of things but none of them seem to do anything.

Any ideas?

Does anyone know a good way to use a macro to paste data copied from an
Excel data table into a Word table. The data will always go to the same
location in Word. For example data copied from the Project Desc field in
Excel will always go to the Project Desc field in Word, data copied from the
Project Location field in Excel will always go to the Project Location field
in Word, etc..

I have advanced knowledge of using macros to move data around in Excel, but
know very little about how to place it into word.

Any help would be greatly appreciated.

David

I have to update daily update "Main file" as attached by selecting blank cell in filter and vlookup with "status file" for 3 column records. I have record macro for that but every time in filter column blank cell changing. Is there any code to activate first blank cell in filter.
I have attached both files main file and status file.

Hi am Stuggling to work out how to set up this excel sheet. basicly i have created a sheet with excel which i want to use to help me remeber and log what i have down whilst taken calls in a call centre. there are basicly there i have created a drop down list for certain viables once i have done or not done some thing i want to be able to make it as done then save on another work sheet what i have recorded and reset the changed value back the way it was and change from call 1 to 2 ect logging each call . so it needs to move data from the main sheet to the next sheet and paste it into one row. then next time i click next call do the same but post it into the next row ect ect. the idea is i will be able to see how many times ive promoted a product and by it copying row by row which call i offered a product on ect

I need help creating a macro to copy from a cell in sheet1 to a cell in
sheet1. Though when it pastes I can't have it fill the same cell over
again I need it to paste it in the next cell down from it.

Example:

Sheet1 -> Sheet2

B12 -> B2
D12 -> C2
I5 -> A2
I17 -> E2
G12 -> D2
I22 -> F2
I27 -> G2

I have merged cells from B through G with rows 16-28 needing to be
copied to H2 but become unmerged in the seperate sheet. Can anyone
help me out?

Hello,

I really could use some of your expert and excellent help with a problem I
am having in creating a macro to perform a search in "Column A" to locate a
change in the data in that column and then move one column to the right and
one row down and type "1" in that cell.

I really appreciate your help, and Have a Blessed Holiday Season.

I have a large workbook that shares data between two worksheets. I need to copy specific cells from the original worksheet, based on criteria in one of the cells to different cells in the destination worksheet. The in the rows for the two worksheets are not identical, that is why I can't copy the entire row. I need to copy cells from columns B-E and H-I in the origin worksheet, to cells in columns A-F in the destination worksheet all based on the criteria in cell B of the origin worksheet. If possible, after the move I need to delete all duplicates created by the copy operation. I have just started learning VBA and this task is way beyond my current knowledge, any help you can provide is greatly appreciated.

Hello,

I really could use some of your expert and excellent help with a problem I
am having in creating a macro to perform a search in "Column A" to locate a
change in the data in that column and then move one column to the right and
one row down and type "1" in that cell.

I really appreciate your help, and Have a Blessed Holiday Season.

Hello,

I've been creating a macro to poulate a database based on information sent to me in a second workbook.
The part I've become stuck on is if the form is sent to me by Chris, with his name in Cell D5 and some data in cell D11, I want the macro to look for "Chris" in my separate database then move to the next empty cell in the row to the right, pasting the data from cell D11.

Is this easy to code?

Many thanks

Currently have 4 sheets in a workbook.
Sheet 1 is where all the data goes - 4 columns, account # and date included.
Sheets 2, 3 & 4 each represent a fee amount. 2=$25, 3=$45 & 4=$75
If an account number shows up on sheet one just once, it moves to sheet 2. If it shows up on sheet one twice, it is removed from sheet 2 and is added to sheet 3. If is shows up on sheet one three or more times, it is removed from sheet 3 and moves the first of all the entries on sheet one to sheet 4.

What I need:
Let's say Fred has already had his acct # show up on Sheet 2 and 3 and today it's being added to sheet 4 as well. I need the acct # to stay on sheet 2 with the date it was added for tracking that he's already paid the $25.00 fee, stay on sheet 3 with the date it was added for tracking that he's already paid the $45.00 fee and then add to sheet 4 with the date it was added as well. Then next week, if it's added yet again to sheet 4, I need it to add the data AGAIN to sheet 4 with the new date. That indicates that he's paid the $75.00 fee on two seperate dates.

Here is the macro that I currently use:

	VB:
	
 
Sub CopyAccounts() 
     
    Dim WSData As Worksheet 
    Dim WS1 As Worksheet 
    Dim WS2 As Worksheet 
    Dim WS3 As Worksheet 
    Dim Dest1 As Range 
    Dim Dest2 As Range 
    Dim Dest3 As Range 
    Dim R As Range 
    Dim N As Long 
    Dim FoundCell As Range 
    Dim LastCell As Range 
    Dim FirstCell As Range 
    Dim First1 As Range 
    Dim First2 As Range 
    Dim First3 As Range 
    Const COPY_NUM_COLS = 4 '

Hello,
Since I am relatively new to the world of VBA, I apologize in advance if this question seems redundant.

I have a row of data in one part of my worksheet. I am trying to add data to a specific range elsewhere in the same sheet based on what color each of the cells in this row are. If the cell is one color, the data will get placed in one range; if it is another color, the data will get put into another range. However, these are all in the same worksheet because I am writing code to automate a report.
My problem is that I want to write my If, Then loop for adding this data to include finding the next blank cell in the specific range and then pasting the data there. The ranges will never change, but I am still coming up with different errors when I try to write the code to the next blank cell in that specific range and to then write my value to that cell. I have enclosed my code thus far below. I have tried to incorporate a few methods that I have found posted on these forums for similar situations, but my relative newness to VBA is not helping. Right now, I am trying to give the range a variable name and then to have the cells search that range only. However, I don't think I am having much success.


	VB:
	
 basPasteRating() 
     
    Dim RV1 As Range 
    Dim RV2 As Range 
    Dim RV As Range 
     
    Set RV1 = Range(12, 1) 
    Set RV2 = Range(19, 1) 
    Set RV = Range(RV1, RV2) 
     
    Workbooks("JCPT").Activate 
    Cells(32, 1).Select 
    If Cells(32, 1).Interior.Color = RGB(204, 255, 204) Then 
        Worksheets("JCP").Select 
         'I want the macro to search this specific range and then to end on the next blank cell and select it. That way, I
can add the data value I want. The specific range is Range(Cells(12,1),Cells(19,1))
        Cells.Find("", [RV], x1formulas, x1whole, x1columns, xlNext, False, False).Select 
        Selection = "DO" 
    Else 
        If Cells(32, 1).Interior.Color = RGB(255, 255, 153) Then 
            Worksheets("JCP").Cells(21, 1) = "DO" 
        End If 
    End If 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Any help is appreciated! Or, any resources you can point me to.


No luck finding an answer? You could always try Google.