Free Microsoft Excel 2013 Quick Reference

adding data to the next row

I get data from 1 sheet and add that data to column a1.. I then open another spreadsheet and get data and want to add it to the next row down in a1. The problem I have is that its writing over the current data in a1. Please help me move to the next cell. Thanks, Brian


Hello,

In the Sub GraphRoutine I want to copy H5:J5 (the numbers are always changing) and paste it as a value in another sheet on a different row every 30 seconds. But the macro puts the data in cells(6,2) and it does not advance to the next row even though kk = kk + 1. What am I doing wrong? My macros are below:

Sub StartGraphRoutine()

Dim WhenToRunA As Double

WhenToRunA = TimeValue("08:30:00")
Application.OnTime EarliestTime:=WhenToRunA, Procedure:="GraphRoutine", Schedule:=True

End Sub

Sub GraphRoutine()

Dim kk As Integer

Worksheets("Sell_Orders").Range("H5:J5").Copy
Worksheets("Sheet1").Cells(6 + kk, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _:=False, Transpose:=False
kk = kk + 1

On Error Resume Next

StartTimer

End Sub

Sub StartTimer()
Dim WhenToRun As Double
WhenToRun = Now + TimeSerial(0, 0, 30)
Application.OnTime EarliestTime:=WhenToRun, Procedure:="GraphRoutine", Schedule:=True
End Sub

Sub StopTimer()
On Error Resume Next
Application.OnTime EarliestTime:=WhenToRun, Procedure:="GraphRoutine", Schedule:=False
End Sub

Hi

I have the following code that moves down to the next row,

TestRow = range("A1").select
fin_Row = Activecell.SpecialCells(xlLastCell).select

Do While TestRow

HELLO EVERYONE!! I built a Userform to enter information to a spreadsheet. The problem I am having is the info will not always go to the next available empty row. It comes back to the first row after any data is entered in the 3rd or forth column. Its driving me crazy and I new at writing codes. Here is what me code looks like:

Private Sub CmdOK_Click()
Dim LastRow As Object

Set LastRow = Sheet1.Range("a65536").End(xlUp)

LastRow.Offset(1, 0).Value = txtRA.Text
LastRow.Offset(1, 1).Value = txtJB.Text
LastRow.Offset(1, 2).Value = txtRH.Text
LastRow.Offset(1, 3).Value = txtGD.Text
LastRow.Offset(1, 4).Value = txtGK.Text
LastRow.Offset(1, 5).Value = txtAS.Text
LastRow.Offset(1, 6).Value = txtCJ.Text
LastRow.Offset(1, 7).Value = txtNQ1.Text
LastRow.Offset(1, 8).Value = txtNQ2.Text
LastRow.Offset(1, 9).Value = txtNQ3.Text
LastRow.Offset(1, 10).Value = txtA.Text
LastRow.Offset(1, 11).Value = txtCC.Text
LastRow.Offset(1, 12).Value = txtC.Text
LastRow.Offset(1, 13).Value = txtDTV.Text
LastRow.Offset(1, 14).Value = txtFE.Text
LastRow.Offset(1, 15).Value = txtNSTAR.Text
LastRow.Offset(1, 16).Value = txtNYSEG.Text
LastRow.Offset(1, 17).Value = txtCAP.Text
LastRow.Offset(1, 18).Value = txtPC.Text
LastRow.Offset(1, 19).Value = txtSCG.Text
LastRow.Offset(1, 20).Value = cboPROJECT.Text
LastRow.Offset(1, 21).Value = cboMONTH.Text
LastRow.Offset(1, 22).Value = txtNAME.Text
LastRow.Offset(1, 23).Value = txtMONITOR.Text
LastRow.Offset(1, 24).Value = cboINCIDENT.Text
LastRow.Offset(1, 25).Value = cboSUP.Text
LastRow.Offset(1, 26).Value = txtCORRECTED.Text

MsgBox "One record entered"

response = MsgBox("Do you want to enter another record?", _
vbYesNo)

If response = vbYes Then
txtRA.Text = ""
txtJB.Text = ""
txtRH.Text = ""
txtGD.Text = ""
txtGK.Text = ""
txtAS.Text = ""
txtCJ.Text = ""
txtNQ1.Text = ""
txtNQ2.Text = ""
txtNQ3.Text = ""
txtA.Text = ""
txtCC.Text = ""
txtC.Text = ""
txtDTV.Text = ""
txtFE.Text = ""
txtNSTAR.Text = ""
txtNYSEG.Text = ""
txtCAP.Text = ""
txtPC.Text = ""
txtSCG.Text = ""
cboPROJECT.Text = ""
cboMONTH.Text = ""
txtNAME.Text = ""
txtMONITOR.Text = ""
cboINCIDENT.Text = ""
cboSUP.Text = ""
txtCORRECTED.Text = ""

txtRA.SetFocus

Else
Unload Me
End If

End Sub

Can some please help!! You can also email me at chukujones234@msn.com

I have data in 50 columns and 30 rows.
I would like to know how to either copy or move the data in the next columns
to the next row of the previous column without using "copy and paste" because
there are too many columns and I think there is a better way to do this.

I want to have only one column combining all data from 50 different columns
into one column.

So it looks like this after moving or copying the data from columns into one
column.

column A
------------
A1
....
A30
B1 ( data in the column B is now in the next row in the column A)
....
B30
......
....
AX1 ( data in the column AX is now in the next row in the column A)

....
Ax30

Thank you
Jim

I have data in 50 columns and 30 rows.
I would like to know how to either copy or move the data in the next columns
to the next row of the previous column without using "copy and paste" because
there are too many columns and I think there is a better way to do this.

I want to have only one column combining all data from 50 different columns
into one column.

So it looks like this after moving or copying the data from columns into one
column.

column A
------------
A1
....
A30
B1 ( data in the column B is now in the next row in the column A)
....
B30
......
....
AX1 ( data in the column AX is now in the next row in the column A)

....
Ax30

Thank you
Jim

Hi. I have a problem. I've attached an example to this thread for anyone to look at. My problem is this. I work in a department that wants me to manually enter data into cells denoting the hour it was input for that department on that day it was entered. The same goes for three other departments. There are 4 departments total. The spreadsheet is listing 24 columns from 0 to 23. Each column header refers to one hour. Every empty cell below is where I enter the data matching the department that is using that hour for the day. So each department does something for that day and this report I enter lists the actions for that department for the next 30 days. Each day I input that data into the day the work was done for each department stretching to 23 hours listed on a single row. This is causing me to get headaches and cross eyes because there's no way I can be sure I'd enter the correct information until it's all entered for the day and tallying up the totals to see if they match.

I need a better and quicker way to input these values into this spreadsheet by helping me generate a macro that allows me to enter the values for each department for that day and then after entering the information, the macro copies the data to the appropriate row and then clears the input row allowing me to input new data for a different department for that day and then repeats the same action. This would allow me to simply enter the data without double checking to make sure I input it correctly on the right row where I now have to enter manually.

I have Excel setup so that when I press "Enter" I move to the next column.

However, I would like to set up my spreadsheet so that when I hit "enter" in the last column of data, it moves to the next row down.

i.e.

I have data I need to enter in Row 1, columns A-L.

I enter data in A1 and hit "enter" and I move to cell B1 and so forth, always moving right. When I get to cell L1 however and input my data and hit "enter" I would like to be moved to cell A2.

Is this possible?

I have the following code done. The user wants to be able to push the "Flat"
button as many times as needed to add more information. Every time I loop it
I rewrite over my initial data. How do I make it skip down to the next row?
Also I want the user to have to choose yes or no to continue to loop.

Private Sub Flat_Click()
Dim sreturn As String
sreturn = InputBox("Enter start date dd/mm/yyyy 24hr clock")
[A11] = sreturn
sreturn = InputBox("Enter stop date dd/mm/yyyy 24hr clock")
[B11] = sreturn
sreturn = InputBox("Enter TSN to increase")
[C11] = sreturn
sreturn = InputBox("Enter TSN to decrease")
[D11] = sreturn
sreturn = InputBox("Enter MW Amount")
[E11] = sreturn
End Sub

Hi,

I need a way to compare to cells in a row and then move to the next row.

For example, in the table below I have two rows where ColumnA contains the string "approved" and ColumnB is empty. The count for this should be "2".

There are 3 rows where ColumnA contains the string "in progress" and ColumnB contains the string "completed". The count for this should be "3".

ColumnA ______ColumnB
1 approved
2 in progress__completed
3 approved
4 in progress__completed
5 in progress__completed

My sheet has 50 rows.

Thank you very much!!!!

Hi again

Sorry for this easy question but I am new in VBA.

How to get my cell to the next row if it reachs the border of the defined range?

Dim myCell As Range
Dim myRange As Range
Dim rowsCnt As Integer
Dim ColumnsCnt As Integer
Dim i As Integer
Dim j As Integer

Set myCell = Range("G6")
Set myRange = Range("G6:M15").CurrentRegion
rowsCnt = Range("G6:M15").CurrentRegion.Rows.Count
ColumnsCnt = Range("G6:M15").CurrentRegion.Columns.Count

For i = 0 To rowsCnt

For j = 0 To ColumnsCnt - 1

Set myCell = myCell.Offset(0, 1)
myCell.Select

Next j
Next i

Please advise
Thank you

I have rows (B20:G20) to (B24:G24) merged. I want to type text in B20 and when it gets to the end of G20 to automatically flow into B21; and when it gets to G21 to automatically flow into row (B22:G22) and so on.

I can press enter at the end of lets say G20 and go to B21 but I have to be careful to limit the end of the text so it fits in B20:G20 so I want to automate this.

Basically I want a word wrap to the next row at the end of cell column G.

Every month I update links in a particular work book. The formulae need to link to the next row in the linked workbook (e.g. in May a cell links to [some or other workbook]$C13 and in June to [the same workbook]$C14). Is it possible to automate these updates?

I can't just replace because different cells link to different rows.

Hi Guys
In cell "C2" I've created a Conditional Formatting based on B2.

How do I apply the same rule to the next row?
So, in "C3" I'll have the rule based on what happen in "B3".

(I have more then 60 rows "C2:C66", and doing it manually makes me fall a sleep on my keyboard).

Any suggestions?

Thanks

please help,
i need a macro to copy a specific columns (A to F) and the last not null row to the next row with formulas, its like copy a1 to a2 but a1 have the value of PO.01 and i need to increment it to PO.02. while the other columns are formulas and need to be copied. attached is my sample file

thanks,

Hi All -

I need a macro that will copy the existing row let's say A10 to the next row A11 - FORMULAS AND ALL. Here is the catch the row it just copied should remain, but it needs to remove the formulas from it. It just needs to retain the values that are currrently in the cells.

Anyone ever encountered such a task?

thanks

I decided to sign up and join this forum since there are so many knowledgable people here. Im not the best Excel user. I currently use Excel 2007 and Im still learning the basics and what not. Anyways, I recently purchased a WASP WWS450 Barcode Scanner to help me with Inventory of our warehouse. I have a ton of banker boxes filled with Patient Records from a healthcare facility. Each patient folder contains a coversheet with barcodes Ive created. I can get my Scanner to input info into the cells but once I am done scanning the barcodes, I need to redirect the cursor onto the next row. For an example, I have 3 column fields to scan into (Patient Name, Patient Number, and DOB)... The "Box Number" will be manually inputed by myself.

Patient Name | Patient Number | DOB | Box Number

Here's another way to look at it.. My 3 barcode fields will scan and input them in A2, B2, C2. After the A4 cell, I need it to then go to the next row...A3, B3, C3.

A patient's folder will contain a cover sheet with barcodes on them. First I would scan these barcodes in this order... Patient Name>Patient Number>DOB. After scanning the DOB, I want the cursor to move to the next row and start from Patient Name and repeat. Ive tried unprotecting/protecting the spreadsheet and also changed Excel options for "Move selection" but nothing worked. Could someone please help me solve this issue ? Again, I am a total newbie with Excel so any detailed advice would be grealty appreciated. I will attach my spreadsheet that I made. Thanks in advance guys !

InventoryTest.xls

Hello All,

I am attempting to copy data from a sheet on one workbook to the next empty row of another sheet on another workbook. I have the macro to simply copy and paste it, but it always copies over the content.

Also, just as a side note, is there any way to run the macro, so the workbook with the original content doesn't have to be opened?

Here's the code I have so far...

Sub()
Windows("Example1.xls").Activate
Worksheets("Sheet1").Select
Cells.Select
Selection.Copy
Windows("Example2.xls").Activate
Worksheets("Sheet1").Select
Cells.Select
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats, Operation:= _
xlNone, SkipBlanks:=False, Transpose:=False

End Sub

Thank you for your help!

Hi,

I have the following columns A, B and C.

A B C
123 b x

455 b x
789 b x
672 b x

143 b x

---------------------------
Say something is in Col A (but not on every row). But in some instances
there is data in Col A one row after the next. How do I move from one
row to the next ( in Col A ) and not jump over any rows with data.

This is the code I currently have:

Do While LoopRw

Enclosed file showing data recorded to next column for each individual profile for analisys. Each profile records five elements (A,B,C,D,E).

Instead to record to the next column for the next profile I would like to add to the latest row in col A with the five records and so on.

At the end I want 5 columms with all profile added to previuos one. I have only put few profiles, at the end 70 profiles have to be anaised.

If not clear please let me know.

Regards,

Thierry.

Hi,

I've got a spreadsheet at the moment where a lot of data has to be entered on a daily basis. To combat this I have created a main data tab(To clarify, this is seperate to the tab which the final data will be displayed on) and have created a range of formulas which run across the range (C:CC) The data is updated via a copy and paste method daily and is automatically pulled thorugh via said formulas. Some users aren't particularly Excel savvy and therefore I want to automate the process of copying the formulas down to the next row and pasting the original values into the cells which previously contained the data therefore retaining the values.

I'm looking to incorporate a macro and attach it to a button which will do the following:

Locate the last row in which figures were entered
Copy the formulas in these cells down (C*:CC*)to the next set of blank cells
Copy and paste special the original row to retain the values whilst also removing the formulas.

The formulas will then continue to function normally and will pull through the next days figures once entered into the main data tab.

I hope this is clear but if need be I could probably cut the spreadsheet down to size to provide an example.

Many Thanks in advance.

Mark

I have designed a VB program that’s takes data from 8 text boxes with a submit button it opens excel workbook creates sheet1 and then it inputs all the text boxes in row1 across 8 columns. What I want to do is when I submit another person’s data from my program I want it to go to the same work book and the same sheet and place that data in the next empty row.

Thanks

I am running excel 2003 and have a worksheet spanning several columns. I want to be able to copy all the formulars in the last row with data in my worksheet to the next empty row(s) and maintain my totals at the bottom. Can any one help?

Godfrey

Hi everyone.I have a data that updates once a day. I want Excel to take a copy of each new bit of data as it comes in, and copy and paste it into a column on the same sheet so that it can be stored. I then want it to move down a row and take the next reading and so on, so that each time a new reading comes in, Excel takes the value and puts it in the next row down in a set column.Any suggestions out there??
Thanks a lot.
here is the code:

 Sheets("DDE").Select
    ActiveWindow.ScrollColumn = 7
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 15
    Range("T23").Select
    Selection.Copy
    Sheets("data").Select
    Range("C299").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("DDE").Select
    Range("V23").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("data").Select
    Range("B299").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("DDE").Select
    Range("U23").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("data").Select
    Range("E299").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("DDE").Select
    ActiveWindow.ScrollColumn = 14
    ActiveWindow.ScrollColumn = 13
    ActiveWindow.ScrollColumn = 12
    ActiveWindow.ScrollColumn = 11
    ActiveWindow.ScrollColumn = 10
    ActiveWindow.ScrollColumn = 9
    ActiveWindow.ScrollColumn = 8
    ActiveWindow.ScrollColumn = 7
    Range("J25").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("data").Select
    Range("D299").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub


I recorded a macro to refresh row 1 sheet 2 from an external data source and copy the data to sheet 1. The data will change frequently and I need the data from row 1 sheet 2 to paste to the next (blank) row in sheet 1. I'm attempting to keep the user from having to manually select the next available row prior to running the macro.

Sheets("Sheet2").Select
    Selection.QueryTable.Refresh BackgroundQuery:=False
    Rows("1:1").Select
    Selection.Copy
    Sheets("Sheet1").Select
    ActiveSheet.Paste