Free Microsoft Excel 2013 Quick Reference

Getting Macro to paste into next empty cell

Hi,

I'll say right off the bat that I have very little experience programming. I do have an aptitude for it, but that doesn't get you very far without training .

Here is my question/situation:

I have made a macro (using the record button) that copies information from particular cells on sheet 1 and copies them to cells on another sheet called "tracker". It works just like I told it to, but now I want it to check if the cell is occupied and if so, then paste the information on the next row. Can any of you guys help me with that? Here is the code which I copied from the macro I created.

Sheets("Sheet1").Select
    Range("N12").Select
    Selection.Copy
    Sheets("Tracker").Select
    Range("b2").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Sheets("Sheet1").Select
    ActiveWindow.SmallScroll Down:=12
    Range("B29").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Tracker").Select
    Range("C2").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Sheets("Sheet1").Select
    Range("N34").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Tracker").Select
    Range("D2").Select
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Thanks in advance for any help you guys can give.


I am running this macro or some variation of it, depending on the column I need the time entered into:

Sub MacroD()
Dim LR As Long
LR = Range("D" & Rows.Count).End(xlUp).Row
ActiveSheet.Unprotect
Range("D18:D" & LR).Value = Now
ActiveCell.Offset(1, 0).Select
UserForm1.ListBox1.Text = "Time"
UserForm1.ListBox1.SetFocus
' UserForm1.Show
End Sub

