Free Microsoft Excel 2013
Quick Reference
Free Microsoft 2013 Quick Reference Guide

Free Microsoft Excel 2013 Quick Reference

VBA Code- Copy & Paste in Blank Range

Hi,

I'm using Excel 2002

I'm trying to write a code to copy and paste special a range of data in the
next empty cell. Therefore if cell j5 has data it would start pasting it in
only k5 and so on. This is what I have so far but its pasting in all the
columns where row 5 is blank:

If Range("j5").Value = " " Then

End If

ActiveWindow.SmallScroll ToRight:=-1
Range("D593").Select
Selection.copy
ActiveWindow.SmallScroll Down:=-123
ActiveWindow.SmallScroll ToRight:=15
ActiveWindow.SmallScroll Down:=-15
Range("j5").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False

End If

Else

I would greatly appreciate any help with this. Thanks in advance.


Post your answer or comment

comments powered by Disqus
Hi all,

I hope you can help me - I have searched the web but no luck

Right, what I need is a vba code that copies a range in a sheet and then paste it in the next available blank column.

Illustration:

A B C AB
1 8
9 7
6 4

What I need it to do is copy cells in column AB then paste it in column B then when B has data paste in Column C.

Note: all the data will be held in the same sheet.

Hope this makes sense.

Thanks for your help in advance

Thanks forum for providing great examples on vba.
Presently i m working on a code where in i m Using autofilter a range,copying & pasting it to a new location in a worksheet.I had checked out many examples on the same and found the code.but i m struggling with a small problem.....Pls check out my tiny example.
Private Sub Test()
ActiveWorkbook.Sheets("test").Activate
Range("A1").Select
ActiveSheet.Range("$A$1:$D$182").AutoFilter Field:=1, Criteria1:="abc"
    Range("A1:D101").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("I1").Select
    
' do loop for testing blank cells
Do
If IsEmpty(ActiveCell) = False Then
ActiveCell.Offset(1, 0).Select
End If
Loop Until IsEmpty(ActiveCell) = True
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("A82:D101").Select
    Selection.Copy
    Range("I82").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Selection.AutoFilter
    ActiveWorkbook.Save
End If
End Sub
i want to use autofilter for copying & pasting the new range down the other with different Criteria with out repeating the Heading.
The heading is copied on to the first row in the new location.....
Attached a small Example with code...
what do i need to make changes to do so...help me...pls....

Hi.
Great forum.

I am cleaning up a spreadsheet for import and are using the following vba code for filling in blanks from them above.


	VB:
	
 FillinTheBlanksScript() 
    Dim topcell As Range, bottomcell As Range 
    Set topcell = Cells(1, ActiveCell.Column) 
    Set bottomcell = Cells(16384, ActiveCell.Column) 
     
    If IsEmpty(topcell) Then Set topcell = topcell.End(xlDown) 
    If IsEmpty(bottomcell) Then Set bottomcell = bottomcell.End(xlUp).Offset 
     
    Range(topcell, bottomcell).Select 
    Selection.SpecialCells(xlBlanks).Select 
    Selection.FormulaR1C1 = "=R[-1]C" 
     
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
The problem appears when the company does not have a url address and copies the url from the company above(The spreadsheet consists of multiple companies, which some have multiple lines.)
However, the companies are separated by color, so I was trying to edit it to check the "interior" color as well, so if the cell above have the same color, then copy the text from above. If not, just leave it blank.
Is it possible?

Thanks for any hints or snippets. Would be greatly appreciated.

I need a macro which will look up a value in a cell, then copy data from a
range of cells and paste them into a specific location in another worksheet,
delete the original data. I need to do this several times always picking
data up from the same range of cells but pasting into a new location every
time. Please help as I'm bald enough as it is!!

I use this code to fill in blanks in one of my workbooks.

