Free Microsoft Excel 2013 Quick Reference

Select and copy range Results

Hi!

I'm trying to create av CommandButton in sheet "Totalt" and copy range "A8:I200" to sheet "Publisering" and then do some formatting...

But gets "Run-time error '1004'"

I have read that 'copy' from one sheet to another and then performing operations to the new sheet might be a problem. The CommandButton is only working for that spesific sheet (range)... I might be wrong, but need help.

Here is the code:
 
Private Sub CommandButton4_Click()

    Range("A8:I200").Select
    Selection.Copy
    Sheets("Publisering").Select
    Cells.ClearContents
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, kipBlanks:=False, Transpose:=False
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
    
End Sub
Thanks!

ruttian

Hello all, I have a sheet called "fixtures" in my workbook.

I column P i have a cell value of either 0 or 1.

What i would like to happen is every time i exit the fixtures tab - for all rows with a value of 1 in column P the range B to O to be selected, and copied into a sheet called Data.

So to summarize, assume cell P3 and P6 have a value of 1. When i exit the fixture tab cells B3:O3 and B6:O6 would be selected/ copied and pasted into sheet Data.

- I need to copy a range from a worksheet (sheet5.)

- There are 30 named ranges MOD_01 through MOD_30

- I need a macro that copies the range from sheet5. to sheet1. (Easy I know…that part I can do!)

- However, I do not want to make 30 buttons (which is the only way I could do it at my current level).

- Instead need one macro that opens a dialogue box which allows me to select between 01 and 30 (preferable a dropdown between 01-30).

- The number returned would be concatenated with the (MOD_&‘returned number’) to define which range needs to be copied.

- The copied range needs to be pasted to
Sheets("Gantt Chart").Range("A8").End(xlDown).Offset(1, 0).Select
    ActiveSheet.Paste
- I honestly don’t know where to start with this one.

- I have attached a sample file. In this I only have named ranges MOD_25 through MOD_30. The sample file shows a simple macro to copy and paste one of the ranges (mod_30) with no dialogue box but that doesn't really help much...

Sub AddRange()
Application.ScreenUpdating = False
Sheet5.Unprotect

    Range("MOD_30").Copy
    Sheets("Gantt Chart").Range("A8").End(xlDown).Offset(1, 0).Select
    ActiveSheet.Paste

Sheet5.protect
Application.ScreenUpdating = True
End Sub
Any help will be greatly appreciated!
Thanks!!!!

Hi,

I am doing the following to copy and paste data from multiple open (3) sample files into a result file.

'Sample File
'Select and Copy range
    Range("B4").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Copy
    
'Result File   
'Paste sample in column A
    Windows(ResultFile).Activate
    Range("a65536").End(xlUp).Offset(0, 0).Select
    ActiveSheet.Paste
    ActiveWorkbook.Save
This copies the data into the first cell in column A no problem. However when processing the second file, data ovewrites the last cell of the first set of data and same for the third file.
Previously I just repeated the code 3 times and set an offset of 1 to the repeated code.

Can anyone point me in the right direction to fixing this?

I wrote the following macro to sort data in a column. Can anyone write the macro to copy the first 5%, middle 5%, and last 5% of my sorted data in three different Sheet. I would much appreciate.

Thanks,

-----------------------------------------------------------
Sub sort_1()

Dim wkbook As Workbook
Set wkbook = ActiveWorkbook

Dim iRow As Integer

iRow = wkbook.Sheets("Sheet1").Cells(Rows.Count, 1) _
.End(xlUp).Offset(1, 0).Row

wkbook.Worksheets("Sheet1").Activate
wkbook.Sheets("Sheet1").Range("A3:A" & iRow - 1).Select

Selection.sort Key1:=Range("A3"), Order1:=xlAscending, Header:=xlNo, OrderCustom:=1, MatchCase:=False,
Orientation:=xlTopToBottom, DataOption1:=xlSortNormal

End Sub


Good day to All,