When I need to run the same macro again (say I've run Macro D once, and now I need to run it again to get the next time), the forumla overrides the previous timestamp. I need the macro to find the next empty cell in column D and enter the timestamp there.

Can anyone help? Thanks!!

Sheet1

*ABCDEFGHIJKLMN15Major EventMajor Event Clock TimePUSH#########SPREAD########OUT FWD########REV########OUT REV########Delay########1617Start10:55:09*10:55:27 *10:56:24 *10:56:26 *10:56:19 *10:56:28 ####10:55:59 18W-U10:55:180.40010:56:23 0.01710:56:24 0.03310:56:26 0.01710:56:27 0.01710:56:28 0.03310:56:30 19MTT10:55:26######*0.000*0.000*0.000*0.000*0.000*20**0.000*0.000*0.000*0.000*0.000*0.000*21****0.000*0.000*0.000*0.000*0.000*
> Excel Jeanie HTML 4

HI EVERYBODY.

i need help in COPY FROM SHEET TO ANOTHER SHEETS NEXT EMPTY CELL..

sample code

Sheet1

1500 11114
1501 11115
1502 11117
1502 11119
1603 11118
1410 11115
1454 11114
1012 11113
4141 11112

i want the above data copied from sheet1 to sheet 2 each time i run the macro to the next empty cell

sample of Sheet2

1500 11114
1501 11115
1502 11117
1502 11119
1603 11118
1410 11115
1454 11114
1012 11113
4141 11112
1500 11114
1501 11115
1502 11117
1502 11119
1603 11118
1410 11115
1454 11114
1012 11113
4141 11112
1500 11114
1501 11115
1502 11117
1502 11119
1603 11118
1410 11115
1454 11114
1012 11113
4141 11112
1500 11114
1501 11115
1502 11117
1502 11119
1603 11118
1410 11115
1454 11114
1012 11113
4141 11112
1500 11114
1501 11115
1502 11117
1502 11119
1603 11118
1410 11115
1454 11114
1012 11113
4141 11112
1500 11114
1501 11115
1502 11117
1502 11119
1603 11118
1410 11115
1454 11114
1012 11113
4141 11112
1500 11114
1501 11115
1502 11117
1502 11119
1603 11118

Help guys

Regards tikku

I know this isn't efficient but I am able to accomplish what I need except for one piece. I am copying a specific cell from one spreadsheet into the first empty cell of a specific column on another. With this code I get an error on the line just before I paste where I was hoping to select the first empty cell in the column.

Note: This is just the section of code that is problematic. The variables for the workbooks (Wk1 and Wk2) are setup and working before this point.

  
    Windows(Wk2).Activate
    Sheets("Sheet1").Select  
    Range("C14:C14").Select
    Selection.Copy
    Windows(Wk1).Activate
    Sheets("Sheet1").Select
    ' Next line is the broken peice
    Range("B1").End(xlDown).Offset(1, 0).Select 
    Selection.PasteSpecial Paste:=xlValues
Any help would be much appreciated.

Good evening.
I have a workbook with two sheets called "manuf" and "printsh". A Find Macro finds a numeric or alphabetic string (cell A1 in manuf) in column A and/or column B in that sheet. The sheet has 4 columns (A:D) with info. Once found, I want to have the option to copy the first four cells (A??:D??) to cells A3:D3 in sheet "printsh". If cells A3:D3 have something in them, select the next row down and if they have something in them, select the row below that etc etc.
The following macro does it but I like to get rid of the "select" and "activate" in the macro.


	VB:
	
 Transfer() 
    Application.ScreenUpdating = False 
    If IsEmpty(ActiveCell) Then Exit Sub 
    On Error Resume Next 
     
    If IsEmpty(ActiveCell.Offset(0, -1)) Then Set LeftCell = ActiveCell Else Set LeftCell = ActiveCell.End(xlToLeft) 
    If IsEmpty(ActiveCell.Offset(0, 1)) Then Set RightCell = ActiveCell Else Set RightCell = ActiveCell.End(xlToRight) 
    Range(LeftCell, RightCell).Copy 
     
    Sheets("printsh").Activate 
    ActiveSheet.Range("A3").Select 
    If IsEmpty(ActiveCell) Then 
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
        :=False, Transpose:=False 
    Else 
         
        Do While Not IsEmpty(ActiveCell) 
            ActiveCell.Offset(1, 0).Select 
        Loop 
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
        :=False, Transpose:=False 
    End If 
     
    Sheets("manuf").Select 
    Application.CutCopyMode = False 
    Application.ScreenUpdating = True 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
From what I have read in this forum, this is bad programming. The part to
find the next empty cell is not the nicest either.
As is obvious, I am not an accomplished VBA man by any means.
Any help and/or changes are very much appreciated.

Thanks and regards
John

Hi

I have set up a macro which copies a cell range then goes to another workbook, opens a new sheet and pastes in cell A1.

It works fine but what I really want to do is paste all copies in the same worksheet.

ie. The first paste was cell A1 and goes down to A10, I want the macro to paste the next set of data in cell A12.

Is this possible?

Hello..
I m using a macro that copies a row from sheet 1 and paste it into sheet 2. The problem is that I need to paste the new values to the next empty row to create a list. The macro however paste into the initial row replacing the previus values.. What can I do?

Hi,

I have done a search but I keep getting a eroor for results so I hope someone can post me a quick helful reply.

I have a qorkbook. I will already have the row highlighted, then activate this macro

Sub
Tran2Hist()
'
' Tran2Hist Macro
'
    Selection.Copy
    Sheets("HistRec").Select

    ' Code to find the next empty cell in column A so I can paste in the data on the clipboard  

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("db").Select
    Range("D19").Select
    Application.CutCopyMode = False
    ActiveWorkbook.Save
End Sub
I need a quick bit of code to locate the next empty cell in column "A" so I can then continue with the code by pasting data in.

Thanks for the help in advance.

Firstly let me thank all who have helped me so far with my project

Now I am using update to produce running sheets, I need to be able to update the first available cell in a number of blocks containing rows.

Block 1
A1 Data
A2
A3
A4
A5
update into the next empty cell (A2)

Block 2
A6 Data
A7 Data
A8 Data
A9
A10
Update into next empty cell (A9)

Thanks
Chaz

Hope I can explain this properly. I want to copy data (text) from one page, e.g (Quantum!H54) onto another page (SOW) so it copies into the next empty cell of a range. e.g. (H18:H60).
say there is already data copied in the "SOW" page in cells H18 & H19 I want it to copy into cell H20 rather than cell H54.

Hi,

I was hoping you could help me with a relatively simple query.

In one sheet (eg Sheet1) I have columns populated with information in them from cols A to E. In another sheet (eg Sheet2) I have values in a column. On a weekly basis I want to use vb to copy the values in the column in Sheet2, and paste these values into the last empty column in Sheet1. ie Column F.

I will do this on a weekly basis, each week deleting and replacing the values in Sheet2. So the next week I will run the macro to copy info from sheet2 and paste into next empty column, column G.

Ive done something similar a while ago, but could use some help with some lines of code - including whether you use the End.xltoLeft or Offset (0,-1) commands in the macro....

I hope this makes sense, and any help would be appreciated.

TD

Hi Folks,

I would really appreciate some help to do the following if possible. I have to have it ready for work tomorrow morning. I have completed most of the project except for this bit.

I have a command button set up and would like the code for the following:

I want to copy the range "A2 to H6"

Then starting from cell "A5" downwards i want to find the next empty row, in that empty row paste the copied range.

Any help would be appreciated.

Thanks

Hi all

How can I restrict text in a cell to that cell and prevent it from displaying into the next empty cell(s) to the right. Or can't I?

Searched for an answer but couldn't find one.

Thanks

Dave

I have data on the clipboard which I want to paste into multiple cells in a
column that is selected. When I Ctrl+V, it only pastes into the first cell.
How can I get it to paste into all the cells that I have selected?

Thanks . . . Walt

have two columns and column B want to paste special with skiping empty
cells into column A (in column A where need to paste there ARE empty
cells) and my excel 2003 paste it all as normal paste! don't get it
what is problem but i'm sure before was working this fine!?

example:

Column A Column B

1,80 - 1,90

2,3 - 2,4
1,50 - 1,60
1,9 - 2,0
2,3 - 2,4
1,40 - 1,50

2,1 - 2,2
1,70 - 1,80

so this what is in B want to transfer into A but not to delete present
data in A!?

Hi,
I'm trying to get a macro to grab a range of cells from one worksheet and paste them into another one. I want the macro to choose between 4 different sets of data based on a single cell value (1,2,3, or 4). I have previously used =choose to do this however my model is going to run very complex sensitivty calcs and I don't want to waste computer grunt on 500 cells having =choose statements.

I have attached an example of my sheets, and I want the data to paste into the orange cells when I click the button.

I don't want to use excel's scenario manager, I want to do it using a macro, or some other clever way that anyone can think of.

I am quite inexperienced with VBA in the multi worksheet & global variable setup type stuff.

Any advice greatly appreciated, Cheers!

Hello guys,

I will appreciate very much if you can help me with the following
situation:

I have about 120 Excel files and one other file, let's call it the
Source File.

I need to copy the contents of one cell from the Source file into each
one of the 120 files and also do the same with a text box.

It will be necessary anyways to go manually through each of the 120
files and check them so I was trying to make a macro that will paste
into the active cell the contents of, let's say, cell B40 from the
Source file. Then I was planning to do another macro for the text box,
ie to paste in the active cell (which will be a different one) the
textbox from the Source file. (I will click manually the active cell).

Because of the two (or may be three) things that need to be pasted in
each of the 120 files I can't just paste the clipboard.

So I was trying to do this macro using the recorder (I know nothing
about code) The problem is that it works only for the file it's
created in. If run in another file it gives an error (Runtime error 9:
Subscript out of range) and stops on the 5-th line that has the name of
the file it was created in("CO23df_pds.xls"). Here is the code:

Sub Macro6()
'
' Macro6 Macro
' Macro recorded 6/9/2006 by LARRYB
'

'
Windows("Source File.xls").Activate
Range("B40").Select
Application.CutCopyMode = False
Selection.Copy
Windows("CO23df_pds.xls").Activate
ActiveCell.Select
ActiveSheet.Paste
End Sub

It looks to me it's not pasting in the currently active cell but is
trying to paste in the file it was created in.

So as I have 120 of these, my question is how to make a macro that
pastes in the active cell the contents of a given cell from another
file (in my case B40 from Source file)???

Thank you in advance for all help - it is really appreciated !! Also
please explain in a simple way as I've never worked with any code...
:o)