'Summary By Unit and Category
                                    ActiveWorkbook.Sheets("Summary By Unit and Category").Select
                                    Range("A19:A" & Range("D" & Rows.Count).End(xlUp).Offset(1,
0).Row).Select
                                    
                                    'Fill in gaps using data above
                                    Selection.SpecialCells(xlCellTypeBlanks).Select
                                    Selection.FormulaR1C1 = "=R[-1]C"
                                    'Make static
                                    With ActiveSheet.UsedRange
                                        .Copy
                                        .PasteSpecial xlPasteValuesAndNumberFormats
                                        .PasteSpecial xlPasteColumnWidths
                                        .PasteSpecial xlPasteFormats
                                    End With

It selectes data in one column based on the amount of rows in another and then just fills in the blank cells based on what's above.

Unfortunately, it's not working in another sheet.

I get a complie error: Expected function or variable and Selection on the 4th line is highlighted.

Any ideas?

Thanks

Hello,
In the file in the attachement, names are copied to different sheets on a condition that is in column 2.
(Macro Searchonstring)
Example all rows with FS in column 2 are copied to sheet FS, with CF in column 2 are copied to Sheet CF,...
But I have a 3th column that can contain 2 values: OK or NOK
What I want is that rows with FS in column 2 are copied to sheet FS, this works already, but then those with OK in column 3 must be pasted in the Range A1:A10, those with NOK must be pasted in the range A11:A20
Thanks for helping
aisietie

Dear Friends,

I got the following code (which disables the cut, copy, paste in a workbook) in this forum only. Thanks to the author. It works nicely. I applied the code as follows:

In workbook module:
Option Explicit

Sub Workbook_open()
EnableControl 21, False ' cut
EnableControl 19, False ' copy
EnableControl 22, False ' paste
EnableControl 755, False ' pastespecial
Application.OnKey "^c", "Error"
Application.OnKey "^v", "Error"
Application.OnKey "^x", "Error"
Application.OnKey "+{DEL}", "Error"
Application.OnKey "+{INSERT}", "Error"
Application.CellDragAndDrop = False
Application.OnDoubleClick = "Error"

End Sub
In general module:
Option Explicit

Sub Error()
MsgBox "This option has been disabled"
End Sub

Sub EnableControl(Id As Integer, Enabled As Boolean)
Dim CB As CommandBar
Dim C As CommandBarControl
On Error Resume Next
For Each CB In Application.CommandBars
Set C = CB.FindControl(Id:=Id, recursive:=True)
If Not C Is Nothing Then C.Enabled = Enabled
Next
End Sub
It disables shortcuts (Ctrl+x, Ctrl+C, etc) only. But, how to disable these commands in Menu control also?

Thanks in advance.

acsishere.

Hi all,

I do a lot of copying/pasting in excel. This gets 'tossed' into the
Clipboard and somehow gets full. When this happens, I'm unable to copy/paste.
I close excel completely and am still unable to copy/paste. The weird thing
is that it affects other programs, not just Office. Is there a way to clear
the clipboard? Or is there something else that could be the cause? Thanks
for any feedback.

Sean

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200604/1

Hi all,

I do a lot of copying/pasting in excel. This gets 'tossed' into the
Clipboard and somehow gets full. When this happens, I'm unable to copy/paste.
I close excel completely and am still unable to copy/paste. The weird thing
is that it affects other programs, not just Office. Is there a way to clear
the clipboard? Or is there something else that could be the cause? Thanks
for any feedback.

Sean

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200604/1

I am using Excel 2003 and access it over the COM interface through C# on
Windows 2000

While the Excel sheets are filled by the program copy/paste in the explorer
does not work. Does someone has an idea how to solve this problem.

Thanks

Joerg Battermann

Hi all,

I do a lot of copying/pasting in excel. This gets 'tossed' into the
Clipboard and somehow gets full. When this happens, I'm unable to copy/paste.
I close excel completely and am still unable to copy/paste. The weird thing
is that it affects other programs, not just Office. Is there a way to clear
the clipboard? Or is there something else that could be the cause? Thanks
for any feedback.

Sean

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...excel/200604/1

