Free Microsoft Excel 2013 Quick Reference

VBA To Paste Data Into Next Blank Cell On Another Worksheet

Excel 2003
I would like to use a code on one worksheet to initiate 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 available row.

The user will have information on Sheet5 from row A to P. Doesn't matter the Column.
I have the following code but need to modify it to do the following:


	VB:
	
 
    If Target.Cells.Count = 1 Then ' stops the code looping
        If LCase(Target.Value) = "yes" Then 
            Range("A5:P5").Copy 
            Sheets("Archives").Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial xlvalue 
        End If 
        Target.EntireRow.Delete 'I need this to be clear content instead of delete the entire row
    End If 
End If 

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

I do not need to have A5:P5. I need the selected row to copy the info from A:P that the user selected from Row? to the next available row in my Archives sheet.

I need that everytime a user select a row and from the Q column he selects "yes" then to have that row copy to the next available row in my Archives sheet and then clear the content that was in that selected row.

Ex: Q5 Is selected and the user select "yes", then Copy and paste to next available row in Sheet("Archives") then Clear content in Sheet5 A5:P5
Ex: Q9 Is selected and the user select "yes", then Copy and paste to next available row in Sheet("Archives") then Clear content in Sheet5 A9:A9
Ex: Q8 Is selected and the user select "yes", then Copy and paste to next available row in Sheet("Archives") then Clear content in Sheet5 A8:A8

If we take this example my sheet"Archives" will have the following info from the Sheet5 instruction:

A5:P5 = Info copied from Sheet5 A5:P5
A6:P6 = Info copied from Sheet5 A9:P9
A7:P7 = Info copied from Sheet5 A7:P7

Please Help


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

Dear All,

Looking for support for Macro Code to paste special from one Sheet to another sheet.

1. Copy Data from Sheet(Jaspal) range B6:e11
2. Paste special (Value) to Sheet2 (Date wise)
3. every time we run macro it should paste data into next column and blank cell; for example on first run it copied data into first blank cell of column B Cell 2 and data copied till Column F cell 11; so now on next run data should be copied to Column G and cell 2 till Column K and cell 11... So on!

Till now I am using below code .. which copies data from sheet jaspal to sheet 2 but only in first blank cell of column of B.

*** I am not aware of VB coding or Macro Coding and new for this but by help I will be able to complete the task...

Thanking all in advance.

Code:
Sub Macro3()
'
' Macro3 Macro
'
' Keyboard Shortcut: Ctrl+i
'
    Sheets("Jaspal").Select
    Range("B5:E11").Select
    Selection.Copy
    Sheets("Sheet2").Select
    
    If Application.WorksheetFunction.CountA("B:B") = 0 Then
[B1].Select
Else
On Error Resume Next
Columns(2).SpecialCells(xlCellTypeBlanks)(1, 1).Select
If Err  0 Then
On Error GoTo 0
[B65536].End(xlUp)(2, 1).Select
End If
On Error GoTo 0
End If
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub
My Mail ID with hope of quick response.
sikhcomputer@yahoo.com

My original post is over at http://www.excelforum.com/excel-prog...worksheet.html but I haven't had any luck and need to find additional expertise because this project is way past it's deadline.

Here is the post:

I want to have Excel copy an entire row to a new Excel worksheet based upon a cell entry. For example I have data in cells A5:O5 in a separate workbook in sheet "Individual Sheet", I want to have Excel copy the entire row to sheet "Opportunities-proposals" if column B has any text in it. The most complicated part and not just copied, I need it copied to the next blank row on the worksheet. Of the 50 rows or so only about 15 of each type will actually be copied to a new sheet. I need this function to do this for each individual employee's sheet so that we have one main master sheet with everyone's info on it.

This is a question that I found that was very close to my same issue and they found an answer, so I just changed it a bit to fit my needs. The link to the original question and the answer is here: http://www.mrexcel.com/articles/copy...-blank-row.php
Unfortunately, I do not know enough about vba to change the code to exactly fit my needs. I attached 2 of my sample sheets below. I appreciate any help, thanks.

Bus Dev Main Sheet.xlsmIndividual Master Sheet.xlsm

Hi, I'm very new to VBA and created my first Macro (by recording).
Below is my code, This macro is to be run daily to keep a history of data, the macro is copying data from sheet 'Table for GMC' into 'Sheet 3' this has ran perfectly for the first days data. The data on sheet 3 is from cells A3:D14. The next lot of data is to be pasted into F3. This will leave column E blank for a break between days.
Can anyone assist with easy to understand code for the macro to move to a particular cell instead of overwriting the previous data?