Regards,
Larry B

Hi Gang,
This has to be so simple that I'm just missing it
I have a command button that opens a sheet based on inputbox
Then I would like for it to locate in column D the first empty cell.
Code:

Private Sub CommandButton1_Click()
Dim Message, Title, MyValue

Message = ("What Type Of Case?" & Chr(10) & "CA, CR, DR...etc." _
& Chr(10) & "Be sure to type HC, or FD first")
Title = "New Case Log"
MyValue = InputBox(Message, Title)
On Error Resume Next
Sheets(MyValue).Select

Range("d7").End(xlDown).Offset(1, 4).Select

End Sub
Tried : Range("D7").end(xldown).offset(1,0).select
and .activate
The sheets open, but not getting it to goto the first empty cell in the column.
What am I missing
Thanks gang,

Hi, I would like to select the next empty cell in columb A after running this macro, what code do I need to add to this to enable that please?

Thanks
Chris

Sub Macro2()
'
' Macro2 Macro
' Macro recorded 12/01/2010 by chris walker
'
' Keyboard Shortcut: Ctrl+q
'
    Sheets("Room Template").Visible = True
    Sheets("Room Template").Select
    Rows("1:18").Select
    Selection.Copy
    Sheets(" Estimate").Select
    
    Selection.Insert Shift:=x1Down
    Sheets("Room Template").Visible = False
        
