Free Microsoft Excel 2013 Quick Reference

Running a macro and making it paste into the next blank cell.

I have a problem with a macro that I have used in my spreadsheet.

The problem is that when I use a macro I have created to copy and paste cells onto another sheet it is constantly overwriting the data when it needs to be constantly adding new information every time that the button is clicked, I need the button to paste below what has already been pasted when the button has previously been clicked.

My question basically is:
What coding do I need to insert into the macro to make it paste into the next available blank cell and not overwrite what has already been done?

Thanks,
Connor


Post your answer or comment

comments powered by Disqus
Hey
i need some VBA code or any other way of copying data from one cell and pasting it to the next blank cell down in a selected range of cells. It only has to be one cell wide and the column i want it to be coppied into is only one cell wide also.

Thanks
Amit

Hi everone,

I was wondering if anyone could help me with a problem I'm having...

I am trying to write some code so that I can paste a selection of text (B4:B11on sheet1) into cell the first blank cell in column B on sheet2, rather than specifying exactly which cell it is posted into.

If anyone could shed some light onto this problem I would be extremely grateful!

Thanks,
Alex

I have an Inventory database that i use for work and i have been using formulas for keeping up with all of it. Currently when issuing out an item i have a sheet printed then I use a barcode scanner to scan them into the database. If this is not done daily it get tedious with a barcode scanner. i would like to setup a seperate sheet that i can put all of the inventory items on the sheet with buttons beside. This button i would like to copy the name date serial # and qty into the database sheet and for it to goto the next blank row. I have tried to search for something like this with no luck and i know formulas but i do not know VBA. I tried to record my own macro and it partially worked i just could not figure out how to goto the blank row. Any help would be awesome.Inventory.xlsx

I'm new into macros, In my job I have a database which is captured everyday. My macro cuts the captured data and paste it to another workbook, in this workbook i keep all the records so i want that the next time data is captured and the macro run, data to be copied into the next empty cell in my workbook.

Thanks!!

Hi guys, I could do with some help with VBA Code

I have a excel file in which data gets inputted, and then based on this data excel will match some text in the cell to the right from a mapping tab. If excel can't find a match for this data, I get an #N/A error. So I want to search column V and If I get this error, I want to copy the data in the cell to the left of the #N/A error (Column U) and paste it into the first empty cell in column A of another tab.

So basically, I want to search column V for #N/A Errors, if there is an error, copy the cell to the left of the #n/a error and paste it into the first empty row of another tab.

Thanks guys, hugely appreciated!

I would like to use a Command Button on one worksheet to initiate a macro that will copy data from that worksheet and paste the data onto another worksheet in the same workbook. As this may be used multiple times, the data should be pasted into the next empty cell in the column specified.

I have only a basic knowledge of VB and I wonder if a loop may be required but I do not know how to write it correctly. Any help would be very much appreciated. Please let me know if any additional information is needed.

Thanks much and happy coding!

-Ryan

I have read many articles on how to search for empty excel cells. but none really help.

what i need is my company recieves a data every week. i want to be able to grab the data from another sheet called "DATASHEET" and past them into our stored data sheet called the "STOREDDATA"
the amount of data though is not on cell it is a numerous amount of cells which have spaces inbetween and is all over the place.
what i tried doing was running an autofilter macro and pasting into the next blank cell. all i get is 1 cell being pasted in the blank and the rest repasted on each other.
i have data in "STOREDDATA" that i want stored for all time. the dat is from E2 to V7. The next blanks are just below in E8 to V12.

so just to recap i want it to
1. grab the "SELECTED" info i need from DATASHEET
2. Paste it into the next balnk cell which is current E8.
This will be an ongiong thing so if i can get it to always store in the next blank cell would be appreicated

PLEASE SOMEONE HELP.

I NEED THIS ASAP OR IM IN TROUBLE FROM THE BOSS

I'm trying to copy a set of rows from one tab (Sheet2) and paste them into the next blank row on another tab (Sheet1). I've recorded the following macro, but I'm not sure how to edit so the cells are automatically pasted to the next blank row in Sheet1.

Sub CopyTemplate()
'
' CopyTemplate Macro
'

'
    Rows("4:36").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Sheet1").Select
    Range("A136").Select
    ActiveSheet.Paste
End Sub


hello. I'm trying to make a macro to find a specific text, and if not available, this same text to be inserted into the first blank cell.
The macro to find Is already inserted in the project, now I just need the part of in case does not find the text, paste it in the first blank cell.
what I want to do is place the text, not found in the first blank cell in column A. in this case line 7 of column A.
For example: if you do not find the word "line" in column A, the macro would paste that word in the blank line immediately below. in this case line 7.

first of all thanks for the help.

Hello, i need a little help with coding a macro for my workbook, am a bit of a novice at this and am still learning all the Functions of VBA.

I need the VBA code to be able to copy specific data from cells in the selected active row in the one sheet to a specific range of cells in another worksheet without pasting over existing data.

For example:

Sheet 1: I select Rows "1, 2 & 3" As Active rows, i want the data from cell "F" of each of these rows to be copied and pasted to Sheet 2 into the Range of cells A42 to A59.
Now what want to happen is for the data in (sheet 1) Row 1, F to be pasted into (sheet 2) cell A42, then the data from (sheet 1) rows 2, cell F and 3 cell F to be pasted in the next blank cell below.

Basically what i have is a Goods In booking system of which i want to create a Delivery note for Goods Out, i want to take the data from each active row i select and to put it into the Range of cells shown above, so that each item is listed one below each other descending in column A starting in Cell 42. I have tried to code it myself, and can only get the data to be pasted into individual separate cells rather than pasting the data in the next blank cell below in colum A.

Here's my try at it:


	VB:
	
 IMPORT() 
    Sheets("Sheet1").Activate 
    Rows(ActiveCell.Row).Select 
    Cells(ActiveCell.Row, "F").Select 
    Sheets("Sheet2").Activate 
    Range("A42").Select 
    xlLastRow = .Cells.Find("*", .Cells(1), xlFormulas, _ 
    xlWhole, xlByRows, xlPrevious).Row 
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ 
    False, Transpose:=False 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The code from above ive got from reading these forums and adapting to my workbook, but obviously doesnt work for me.

Any help appreciated, thanks!

Can anybody help me do this?

If its easier.
I am attempting to create a macro that will copy cell C4 from workbook 'Form' into the next empty cell in column A in workbook 'Master'

Does that make sense?

Any suggestions would be brilliant as the one i currently have doesnt work whatsoever

Regards,

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 am trying to post the results of one cell to the next blank cell in a different row. For example; I need the results in S3 to post to the next blank cell in row P. I tried the worksheet_change code below but that will only work if I make a manual entry in S3 and does not work if I use a formula in S3 to perform the calculation. My formula in S3 is =MAX(L5:L505). When I hit F9 to recalculate I need the results of S3 to post in row P each time. Perhaps there is a formula that I can use as opposed to VBA.


	VB:
	
 
Private Sub Worksheet_Change(ByVal Target As Range) 
    If Target.Address  "$S$3" Then Exit Sub 
     
    Application.EnableEvents = False 
    Range("P65536").End(xlUp).Offset(1, 0).Value = Target.Value 
    Application.EnableEvents = True 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines


Hello,
I am so thrilled to have found you! Would you help me with this?
When I click on a click button, I need to copy the value in E25 Book 1 Sheet 1 into the next blank cell in column b of Book 1 sheet 2 AND the next blank cell in Column J of Book 2.
thank you.

Hi
I would like to write a macro that will fill the next blank cell with data
Example attached
I want to enter the total in f5 into the next blank cell in g5 to r5 using a macro that will be activated by a button
Any help would be appreciated

Hi, does anyone know how to find the next blank cell in column A? I'm trying to add this function so I can paste some data in the proper area of my worksheet with the click of a button.

Can someone please provide some code that will select the next blank cell in a column of data. The problem with using

Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Select

is that if there is nothing in the column the last cell in the column (cell 65536) is selected

thanks

I have a macro in my spreadsheet that changes the shading (color) of cells depending on the contents. Kind of like an enhanced conditional formatting. The macro works fine, however, when I pasted the macro into a VBA function, it didn't work. Also, when I created a button on the spreadsheet to launch the macro, it failed at the same spot.

Choosing Tools -> Macro -> Macros -> (and selecting Macro1) works fine. Running it any other way the code fails the first time it tries to set the "Interior.ColorIndex" property.

The offending code is pasted here
...

	VB:
	
 Left(Cells(j, i).Value, 2) 
Case "S-" 
    With Cells(j, i).Interior 
        .ColorIndex = 36 
        .Pattern = xlSolid 
    End With 
Case "J-" 
    With Cells(j, i).Interior 
        .ColorIndex = 34 
        .Pattern = xlSolid 
         '.PatternColorIndex = xlAutomatic
    End With 
Case "" 
    Cells(j, i).Interior.ColorIndex = xlNone 
Case Else 
    Cells(j, i).Interior.ColorIndex = Cells(j - 1, i).Interior.ColorIndex 
End Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks,
Dave

I have this project i have to do for work and i want the commandbuttons to
open seperate sheets within the workbook - but i dont understand all this
visual basic language stuff so can someone please translate it into a dummy
version for me - i can run a macro by recording it but how do i make the
macro perform within the button??? i remember doing it in college but can not
rem think it was an older version - all help is appreciated.

thank you for ur time and many regards

This is probably a simple question, but I can't seem to figure it out...

I want to run a task of pasting several rows and columns of information into
a cell that is selected by the user.

I recorded a Macro and asigned it to a rectangular button that will copy the
information, but how can I make it paste the information starting in the
selected cell?

Thanks in advance.

I have this project i have to do for work and i want the commandbuttons to
open seperate sheets within the workbook - but i dont understand all this
visual basic language stuff so can someone please translate it into a dummy
version for me - i can run a macro by recording it but how do i make the
macro perform within the button??? i remember doing it in college but can not
rem think it was an older version - all help is appreciated.

thank you for ur time and many regards