Sub History()
'
' History Macro
'
Range("A3").Select
Sheets("Table for GMC").Select
Range("A2:D28").Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("Sheet3").Select
ActiveSheet.Paste
Range("F3").Select
Sheets("Table for GMC").Select
Range("E2").Select
Application.CutCopyMode = False
Sheets("Sheet3").Select
Range("F3").Select
End Sub

Many thanks

Hi Folks

Does anyone know how to paste the data into next blank rows without delete existing data based on user form selection? I have attached a file of what I'm trying to accomplish. Thanks in advance for your expertise!

Joshi

Hi there,

I have a slight problem that would best be understood with my sample sheet attached.

Basically i have a dynamic range which i need to copy and paste into the next blank cell on another sheet.

First part:

I think I need some code that basically loops and selects the data until the next cell is either blank or an error and then copy it

( xlDown will not work for this situation as formula's exist in the "blank" cells)

Second part:
I've tried alot of "find next blank cell and paste data" macro's but always get a run-time error. Can someone help?

See attached sample worksheet for clearer description.

Thanks

Joel PJ

Hi everyone,

I am looking to use a userform with 10 textboxes on to enter data into the next available blank space in a sheet.

A blank space for me is a block of 20 blank rows. Textbox1 value will go into the first blank cell in that space i.e. A1, textbox2 B1 textbox3 C1 and so on......if the next available blank space started at A100 the textbox1 value would go into A100, Textbox2 value in B100 and so on...

So the code will need to first find a block of 20 blank rows and then populate the first row in that block with data from the userform. The code will be activated by a command button on the userform.

Any help with with this would be much appreciated.

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 Folks

Does anyone know how to paste the data into next blank rows without delete existing data based on user form selection? I have attached a file of what I'm trying to accomplish. Thanks in advance for your expertise!

Joshi

i am using the following code to enter data into column a starting at a6. What can i add to enable the data to be added ito the next free cell in column a when the input box is used?

Code:
Sub entername()

Dim name As String
name = InputBox("Enter Name", "Add Name")

If name  "" Then
    Range("A6").Value = name
End If

End Sub


Hi everyone,
I hope I don't sound like an idiot here - but I am not at all a vb programmer.. I am trying to find a way to paste data into merged cells. I noticed that when I try to paste in something from.. say.. a web page, to a merged cell - that does not work.. but.. when I paste something from a web page to a single, un-merged cell then to a merged cell - that *does* work. I am thinking - if this is possible - to write a script which will make it so when a user tries to paste data into a merged cell, it first copies that data to a temporary cell then copies it back to the intended cell. Is this possible? if so, can anyone point me in the right direction as to how to get started with this?!

Thank you very much in advance ! !

How can I find the next blank cell in another workbook? The code I have is:

Dim SelectedFile As String
Dim MessageBox As Integer
Private Sub Workbook_Open()

    '//Send opening message
    MessageBox = Msgbox("If you haven't already downloaded an Expense Report please do that now", ,
"Welcome")
    
    '//OpenFileDialog
    SelectedFile = Application.GetOpenFilename("Excel Files (*.xlsx),*.xlsx", , "Please select an expense
report")
    
    If Len(SelectedFile) > 5 Then
    
        'Set variables
        Dim ExpenseReport As Workbook
        Dim CurrentWorkBook As String
        Dim ExpenseReportString As String
        Dim LastDataRow As Integer
        Dim StrFind As String
                
        '//Set CurrentWorkbook name
        CurrentWorkBook = ThisWorkbook.Name
    
        ' Open the Expense Report
        Set ExpenseReport = Workbooks.Open(Filename:=SelectedFile)
        
        '//Get Expense Report name
            Do Until Left(StrFind, 1) = ""
                iCount = iCount + 1
                StrFind = Right(SelectedFile, iCount)
                If iCount = Len(SelectedFile) Then Exit Do
        Loop
        ExpenseReportString = Right(StrFind, Len(StrFind) - 1)

        '//Activate the ExpenseReport
        Application.Workbooks(ExpenseReportString).Activate
        
        '//Find and select data in ExpenseReport
        LastDataRow = Sheet1.Cells(Rows.Count, 1).End(xlUp).Row
        Range("A2:E" & LastDataRow).Select
        
        '//Copy data in ExpenseReport
        Selection.Copy
        
        '//Place copied data into new workbook
        Windows(CurrentWorkBook).Activate
        Range("A1").Select
        ActiveSheet.Paste
        
        '//Close Expense Report
        Workbooks(ExpenseReportString).Close

    End If

End Sub


I'm trying to reference a cell on another worksheet in my criteria and the formula doesn't seem to be working. All I get is a value of 0.

=SUMIF(G3:G76, ">='City Wide'!B6",I3:I76)

If I replace 'City Wide'!B6 with an actual number the formula works. This does not help as 'City Wide'!B6 is a value that can change.

--------------------------------------------------------------------------------

I have a combo box that needs to link to cells on another worksheet. I figured out how to link to the worksheet, but depending on the choice of the combo box different cells need to be selected. The reason for that is that my worksheet is very long and instead of scrolling to find the spot the user needs on it, I want them to be able to select an area on that worksheet and jump directly to it. I would appreciate any help on this matter.

Thanks,

Mickie

I have a series of worksheets, some of which contain the same data, how do I duplicate a series of cells on another worksheet, not as in copy and paste but if I change the cell contents on one sheet it changes on the others as well?

Many thanks

Paul

I have been trying to write some code that selects a specific cell on another worksheet during a Worksheet_Change macro but it fails if I just use

	VB:
	
Sheet2.Select 
Range("A1").Select 

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

	VB:
	
Sheet2.Select 
Cells(1, 1).Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
If I could do this I might be able to solve the problem I have detailed in the following post:

http://www.ozgrid.com/forum/showthread.php?t=48322

I am starting to think that I cannot do this action for some reason.

Can anybody clarify this for me please?

Thanks.

Hi

Could anyone tell me if it is possible to changes a cells colour if a word is written in a specific cell on another worksheet. I have a small excel document set up at work for invoicing - Each worksheet is linked to the invoicing page - What I would like to know is this - each worksheet I fill in -parts of the information are linked to the invoicing page - but I would like to be able to make the information that appears on the invoice to show up in blue lettering if I type the word "stralsund" in a specific cell on a worksheet. And if I do not type this work for the lettering on the invoice to just be in black. I have no Visual basic knowledge so it would have to be an excel formual etc.

Regards Mumbles

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!

Hi,

I'm looking to write a macro that will find the next blank (empty) cell in column A and then copy the value from the above cell and paste it into that blank cell.

So for example, Column A has 10 rows of data and in cell A11 I want to copy whatever value is in A10 and paste this into A11.

Thanks

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


Hoping somebody can help please with what should be to some of you 'big guns', a fairly simple fix but can't for the life of me figure it out......

Following code upon click of a button ('Test Transfer Data', top right hand corner of BPC Form), does the following: -

1) Opens up another excel file 'BPC Data Form'
2) Looks in Column A for a corresponding match from the Archdiocese No (Ref J11) on the BPC Form
3) Goes to the next blank cell in that row, and is then supposed to copy/paste the DFCAmount (Ref J138) from the BPC Form.