I've a little problem here...

Scenario

I have 2 workbook

A is the compile list all my products,
B is the new one tat i wanna create,

Customer order by ItemID, therefore i copy the ItemID from the order list and paste in B and I need it to search A for the ItemID in column 3

If exist
copy the whole row in A to B's active row
else
just paste the ItemID in the B

Welcome another other coding and below is what i use last time just for check repeated item. I try to modify from there but I didn't manage to do as I'm just a office guy. THanks alot in adv and even for reading.

RED is my question
Blue please just ingore

Sub Worksheet_Change(ByVal Target As Range)
Dim r As Range
'Dim ans As String

Const myCol As Long = Application.Workbooks("XXX").Worksheets("XXX"). [How do i select the whole of column C?]
'MsgBox (myCol)

If Intersect(Target, Columns(myCol)) Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each r In Intersect(Target, Columns(myCol))
If Application.CountIf(Columns(myCol), r.Value) > 1 Then

[Select the row and copy to a active workbooks cell]

Else
r.Value?

' ans = (r.Value & " already exsists")
' MsgBox (ans)
' r.ClearContents

End If
Next
Application.EnableEvents = True
End Sub

Hi Team,

Iam just looking for an help on find a string and copy certian string to next cell i have tried something which can copy but unforunately that is copying whole cell not certian string.

I have data of 500 rows and i have data till A,B,C,D and E.Please ignore all the other rows which are of no use except E in Column E i just need Reference : A0000000:1 to be copied to next column beside to it that means in Column F.

could someone please suggest why this code is not working eventhough i mentioned in it.

Any Help would be greatly Appreciated.

Sub jusfind()


Dim cCell As Object
Cells(1, "E").Select
For Each cCell In Range(Cells(1, "E"), Cells(1, "E").End(xlDown))
If InStr(1, cCell.Value, "Reference: ") > 0 Then
cCell.Offset(0, 1).Value = Mid(cCell.Value, 19, Len(cCell.Value))
cCell.Offset(0, 2).Value = cCell.Offset(1, 0).Value


End If
Next cCell

End Sub


I have a macro that selects and copies and pastes a range of data from one sheet to another sheet. It works perfectly except it is pasting all the cell formatting and formulas and i only want or need the values. Can someone help ?

here is the code i am currently using

Dim i As Long
With Sheets("Sheet1")
i = .Range("E" & Rows.Count).End(xlUp).Row
    .Range("E8").AutoFilter
    .Range("E8").AutoFilter Field:=5, Criteria1:="1"
    .Range("A8:E8" & i).SpecialCells(xlCellTypeVisible).Copy Sheets("Groups").Range("B8")
    .Range("A1").AutoFilter
End With
Application.CutCopyMode = False
Thanks

Hi,
I have created a spreadsheet that is 38mb and needed to size it down. I recorded the following macro to do this. This essentialy jut copies the top line of the relevant columns on each page and then copies the formulas down, calculates, and then pastes just the values of these calculations, thereby reducing the sheet to a third of its size. The macro works if I run it from the macro option on the toolbar, but when I assign it to a command button I keep getting ' select method class range failed'.
Can anyone help???