I created a button in one sheet where data will be input (task, start date, end date, etc). When the button is clicked the information is copied and pasted into a new sheet.

How can I write code to make it paste in the next row and continue that each time the button is clicked. For example, the first time it's clicked it pastes the data into row 3, the next time I want it in row 4, then row 5, etc. if statement? for? when? I get confused when reading through the help section of vba. Each time I read a new one it seems like it might work but I haven't been able to develop one that does work.

Thanks for any help, I'm not very good at writing vba code. The recorder works for me most of the time. If the macro is slow then I make some adjustments and clean it up.

I created a macro that formats a report in one workbook and then opens another file macro_prep.xls. It then copies a range and should go back into the first workbook. However, when I run the macro it just copies the data and pastes it back into the Macro_Prep.xls. I believe my problem lies somewhere in the thisworkbook.activate line but am not sure. Any insight as to why this is happening would be greatly appreciated.

Sub Payroll()
'
' Payroll Macro
'

'
    Range("E:E,G:G,J:J,L:L,M:M").Select
    Range("M1").Activate
    Selection.Delete Shift:=xlToLeft
    Columns("D:D").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Columns("F:F").Select
    Selection.Insert Shift:=xlToRight, CopyOrigin:=xlFormatFromLeftOrAbove
    Range("C1").Select
    Selection.Copy
    Range("D1").Select
    ActiveSheet.Paste
    Range("E1").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("F1").Select
    ActiveSheet.Paste
    Range("D3").Select
    Application.CutCopyMode = False
    ActiveCell.FormulaR1C1 = "=MROUND(RC[-1],0.25)"
    Range("D3").Select
    Selection.AutoFill Destination:=Range("D3:D32"), Type:=xlFillDefault
    Range("D3:D32").Select
    Selection.NumberFormat = "0.0"
    Selection.NumberFormat = "0.00"
    Columns("C:C").ColumnWidth = 0
    Range("F3").Select
    ActiveCell.FormulaR1C1 = "=MROUND(RC[-1],0.25)"
    Range("F3").Select
    Selection.AutoFill Destination:=Range("F3:F32"), Type:=xlFillDefault
    Range("F3:F32").Select
    Selection.NumberFormat = "0.000"
    Selection.NumberFormat = "0.00"
    Columns("E:E").ColumnWidth = 0
    Range("G3:I32").Select
    Selection.NumberFormat = "0.0"
    Selection.NumberFormat = "0.00"
    Range("J3").Select
    ActiveCell.FormulaR1C1 = "=SUM(RC[-6],RC[-4],RC[-3],RC[-2],RC[-1])"
    Range("J3").Select
    Selection.AutoFill Destination:=Range("J3:J32"), Type:=xlFillDefault
    Range("J3:J32").Select
    Range("A2").Select
    ActiveCell.FormulaR1C1 = "Hourly"
    Range("A2").Select
    With Selection.Interior
        .Pattern = xlSolid
        .PatternColorIndex = xlAutomatic
        .Color = 65535
        .TintAndShade = 0
        .PatternTintAndShade = 0
    End With
    Selection.Font.Bold = True
    Range("A3:K32").Select
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    Columns("B:B").EntireColumn.AutoFit
    Columns("A:A").EntireColumn.AutoFit
    Columns("D:D").EntireColumn.AutoFit
    Columns("F:F").EntireColumn.AutoFit
    Columns("G:G").EntireColumn.AutoFit
    Columns("H:H").EntireColumn.AutoFit
    Columns("I:I").EntireColumn.AutoFit
    Columns("J:J").EntireColumn.AutoFit
    With ActiveSheet.PageSetup
        .PrintTitleRows = ""
        .PrintTitleColumns = ""
    End With
    ActiveSheet.PageSetup.PrintArea = ""
    With ActiveSheet.PageSetup
        .LeftHeader = ""
        .CenterHeader = "&A"
        .RightHeader = ""
        .LeftFooter = ""
        .CenterFooter = "Page &P"
        .RightFooter = ""
        .LeftMargin = Application.InchesToPoints(0.5)
        .RightMargin = Application.InchesToPoints(0.5)
        .TopMargin = Application.InchesToPoints(1)
        .BottomMargin = Application.InchesToPoints(1)
        .HeaderMargin = Application.InchesToPoints(0.5)
        .FooterMargin = Application.InchesToPoints(0.5)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .PrintQuality = 600
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperLetter
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = False
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
    End With
    ChDir "C:UsersWendyDocumentsPayrollEmployee Time Reports - 2010"
    Workbooks.Open Filename:= _
        "C:UsersWendyDocumentsPayrollEmployee Time Reports - 2010Macro_Prep.xlsx"
    ActiveWindow.SmallScroll Down:=15
    Range("A33:J51").Select
    Selection.Copy
    ThisWorkbook.Activate
    Range("A33").Select
    ActiveSheet.Paste
    ActiveWindow.SmallScroll Down:=6
    Range("K35:K48").Select
    Application.CutCopyMode = False
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    ActiveWindow.SmallScroll Down:=-24
End Sub


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


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