End Sub


Hi I am trying to create a macro to paste items that I have in excel into a Powerpoint Presentation. Can someone tell me what the code would be to bring up a blank presentation and paste into it?

Thanks in advance!

Good Day,

I am having trouble with a code that would allow me to select the next empty cell. The code is as follows:

It works except for the fact that it starts at cell A2 instead of A1. I have searched and googled, and I know it's probably
something really silly that I do not know how to do. Any suggestions?

Thanks a million.

i want to paste data in alternate cells. e.g. i have following 2 columns:
A B
PWV3 DIGOUTHG
ILA3 DIGOUTHG
ILB3 DIGOUTHG
PDE3 DIGOUTHG
ILC3 DIGOUTHG
QWR3 DIGOUTHG
PWV4 DIGOUTHG
ILA4 DIGOUTHG

I want to paste PWV3 in row 1 and 3 of column B. Then I want to paste ILA3
in row 2 and 4 of column B...and so on....it is tedious to do it for large
numner of rows. Can we write macro for this...

thank you...

-Pawan

Please help me this...... how to make excel to found the next empty cell and to put a number there from a list of data. The attached file is a sample of what I want to do: It is a "Perfect Attendance" spreadsheet. I have 3 tables: Table 1 (green) is the data. Table 2 is the entry of the employees who called off (Yellow). Table 3 is the result I am looking for (red). I need excel to put the names (or employees #) of the people who have perfect attandance. I can not use codes, macros or pivot table. I have to use stright formulas. Thanks a lot!!!

I need to find the number of row of the next empty cell in a column.

For example in Column A I have

1 AA
2 BB
3 CC
4

I like to create a formula that can take in the column index and return a row number. In this case Column index:A, Answer Row = 4. I did it using VBA, but I can't seem to do it using a formula. The reason for this is that if a user use my spreadsheet and add a row in column A I need to update the next empty cell row number 5 as soon as the user type in anything in Cell A4.

Any help is greatly appreceiated.

Thanks

Is it possible to create a macro to paste into a row with a corresponding name.

In my case...I have two pages of baseball stats. One is the current game's stats and page two is the League's Total Stats.

Is it possible to copy the stats from page one onto page two and paste them with the corresponding team?

What I'm trying to do is create one button that quickly copies over the stats for each player so I do not have to manual do it, which takes a long time each to day, going up and down the list of page two trying to find each player.