Sheets("KPI Data Validation").Select
    Rows("4:4").Select
    Selection.Copy
    Rows("5:12000").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Calculate    
    
    Rows("5:12000").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
     
     
     Sheets("Maintenance Calc Sheet").Select
    Range("c3:D3").Select
    Selection.Copy
    Range("c4:d67").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("M3:R3").Select
    Selection.Copy
    Range("M4:R12000").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("t3:y3").Select
    Selection.Copy
    Range("t4:y67").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Calculate
    
    Range("C4:D67").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollColumn = 2
    Range("M4:R12000").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("T4:Y67").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
        
         Sheets("Repair Calc Sheet").Select
    Range("c3:D3").Select
    Selection.Copy
    Range("c4:d67").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("M3:R3").Select
    Selection.Copy
    Range("M4:R12000").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("t3:y3").Select
    Selection.Copy
    Range("t4:y67").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Calculate
    
    Range("C4:D67").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollColumn = 2
    Range("M4:R12000").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("T4:Y67").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
           Sheets("Financier Calc Sheet").Select
    Range("c3:D3").Select
    Selection.Copy
    Range("c4:d67").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("M3:R3").Select
    Selection.Copy
    Range("M4:R12000").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("t3:y3").Select
    Selection.Copy
    Range("t4:y67").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Calculate
    
    Range("C4:D67").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollColumn = 2
    Range("M4:R12000").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
       Range("T4:Y67").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
           Sheets("Vandalism Calc Sheet").Select
    Range("c3:D3").Select
    Selection.Copy
    Range("c4:d67").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("M3:R3").Select
    Selection.Copy
    Range("M4:R12000").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("t3:y3").Select
    Selection.Copy
    Range("t4:y67").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Calculate
    
    Range("C4:D67").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollColumn = 2
    Range("M4:R12000").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("T4:Y67").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub
Sub Macro15()
'
' Macro15 Macro
' Macro recorded 30/10/2007 by Administrator
'

'
    Sheets("Maintenance Calc Sheet").Select
    Range("c3:D3").Select
    Selection.Copy
    Range("c4:d67").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("M3:R3").Select
    Selection.Copy
    Range("M4:R12000").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Range("c3:D3").Select
    Selection.Copy
    Range("t4:y67").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    Calculate
    
    Sheets("Maintenance Calc Sheet").Select
    Range("C4:D67").Select
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    ActiveWindow.ScrollColumn = 2
    Range("M4:R12000").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        Range("T4:Y67").Select
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub


I want to replace this code with a code that will allow the operation without actually selecting the sheets. What the code does is select a range of data in sheet "PIV-TABLE" then it paste the values in the same sheet, just a little further down with paste values only. The thing is that this macro can be executed in a different sheet, I would like for this copy and paste to be done without actually selecting the sheets so you could see the graph update as the selection changes.

Original code to be replaced:
Sheets("PIV-TABLE").Select
Range("D62:O67").Copy
Range("D77").PasteSpecial Paste:=xlValues

Greetings!

I have used the paste special technique before to add a value to a range of
selected values. What I would like to do now, is that same technique but
instead of choosing a cell value in the worksheet I would like to use an
input box for that value. What I can't figure out is how to copy that input
box value to be used in the paste special commands.

Below is what the code looks like if I type a value in a cell, select and
copy it, and then select my new range of cells to use the paste special
command to add that value to the existing numeric values.

'This is the number I am copying
Range("E2").Select
Selection.Copy
'Now I select the range of cells I want to add it too
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
'The paste special command to add that value to the existing numeric values
in the cells
Selection.PasteSpecial Paste:=xlAll, Operation:=xlAdd, SkipBlanks:=False _
, Transpose:=False

Any ideas on how to do this by using an input box?

Many thanks!

Jessica

Hello from Steved

In My workbook I've inserted 250 rows

Question Using below macro I put the cursor A6 to Q6 then use insert to
insert a row above, I have now gone to Row A7 to Q7 using the paint brush put
the cursor in A6 which using the formatting of row 7 has noe formated the new
row A6.

Is it possible in VBA to do the above.
The reason is instead of inserting 250 rows i only need to insert 1 row.

Ok if i type in D6 "90201" and then push the enter key it also will activate
VBA to insert the new row.

Sub insertrow()
Range("A6:Q6").Select
Selection.EntireRow.Insert
Range("A7:Q7").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("A6:Q6").Select
End Sub

Thankyou.

I have two worksheets. I need to compare both worksheets then output a report that shows the difference between the sheets. I have that much done. I can run a compare and then populate an 'output' worksheet with the changed cells.