Need Macro to Copy Paste in a Target Row in Different Columns

Dear Forum,

I have a 3 Column Data where I am checking for some dates from 2 Different Columns one is before the Actual Column where this date has to be copy pasted..

I am actually comparing the Dates from these two columns and pasting them in the middle column...

So the Sequence of action is as below copy the data from the Column B and Paste it into Column C or Copy the Data from the Column D and paste it into Column C.

Now I tried recording a macro but in vain..I need to have 2 separate macros doing this as I need to have a choice of copying the data from either the COLUMN B or COLUMN D into the common COLUMN C...

The macro needs to work on a Target Row basis so I'm imagining that there would be similar macros for both the columns and on running them I make a choice of using either Macro1 or Macro2.

Warm Regards
e4excel

Hi all,

I do a lot of copying/pasting in excel. This gets 'tossed' into the
Clipboard and somehow gets full. When this happens, I'm unable to copy/paste.
I close excel completely and am still unable to copy/paste. The weird thing
is that it affects other programs, not just Office. Is there a way to clear
the clipboard? Or is there something else that could be the cause? Thanks
for any feedback.

Sean

--
Message posted via OfficeKB.com
http://www.officekb.com/Uwe/Forums.a...l-new/200604/1

Ok so I am having a little difficulty figuring out how to do this so I though I would ask for help!

I have a spreadsheet report I need to update daily and email the results. What I am looking for is a macro or VBA module that can do the following...

Copy all the rows in a constant range that currently have data in them. The range will always be a constant - say A1:D300 for example. But sometimes the data will run through row 20, and other days through row 200. I need the VBA code to loop until it hits a blank row of data, and then it can stop. All the data will be consecutive so there won't ever be a situation where there is data on row 5, no data on row 6, then data again on row 7.

Finally I need / would like the data to be pasted into the body of a new email, but not sent yet as I may need to update the daily subject line. However if it is possible to auto populate a distribution list stored in my contacts that would be beneficial (call it "DailyReport Distro").

Any help would b MUCH appreciated!!

I have a Worksheet that contains values in Column D, E, and F. I need to reference column D to find the value of column E, then copy the value found in "E" and paste it in the blank cells between the next value in "E". After that I need to delete the blank cells from columns D and E. I have gotten the deleting of the cells to work, but I'm having much trouble with coping and pasting between two values. I am very new to Visual Basic, any help would be greatly appriciated. Here's my code so far:

	VB:
	
 Delete_Empty_Rows() 
    Dim i As Long 
    Dim c As Range 
    Dim rSearchRange As Range 
    For i = rSearchRange.Row.Count To 1 Step -1 
        Set c = Worksheets("VALUES").UsedRange.Columns(4).Find("Sort Program:", LookIn:=xlValues) 
        If WorksheetFunction.CountIf(c) Then 
            c.SpecialCells(xlCellTypeConstants).Copy 
            If WorksheetFunction.CountBlank(c) Then 
                c.SpecialCells(xlCellTypeConstants).PasteSpecial 
            Next i 
             
            Set rSearchRange = Worksheets("VALUES").UsedRange.Columns(6) 'for example
            If WorksheetFunction.CountBlank(rSearchRange) Then _ 
            rSearchRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete 
            Set rSearchRange = Worksheets("VALUES").UsedRange.Columns(5) 'for example
            If WorksheetFunction.CountBlank(rSearchRange) Then _ 
            rSearchRange.SpecialCells(xlCellTypeBlanks).EntireRow.Delete 
        End Sub 

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


I have a range (A1:F10) on Sheet1 that contains formulas and some formatting.

Currently, I enter data in columns A, B and C. I have formulas in columns D, E and F that perform calculations based on the data in columns A:C.

After I enter my data in columns A, B and C, I manually copy the formulas and formats of from last row of the range (only from columns D:F) to the new row that I just entered the data in.

For example, if I enter data in cells A10:C10, I would manually copy and paste special the formulas and formatting from cells D10:F10 to cells D11:F11.