All works fine but wont copy/paste beyond what row 2 has in it.
Think it has something to do with my (EndColumn =) bit of code

have attached both files.
Please somebody help

Kind Regards
Steve

Private Sub CommandButton19_Click()
Dim DestBook As Workbook, SrcBook As Workbook

Application.ScreenUpdating = False

Set SrcBook = ThisWorkbook
Set DestBook = Workbooks.Open("C:UsersstevetBPC Data Form.xlsx")



Dim rng As Range, c As Range, cfind As Range
', EndColumn As Range

On Error Resume Next
With Worksheets("BPC Form")

Set rng = Range("ArchdioceseNo")
For Each c In rng

EndColumn = Worksheets(DestBook.Worksheets(2).Name).Cells(2, 256).End(xlToLeft).Column

With Worksheets(DestBook.Worksheets(2).Name)


    Set cfind = .Columns("A:A").Cells.Find _
        (what:=c.Value, Lookat:=xlWhole)
        If cfind Is Nothing Then GoTo line1
         
        .Cells(cfind.Row, EndColumn) = [DFCAmount]
        

End With 'sheet2

line1:
Next c
Application.CutCopyMode = False
End With 'BPC Form

'ActiveSheet.Protect Contents:=True
End Sub


Sheet 1 is my data file with text in A19:A138 if I have a quantities in some of them A19:A138, I need it to start automatically in C19 on Sheet 2 and continue into next empty cell until each cell down to W38 is filled if there is a value in A19:A138 in Sheet 1.

Hi
i have the code:

	VB:
	
 test() 
    Range("F4").Copy _ 
    Destination:=ActiveSheet.Range("a1").End(xlDown).Offset(1, 0) 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
thanks to royuk!
but how do i get it to paste into the next blank cell of another column, not just column a?

Hi all! I'm new here, but I've been lurking around a bit. You all seem so friendly, and knowledgeable that I thought I'd throw out a question. I need to figure out how I can find the next empty cell. For instance, I have to create a form for someone, and she wants to be able to enter information into the spreadsheet, and then click a button and find the next empty cell so that she can type in more information. I hope that is clear and concise enough. Thanks so much in advance for any time and effort that any of you might go through to figure this out