My issue is that I want the script to recognize the changed cell and copy the entire row that that discrepency cell is in, and copy that whole row to the output worksheet. I'm attempting it in a loop. It has to compare all the cells in the rows of both worksheets. Follows is the script. Sorry if it's sloppy but I'm fairly new with VBA. Any help would be much appreciated.

Sub CompareWorksheetRanges(rng1 As Range, rng2 As Range)
Dim r As Long, c As Integer
Dim lr1 As Long, lr2 As Long, lc1 As Integer, lc2 As Integer
Dim maxR As Long, maxC As Integer, cf1 As String, cf2 As String
Dim rptWB As Workbook, DiffCount As Long
Dim chnCell As Long, sameCell As Long
Dim rwRange As Range, clRange As Range

If rng1 Is Nothing Or rng2 Is Nothing Then Exit Sub
If rng1.Areas.Count > 1 Or rng2.Areas.Count > 1 Then
MsgBox "Can't compare multiple selections!", _
vbExclamation, "Compare Worksheet Ranges"
Exit Sub
End If
Application.ScreenUpdating = False
Application.StatusBar = "Creating the report..."
Set rptWB = Workbooks.Add
With rng1
lr1 = .Rows.Count
lc1 = .Columns.Count
End With
With rng2
lr2 = .Rows.Count
lc2 = .Columns.Count
End With
maxR = lr1
maxC = lc1
If maxR < lr2 Then maxR = lr2
If maxC < lc2 Then maxC = lc2
DiffCount = 0
For c = 1 To maxC
Application.StatusBar = "Comparing cells " & _
Format(c / maxC, "0 %") & "..."
For r = 1 To maxR
On Error Resume Next
cf1 = rng1.Cells(r, c).FormulaLocal
cf2 = rng2.Cells(r, c).FormulaLocal
On Error GoTo 0

If cf1 <> cf2 Then
DiffCount = DiffCount + 1
Cells(r, c).Formula = "'" & cf1 & " <> " & cf2
End If
Next r
Next c
Application.StatusBar = "Formatting the report..."
Columns("A:IV").ColumnWidth = 20
rptWB.Saved = True
If DiffCount = 0 Then
rptWB.Close False
End If
Set rptWB = Nothing
Application.StatusBar = False
Application.ScreenUpdating = True
MsgBox DiffCount & " cells contain different formulas!", _
vbInformation, "Compare Worksheet Ranges"
Sheets("Sheet1").Select
Sheets("Sheet1").Copy Before:=Workbooks("RadarSen_r1.xls").Sheets(1)
Workbooks(2).Close False

End Sub

Tearing my hair out over the simplest of procedures.

I want to select and copy a range but before that i want to make sure the active cell is in column A and if its not stop the procedure and display a messege box.

It should be simple and i have tried loads of different approaches, read the help, read what i think is the appropriate section of a VBA book and even pleaded with the VBE but no joy.

Heres one of the versions i have come up with, hope somebody can help.

Sub SelectCopyA1toBX1()

If ActiveCell.Address <> ActiveSheet.Range ("A:A") Then
MsgBox ("You need to select a cell in column A")
Exit Sub
End If

ActiveCell.Range("A1:BX1").Select
Selection.Copy

End Sub

Regards

Adrian

I want to select and copy a range of variable size. I know the the first row of the range is from "G3:M3" However, I don't know how many rows deep the range will be. Sometimes the Range may be ("G3:M27") or ("G3:M15").

Additionally, I want to select everything EXCEPT the last row in the range. Also, there is other data below the first range of data that I don't want, so using Range("G65536").End(xlUp) won't work either.

I used the following code and I keep getting Error '91' Object variable or With block variable not set.

I think it's close to doing what I want it to do?!

Sub lastrow()

Dim lastrow As Range
Sheets("sheet1").Select
lastrow = Range("g3:m3").End(xlDown).Row - 1
Range("g3" & lastrow).Select

End Sub

Suggestions?

Hello all,

I need to select a range of data based on the value of a specific cell in each row.