Question: Can you please help me with the VBA code that would:

1. Search the range D1:F??? (the range gets bigger every time we enter new data in a row. The next data entered would be on line 11).

2. 'Find' the LAST row that contains formulas from the range D1:F??

3. Copy and paste special the formulas and formating of the last row of range D1:F?? to the row directly beneath it.

--> so, in the example above, the macro would search the range (currently D1:F10) to find the last row with formulas (currently row 10), copy D10:F10, paste special formulas and formatting to D11:F11.

Thanks for any help with this code!!
Rachel

Good afternoon,

Im trying to create a code that will copy the contents in a range to outlook and then email the relevant parties, i have managed to achieve this but when the contents copy and paste the information into outlook its all over the place so i would like to amend this code to paste the contents as a Paste Special picture. Could anyone help me with this?

Yours gratefully, DJ

Sub Email_Todays_Deals()

   ' Select the range of cells on the active worksheet.
   With Sheets("Email Ready").Range("A1:O37")
   ActiveSheet.Pictures.Paste.Select
   'ActiveSheet.Range("A1:E200").Select
   End With
   ' Show the envelope on the ActiveWorkbook.
   ActiveWorkbook.EnvelopeVisible = True
   
   ' Set the optional introduction field thats adds
   ' some header text to the email body. It also sets
   ' the To and Subject lines. Finally the message
   ' is sent.
   With ActiveSheet.MailEnvelope
      .Introduction = "Data From DJ Database"
      .Item.To = "davidjames@1stcreditltd.com"
      .Item.Subject = "Management Information"
      .Item.Send
   End With
End Sub


Running Excel 2007 I'm trying to copy data from rows on 1 sheet into another sheet. So from Form1 to Sheet3 in my workbook. I have this code so far:

Sub Macro7()
    Range("T2:AP2").Select
    Selection.Copy
    Sheets("Sheet3").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("E10").Select
End Sub
Is there code that will go to the next blank row on Sheet3? So if A2 is not null paste on B2. I'm not very good with VBA.

I wrote a code to search column H of every row from the last populated row and copy the cell only if the contents were not blank and then paste those contents in a running cumulative into another worksheet. Here is the code:


	VB:
	
 Warehouse() 
    Sheet3.Select 
     'Find last row of data
    FinalRow = Cells(Application.Rows.Count, 1).End(xlUp).Row 
     'Loop through each row
    For x = 2 To FinalRow 
         'Decide if to copy based on column H
        ThisValue = Cells(x, 8).Value 
        If ThisValue  "" Then 
            Cells(x, 8).Copy 
            Sheet2.Select 
            NextRow = Cells(Application.Rows.Count, 1).End(xlUp).Row + 1 
            Cells(NextRow, 1).Select 
            ActiveSheet.Paste 
            Sheet3.Select 
        End If 
    Next x 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
When I run the Macro nothing happens...I'm lost as to what's not working here. Thanks for helping me with my ignorance!

Justin

Hi,

I have a xls file with 4 pictures grouped together. I created VBA code so when a group of shapes is clicked then this particular group ungroup itself and show next picture then group toghether again. So when click a picture(group) then next picture is shown and so on.

The problem is I want to copy&paste the group to have more groups like these in the worksheet.
In Excel 2003 I just copied and pasted and it worked without problem.
In Excel 2007 it doesn't work as expected.

I know this is connected with picture names inside the group.

PLEASE HELP!!!!

CHRIS

Hi – I need help with some code to paste special (values) from specific ranges on one worksheet to specific ranges on another worksheet. I feel as though I should be able to figure this one out but I’m struggling.

The origin of the data is on the tab titled “FINAL”, which consists mostly of VLOOKUP formulas. I need to copy from the FINAL tab and paste special (values only) onto the destination tab, titled “Promotions”.

The data on the FINAL tab will always be different in future samples, and will extend down to a different row every time depending on the data size – so the code should run as long as there is a value in column C on the FINAL tab.