For instance, the specific cell is in column "R", and if that cell shows a number "2" i want to select and copy the range of data in that row from A to Q.

So i may have a "2" in rows 2,3,4 and 5 - I would like the VB code (to build into a macro) that will let me select the data range B2: Q5.

I have attached an example, and in this example only are B2:Q2 would be selected based on the 2 in R2.

Any help much appreciated.

Hi there, i'm currently using this macro, which is helping me find data in one sheet and copying it to another. However I now need to search for the data across multiple worksheets and then copy it to one worksheet.

Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim LSearchValue As String

On Error GoTo Err_Execute

LSearchValue = InputBox("Please enter a value to search for.", "Enter value")

Sheets("V.Sat").Select
Range("A1").Select

'Start search in row 4
LSearchRow = 2

'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2

While Len(Range("A" & CStr(LSearchRow)).Value) > 0

'If value in column E = LSearchValue, copy entire row to Sheet2
If Range("E" & CStr(LSearchRow)).Value = LSearchValue Then

'Select row in Sheet1 to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
Selection.Copy

'Paste row into Sheet2 in next row
Sheets("Get Info").Select
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
ActiveSheet.Paste

'Move counter to next row
LCopyToRow = LCopyToRow + 1

'Go back to Sheet1 to continue searching
Sheets("V.Sat").Select

End If

LSearchRow = LSearchRow + 1

Wend

'Position on cell A3
Application.CutCopyMode = False
Range("A3").Select

MsgBox "All matching data has been copied."

Basically, once it's finished searching on the v.sat worksheet, it then moves to the q.sat, neither, q.dis and v.dis worksheets.

Any help would be really appreciated.

Hi,

I'm looking to have a macro that I've started (below) to allow me to go
out to a file that is always in the same location with the same name
and copy information from it and bring it back into the file I was
originally in. The file from which I want to get the information from
is updated automatically every 24 hours from data dumped into it from
our MRP system. The macro is to be stored in my "personal.xls" file.
The problem is that the original file name is always different. So how
can I make the macro come back to this file that has a different name
each time to paste the updated information into it?

In the macro below, it is the "190-DA125-F1V1-BOM-01.xls" file that has
a different name each time. Somehow, I need to run a macro that will
know to come back to the original file I have open each time and paste
the information in without having to worry about its name....

please help,

Steve

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 2/6/2006 by User
'

'
Workbooks.Open Filename:="S:DocBOM ToolsAlt_Items_List.xls"
Range("A:A,J:J").Select
Range("J1").Activate
Selection.Copy
Windows("190-DA125-F1V1-BOM-01.xls").Activate
Range("L1").Select
ActiveSheet.Paste
End Sub

I am trying to create a macro that defines a range, and if it finds a cell
with a "Y" in it copies and moves the cells next to it. Unfortunately, it
doesn't seem to do any finding. I just copies whatever it is next to. I'm
probably not defining the loop correctly. Any help?

Dim RngToSearch As Range
Dim RngToFind As Range
Dim RngCopy As Range

Sheets("Bid Generation").Select
Set RngToSearch = Range("G9:G1003")
Set RngToFind = RngToSearch.Find("Y")

If RngToFind Is Nothing Then
Else
For Each cell In RngToFind
ActiveCell.Offset(0, 1).Range("A1:B1").Copy
Sheets("Bid Log").Select
Range("A1").Select
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Sheets("Bid Generation").Select
Next
End If

I would like to know of a way to select specific dates and corresponding
data and paste them into the next two columns.

The column A contains consecutive dates(mm,dd,yyy) covering a 30 year period
and column B contains data corresponding to each date. A way is needed to
automate selecting and copying a specific range of dates w/data for each
month of all years then pasting to columns C and D. ie: Select the date
range and corresponding data for the range 06/01 thru 8/31 for each of the
30 years. Then paste selected cells from columns A and B to columns C and D.
The 30 year time period will also vary in length from one study to the next.
TIA
Art


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