The destination tab (“Promotions”) has some merged cells that the FINAL tab doesn’t have, so I’m not able to select all and paste special; it looks like I need code to go line by line and offset. There are two steps I need the code to do for each piece of data, here are instructions for the first step the code should do:

1a. FINAL tab: select and copy range D3:BY3
1b. Promotions tab: paste special (values) the selected range starting in cell A16
2a. FINAL tab: select and copy range R2:AO2
2b. Promotions tab: paste special (values) the selected range starting in cell O15

After that, the code will do the same thing but will offset going down to lower rows. On the FINAL tab it will offset 2 rows down to copy, then paste special on Promotions tab offsetting 7 rows down. If it helps to have it written out, I’ll write the next steps out like I did above:

3a. FINAL tab: select and copy range D5:BY5
3b. Promotions tab: paste special (values) the selected range starting in cell A23
4a. FINAL tab: select and copy range R4:AO4
4b. Promotions tab: paste special (values) the selected range starting in cell O22

I hope this all makes sense, please let me know if not. Any help would be greatly appreciated!!

I have been working with Excel 2002 and am migrating my models to Excel 2007. However I am having problems with copy / paste in VBA. In 2002 the following code did as expected copy down lines of data. However in 2007, nothing happens - nothing is pasted. The model runs otherwise (no errors are reported / it doesn't crash etc)

I know that the code is inefficient and that I ought to be using RESIZE. However the original PC's weren't very powerful and hence I had to do single line copies.

As I only want to get the thing working I am not concerned by this inefficiency. Thanks

Kaps

For kk2 = Start_Line To Finish_Line
Worksheets("Transaction Data").Range("Validation_THOUSAND_ROWS").Copy
Destination:=Worksheets("Transaction Data").Range("First_Cell").Offset(kk2 * 1000, 0)
Next kk2

Hi All - sorry, i know there are related articles in the forum, but i just don't know enough about VBA to interpret the code for my needs.

I have one workbook called "SalesReport".
In this workbook is a named range called "MgrReport".

I would like VBA to:

- Copy/Paste Special (Value, Format) named range "MgrReport" from "SalesReport" workbook to a new workbook. (The "SalesReport" workbook will be open at the time).

- I would like the new workbook to be named 1) whatever is in cell Y797 PLUS 2) "Mgr Report" PLUS 3) today's date. Y797 Contains the Mgr's Area number, example: 235-98-0.
So in the end, the new workbook would be named "235-98-0 Mgr Report 9-23-2008".

Thank you for taking the time to read!
Sandi

Hi,

Can I run a vba code to "replicate" cell values to a larger part of a table
with a array solution? Say you have A1:A10 with a number serie like in the
end of this message and want to fill A14:A23, A27:36 etc with the values of
A1:A10. i'm not so god at arrays, but doesn't this code store all 10 values
into the a variable?

[ No, I DON'T want to use the excel handle and just drag it out or just
copy and paste the cells by selecting the compleate range of 300 x 13 cells
and just do one single paste to achieve this. I'm aware of this solution. I
want to be able to ALSO USE this kind of "array solution" in my further vba
code to have a easy way to "replicate" cells VALUES in one range to another
place in the workbook without using copy-paste in the code. ]

'Nothing happends with this code...

Sub UseArraysToCopyRangeCellsValues()
Dim rRow As Long
Dim counter As Long
Dim a As Variant

Let counter = 0
Let rRow = Selection.Row
Let a = Array(Range(Cells(rRow, 10), Cells(rRow + 9, 10)).Cells.Value)
Do While counter < 100
'Debug.Print a
rRow = rRow + 13
counter = counter + 1
ActiveSheet.Range(Cells(rRow, 10), Cells(rRow + 9, 10)).Cells.Value
= a
Loop
End Sub

A1 11
A2 22
A3 33
A4 44
A5 55
A6 66
A7 77
A8 88
A9 99
A10 10
A11
A12
A13
A14 11
A15 22
A16 33
....
and further down
.... ...


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