Free Microsoft Excel 2013 Quick Reference

Excel macro error run time 1004 paste method of worksheet class failed Results

I am trying to run a macro whereby I sort some info on one worksheet and copy the sorted info twice into another worksheet so I can then print. The copy of the current macro content is below. I continue to get the following error... Excel Macro Error - Run time error 1004 - Paste method of worksheet class failed. Please help

Hello,

I've created excel sheet contains macros and connected with SQL query.
I am getting below mentioned error.

"Run-time error '1004' Select Method of Worksheet Class failed Options "

Note: After recording the macro i've hide worksheet through VBA code
with password. Before hiding worksheet macros was run fine but after
hiding the worksheet it shows me above error.

I am getting error on line " sheets("Sheet2"). Select. (4th line)

Sheet2 which is hidden by VBA code after macros was recorded.

Below is whole query:

Sub Finaloutput()
'
' Finaloutput Macro
'
' Keyboard Shortcut: Ctrl+a
'
Sheets("Sheet2").Select
Cells.Select
Selection.Copy
Sheets("Sheet6").Select
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Selection.Subtotal GroupBy:=1, Function:=xlSum, TotalList:=Array
(1, 2, 3, 4, _
5, 6, 7, 8, 9, 10, 11, 12, 13, 14, 15), Replace:=True,
PageBreaks:=False, _
SummaryBelowData:=True
Selection.Copy
Sheets("Sheet8").Select
Range("J13").Select
ActiveWindow.SmallScroll Down:=-15
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Columns("A:A").Select
Selection.SpecialCells(xlCellTypeBlanks).Select
Application.CutCopyMode = False
Selection.EntireRow.Delete
Range("C8").Select
Cells.Replace What:=" Total", Replacement:="", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False,
_
ReplaceFormat:=False
Cells.Select
Selection.Copy
Sheets("Sheet7").Select
ActiveWindow.SmallScroll Down:=-24
Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("K11").Select
Sheets("Sheet5").Select
Range("A7").Select
Sheets("Sheet4").Select
Range("AE5").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet5").Select
Range("A7").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A7").Select
Application.CutCopyMode = False
ActiveWorkbook.Worksheets("Sheet5").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Sheet5").Sort.SortFields.Add Key:=Range
("A7"), _
SortOn:=xlSortOnValues, Order:=xlAscending,
DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Sheet5").Sort
.SetRange Range("A7:N162")
.Header = xlNo
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Range("G10").Select
Selection.FormatConditions.Add Type:=xlCellValue,
Operator:=xlNotEqual, _
Formula1:="=0"
Selection.FormatConditions
(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Interior
.PatternColorIndex = xlAutomatic
.Color = 65535
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = False
Range("J15").Select
End Sub

First off, let me start off by saying that I'm not the creator of the macro, so I don't know all the details. I have been given the file and asked to help find a solution, where best to go to ask for help than here right!
The error is "Run-time error '1004' Paste method of Worksheet class failed"
The macro used to work in excel 2003 if I remember correctly, but definetly doesn't in the 2007 version because of this error. I've done some backtracking down the forums and research but still feel rather hopeless, so I'll post the macro in here, should give you sufficient information.

Sub Danke6()
'
' Danke6 Macro
' Macro recorded 27-05-2005 
'

'
    ActiveSheet.Unprotect
    Selection.Font.ColorIndex = 3
    Selection.Copy
    Range("AG20:AM20").Select
    Application.CutCopyMode = False
    Selection.Clear
    ActiveSheet.Paste
    Range("Z20:AV22").Select
    Selection.PrintOut Copies:=2
    Range("Z20:AT20").Select
    Selection.Copy
    Sheets("Liste").Select
    Application.CutCopyMode = False
    ActiveSheet.Unprotect
    Range("A4").Select
    ActiveSheet.PasteSpecial Format:="VALU", Link:=False, DisplayAsIcon:= _
        False
    Range("V4").Select
    ActiveCell.FormulaR1C1 = "=NOW()"
    Range("V5").Select
    Selection.Copy
    Range("V4").Select
    Selection.PasteSpecial Paste:=xlFormats, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Application.CutCopyMode = False
    Selection.Copy
    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
    Range("W5").Select
    Application.CutCopyMode = False
    Selection.AutoFill Destination:=Range("W4:W5"), Type:=xlFillDefault
    Range("W4:W5").Select
    Range("A4").Select
    Selection.EntireRow.Insert
    Range("A4").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    Sheets("TICKET").Select
    Range("Z20").Select
    ActiveSheet.Protect DrawingObjects:=True, Contents:=True, Scenarios:=True
    ActiveWorkbook.Save
End Sub
Help much appreciated!

I am having trouble with a macro that is giving me the error Run-Time error 1004 (Paste Method of Worksheet Class Failed)

The code is

	VB:
	
 getfile5() 
     
    Call getfile(2) 
     
    Dim ie As Object 
    Set ie = CreateObject("internetexplorer.application") 
     
     
    Windows("TRANS CHECKS CALENDAR.XLS").Activate 
    Sheets(2).Select 
    ActiveSheet.Paste 
    Range("A1").Select 
     
     ' ie.Visible = False
     
    Windows("TRANS CHECKS CALENDAR.XLS").Activate 
    Sheets("Main").Select 
    Range("A1").Select 
     
     
     
End Sub 

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

The macro is timing out on the ActiveSheet.Paste entry...The funny thing is that a number of us can get the macro to work and several of us are getting this error...I am thinking that it is a setting in excel that is causing this...Any help is appreciated. Thanks in advance.

I have just installed Excel 2007 and wish to copy values from one worksheet (data downloaded from online banking in csv format) to the first blank row in another. I am manually copying a range of cells in the first worksheet and then, in the second worksheet, running a macro which includes the code:

Application.ScreenUpdating = False
rownum = 4
Cells(rownum, 1).Select
While Selection.Value ""
rownum = rownum + 1
Cells(rownum, 1).Select
Wend
ActiveSheet.Paste
. . . . and then various formatting functions

When I try to run the macro from a UserForm or by selecting it in the list of macros dialog box, it fails with the message

"Run Time Error 1004 - Paste method of Worksheet class failed"

The macro runs OK from a keyboard shortcut.

I get the same problem if I test it by using Record Macro to create a paste action and then run the recorded macro.

(Running Vista Home Premium)

Can anyone help please?

We recently upgraded some PCs from Office 2000 to Office 2003, and now when
we run an Excel Macro that copies data, I am getting the following error.

Run time Error 1004 "Paste method of Worksheet class failed"

The code causing the exception error is "ActiveSheet.Paste". Is this command
no longer legal in Office 2003?? Is there some other VB command that should
be used other than "ActiveSheet.Paste" for 2003 or a security patch that is
needed?

We recently upgraded some PCs from Office 2000 to Office 2003, and now when
we run an Excel Macro that copies data, I am getting the following error.

Run time Error 1004 "Paste method of Worksheet class failed"

The code causing the exception error is "ActiveSheet.Paste". Is this command
no longer legal in Office 2003?? Is there some other VB command that should
be used other than "ActiveSheet.Paste" for 2003 or a security patch that is
needed?

I have a macro which was developed in Excel 2007 (version 12.0?). When I run it in Excel 2010 (version 14.0), I get some inconsistent errors as follows:

I have this code operating on one worksheet:

	VB:
	
Range("A1").Select 
ActiveSheet.Paste 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
which is acceptable. A little later, I have, operating on a different worksheet:

	VB:
	
Range("A6").Select 
ActiveSheet.Paste 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
which gives a run-time error '1004': Paste method of Worksheet class failed.

Commenting that statement out and running again. I have this code:


	VB:
	
Cells.Find(What:="3", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ 
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False) _ 
.Activate 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
which runs OK. A few lines further on:

	VB:
	
Cells.Find(What:="6", After:=ActiveCell, LookIn:=xlFormulas, LookAt:= _ 
xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False) _ 
.Activate 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
fails with a run-time error '91': Object variable or with block variable not set.

What's happening and how do I fix it? These appear to be the only sources of run-time error.

I'm running the macro in Windows 7 on an ASUS Eee Slate. I can't test it anywhere else.

I have 200+ workbooks containing multiple sheets, of which sheet7 containes Proj Manager and Developer hours for project tasks. I am trying to paste link sheet 7 from all these workbooks to one consolidate workbook. I Receive error "Run-time error '1004': Copy method of Worksheet class failed" when running a macro after it adds the 84th sheet to the master workbook. Debug brings me to the line of code:

Wkb.Sheets(7).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

I can manually add additional sheets to the master workbook and manually paste-link, so it doesn't look like a memory or application limitation. If I try to run the macro again, it starts from the beginning of the files it is pulling in and errors out immediately when attempting to add the first file. I use Ofc standard 2003 / Excel 2003-SP2.

Any idea's - I tried to integrate some counter methods, but was unsuccessful.

My code:
Sub Combine()

Dim Path As String
Dim FileName As String
Dim Wkb As Workbook
Dim WS As Worksheet

Application.EnableEvents = False
Application.ScreenUpdating = False
Path = "P:iSET" 'location of files
FileName = Dir(Path & "*.xl*", vbNormal)
With Application
.Calculation = xlManual
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

Do Until FileName = ""
Set Wkb = Workbooks.Open(FileName:=Path & "" & FileName)

'This grabs the required sheet out of the Excel template
'and pastes to the consolidate workbook as a new sheet
Wkb.Sheets(7).Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

'unprotects sheet to change from original
ActiveSheet.Unprotect

'Copies the Project Number located in Master Sheet
Wkb.Sheets(1).Range("C1").Copy
Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("A:A").Select
Application.CutCopyMode = False
Selection.Insert Shift:=xlToRight
Range("B2").Select
Selection.Copy
Range("A5").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("A5:A28"), Type:=xlFillDefault
Range("A5:A28").Select

'Adds WeekNum to each summary sheet
ActiveSheet.Range("N4").Select
ActiveCell.FormulaR1C1 = "=INT(((RC[-8])-WEEKDAY(RC[-8])-DATE(YEAR(RC[-8]),1,1))/7)+2"
Selection.Copy
Range("N6:N30").Select
ActiveSheet.Paste

'paste the captured text as the new sheet name in the consolidated file
ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count).Name = Wkb.Sheets(1).Range("C1")

Wkb.Close False
FileName = Dir()

Loop
Application.EnableEvents = True
Application.ScreenUpdating = True

With Application
.Calculation = xlAutomatic
.MaxChange = 0.001
End With
ActiveWorkbook.PrecisionAsDisplayed = False

End Sub

Using Excel 97:
My objective is as follows:-
1. User manually selects an area of one column of workbook 1 (name of book will vary)
2. User then switches to workbook 2 (fixed name, can be opened either before or after the copy in step 1)
3. In workbook 2 user clicks a button on "statistics" sheet to run a macro which:-
i)Turns off automatic calculation
ii)Goes to "data entry" sheet and finds first empty column (via offset command)
iii) Pastes the data copied at step 1
iv) Returns to "statistics" sheet

The macro i created was as follows

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 25/10/02 by Simon Clark
'

With Application
.Calculation = xlManual

End With
Sheets("data entry").Select
Range("A2").Activate

Do Until ActiveCell.Value = ""

ActiveCell.Offset(COLUMNOffset:=1).Activate
Loop
ActiveSheet.Paste 'THIS IS WHERE THE ERROR "RUN TIME ERROR 1004 - PASTE METHOD OF WORKSHEET CLASS FAILED "OCCURS
ActiveCell.Select
Application.CutCopyMode = False
Sheets("statistics").Select

End Sub

Although the user copies the data (presumably to the clipboard) by the time the macro gets to the paste command i get an error - it appears that the macro has lost the contents of what it was supposed to be copying. If i incorporate the copy part into the macro it works fine, BUT this is not an option because the name of the workbook being copied from will always vary, as will the size of the area being copied.

Perhaps some of the commands at the start of my macro are causing the loss of data to be copied.
Can someone please give me a solution or workaround. Why is the copied selection being lost.

I imagine i could amend the start of my macro so that it dumps the data somewhere, then finds the first available column, then recopies the dumped data and pastes it where required. However, surely there is a much simpler way.

Hi all,

First post & hoping for some help. Background is that I have an Excel file containing around 35,000 lines of data, which needs to be produced every month (management requirement, can't change it). It has around 30 columns which need to be formularised for each of those rows. Doing this manually results in a frozen computer, unable to cope with the amount of calculations (even if done column by column).

So, I have a macro which cycles through each row, writes all the formulae in, calculates that row, and then does a pastespecial values for the row.

I've tried both:
Worksheets(data_tab_name).Range(Cells(i, 1), Cells(i, 65)).Select
Selection.Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,_
SkipBlanks:= False, Transpose:=False

And the more efficient:
Worksheets(data_tab_name).Range(Cells(i, 1), Cells(i, 65)) = Worksheets(data_tab_name).Range(Cells(i, 1), Cells(i, 65)).Value

With both of these methods, it happily starts at row 2, and works on through until it gets to row 13109, at which point it only pastes half of the row, and then crashes, giving the run-time 1004 error, and "Paste Special Method of Range Class Failed" message.
If I then start it at 13109, it gets to 26216 and crashes.
If I tell it to start at 2, end at 13108, the entire thing executes perfectly.

Please, pleeeease, does someone out there know why and can explain to me how to stop it doing this? I've even tried getting it to do this in blocks of ten thousand, and it still crashed...

thx in advance!

Hi Members,

OK, this is ridiculous....I feel very incompetent right about now.

Using the macro recorder, I am attempting to copy a chart from one sheet and paste it into "sheet2" (as a chart, not a picture etc). This is the code it generates (excel 2007).

Sub copyandpastechart()

    ActiveSheet.ChartObjects("Chart 1").Activate
    ActiveChart.ChartArea.Copy
    Sheets("Sheet2").Select
    Range("D7").Select
    ActiveSheet.Paste
End Sub
I continually get a 'Run-time error 1004: paste method of worksheet class failed' message.

From searching around, it seems that a lot of people have similar problems, for which people have suggested a multitude of possible solutions.

However, no matter how I modify the code in accordance with those suggestions, nothing works!!!

What's up with this crazy behavior??

Thanks for any advice.

I have a macro which was developed in Excel 2007 (version 12.0?). When I run it in Excel 2010 (version 14.0), I get some inconsistent errors as follows:

I have this code operating on one worksheet:

   
Range("A1").Select
    ActiveSheet.Paste
which is acceptable. A little later, I have, operating on a different worksheet:

   
Range("A6").Select 
    ActiveSheet.Paste
which gives a run-time error '1004': Paste method of Worksheet class failed.

Commenting that statement out and running again. I have this code:

    Cells.Find(What:="3", After:=ActiveCell, LookIn:=xlFormulas,
LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False) _
        .Activate
which runs OK. A few lines further on:

    Cells.Find(What:="6", After:=ActiveCell,
LookIn:=xlFormulas, LookAt:= _
        xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, MatchCase:=False) _
        .Activate
fails with a run-time error '91': Object variable or width block variable not set.

What's happening and how do I fix it? These appear to be the only sources of run-time error.

I'm running the macro in Windows 7 on an ASUS Eee Slate. I can't test it anywhere else.

Hello,

So I am trying to Create a macro for a large excel file I deal with at work. Since I know Record captures every thing you do, including the mistakes what I am trying to do is go through the motions and get all the formulas set and saved working copy of the report. I am then trying to copy and paste the formulas from the file I worked in so when all is said and done it does not have to capture all the mistakes and whatnot.

It does not seem to be working. I am getting the error

Run-time error '1004':
Paste method of Worksheet class failed

Does anyone have some reading they can point me to? (search hasnt helped thus far) or suggestions. Maybe I am missing something?

Hi all,

I'm trying to run a macro that copies some data from one
worksheet to 50 other worksheets using some specific
criterias. While the macro copies everything correctly for
around half of the data it stops after that and gives me
the following error messages:

"Excel cannot complete this task with available resources.
Choose less data or close other applications"

In the VBE it gives me:

"Run-time error 1004: Paste method of worksheet class has
failed".

Then when I close Excel after these messages:

"The picture is too large and will be truncated".

Has anyone experienced something like that before? I don't
have any other applications open and I don't think that
this simple procedure should cause any problems with
Excel. It's really strange.

Any help is greatly appreciated!!!!

Manuel

Hi all,

I'm trying to run a macro that copies some data from one worksheet to 50 other worksheets using some specific criterias. While the macro copies everything correctly for around half of the data it stops after that and gives me the following error messages:

"Excel cannot complete this task with available resources. Choose less data or close other applications"

In the VBE it gives me:

"Run-time error 1004: Paste method of worksheet class has failed".

Then when I close Excel after these messages:

"The picture is too large and will be truncated".

Has anyone experienced something like that before? I don't have any other applications open and I don't think that this simple procedure should cause any problems with Excel. It's really strange.

Any help is greatly appreciated!!!!

Manuel

Good afternoon all,

I am having a problem converting a marco to a command button. When I run
this marco .... TOOLS, MARCO, MACROS..., (name).. RUN , the marco runs
correct. When I copy clip the marco to the View Code in the command button,
it does not work. Below is a attached file that might be able to explain it
better. Please help, or what can I do to fix.

Thanks,
ALP

This is what I have in the accutal MARCO. (this works, when I run the marco)

Dim myRange As Range
Set myRange = Worksheets("Work").Range("L1")

If myRange = 1 Then

Range("R4:R36").Select
Selection.Copy
Sheets("QDATA").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.SmallScroll Down:=1
Sheets("WORK").Select
ActiveWorkbook.SAVE
Range("A2").End(xlUp).Select

ElseIf myRange = 2 Then

Range("R4:R36").Select
Selection.Copy
Sheets("QDATA").Select
Range("D4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.SmallScroll Down:=1
Sheets("WORK").Select
ActiveWorkbook.SAVE
Range("A2").End(xlUp).Select

This is what happens when I try to set this marco to be on my
CommandButton1. (doesn’t work)

Private Sub CommandButton1_Click()

Dim myRange As Range
Set myRange = Worksheets("Work").Range("L1")

If myRange = 1 Then

Range("R4:R36").Select
Selection.Copy
Sheets("QDATA").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.SmallScroll Down:=1
Sheets("WORK").Select
ActiveWorkbook.SAVE
Range("A2").End(xlUp).Select

ElseIf myRange = 2 Then

Range("R4:R36").Select
Selection.Copy
Sheets("QDATA").Select
Range("D4").Select ................ this is what is highlighted as error
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.SmallScroll Down:=1
Sheets("WORK").Select
ActiveWorkbook.SAVE
Range("A2").End(xlUp).Select

My error message is:

Run-Time error '1004':
Select Method of Range class failed.

I was in need of a macro, so I searched this forum and found a January 20th, 2011 thread where "tabishimam" had given code to "gaexcel" to accomplish the very thing I needed to do.

"gaexcel" and I both needed a macro which would:

"Copy the value of a cell in sheet 2 and paste it into the 1st empty cell of a column in sheet 1.
For example,
Copy Cell A1 in sheet 2 and paste it into the 1st empty cell of column A in sheet 1."

"tabishimam" offered the following code to "gaexcel" and when "gaexcel" ran it he got:


	VB:
	
Select method of Range class failed 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
and when debugged, highlighted:

	VB:
	
Sheets(sheetTo).Range(colToBeCopiedTo & firstEmptyRow).Select 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
I also ran the code and got the same results.

"tabishimam" then offered "I think you have placed the macro in the code for a specific worksheet.
You need to place the macro in a separate module in the vbaproject of your excel file. Then create your button in the sheet you desire and then select and assign your macro to the button in the dialog box that subsequently appears."

I had placed the macro in a separate module in the vbaproject of my excel file, created a button on sheet2 and assigned the macro to it.

"gaexcel's" original thread ("Paste a cell's value into the 1st empty cell"), ended after "tabishimam's" last suggestion without any subsequent entries from "gaexcel" to indicate if had worked for him or not, nor what solution he had reached.

I was told I shouldn't hijack the original thread by asking if it had worked, that I should start a new thread, so this is what I'm doing.

Can someone help me make this (original "tabishimam"), code work for me? Thanks for all you guru's time and efforts, I really appreciate the work put in and the answers in this forum.

Original "tabishimam" code:

	VB:
	
 Macro1() 
    Dim cellToBeCopied As String 
    Dim colToBeCopiedTo As String 
    Dim sheetFrom As String 
    Dim sheetTo As String 
    Dim firstEmptyRow As Integer 
     
     'specify the cell to be copied
    cellToBeCopied = "A1" 
     'specify the column to which the value has to be copied
    colToBeCopiedTo = "A" 
     'specify sheet from which value is to be copied
    sheetFrom = "Sheet2" 
     'specify sheet to which value is to be copied
    sheetTo = "Sheet1" 
     'Initialise firstEmptyRow to 1
    firstEmptyRow = 1 
     
    Sheets(sheetTo).Range(colToBeCopiedTo & firstEmptyRow).Select 
    If ActiveCell.Value  vbNullString Then 
        If ActiveCell.Offset(1, 0).Value  vbNullString Then 
            firstEmptyRow = Selection.End(xlDown).Row + 1 
        Else 
            firstEmptyRow = Selection.Row + 1 
        End If 
    End If 
    Sheets(sheetTo).Range(colToBeCopiedTo & firstEmptyRow).Value = Sheets(sheetFrom).Range(cellToBeCopied).Value 
End Sub 

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

EDIT by Moderator: source thread - Paste a cell's value into the 1st empty cell

I have a macro (code below) that I have created an add in from and it works fine in Excel 2003 but when running in Excel 2000 it stops with the following error on the line "Selection.PasteSpecial Paste:=xlPasteColumnWidths"

Run Time Error '1004'
PasteSpecial Method of Range class failed

Code:
Sub OrderSummary()
    
    Set wsSummary = Worksheets.Add
    ActiveSheet.Move before:=Worksheets("Cover")
    wsSummary.Name = "OrderSummary"
    
    Worksheets(3).Range("A1:E1").Copy
    Sheets("OrderSummary").Select
    Selection.PasteSpecial Paste:=xlPasteColumnWidths
            
    Worksheets(3).Range("A1:E1").Copy wsSummary.Range("A1:E1")

    For X = 3 To 8
        Set sht = Worksheets(X)
        If X = 7 Then
            sht.Columns("C:C").Hidden = True
            sht.Cells.AutoFilter Field:=4, Criteria1:=">=1"
            C = "F"
        Else
            sht.Cells.AutoFilter Field:=3, Criteria1:=""
            C = "E"
        End If
          If sht.Range("A65536").End(xlUp).Row > 1 Then
            Set rngCopy = sht.Range("A2:" & C & sht.Range("A65536").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
            Set rngPaste = wsSummary.Range("A" & Range("A65536").End(xlUp).Row + 1)
            rngCopy.Copy rngPaste
          End If
        If X = 7 Then
            sht.Columns("C:C").Hidden = False
            sht.Cells.AutoFilter
        Else
            sht.Cells.AutoFilter Field:=3
            sht.Cells.AutoFilter Field:=9, Criteria1:=""
           If sht.Range("G65536").End(xlUp).Row > 1 Then
                Set rngCopy = sht.Range("G2:K" & sht.Range("G65536").End(xlUp).Row).SpecialCells(xlCellTypeVisible)
                Set rngPaste = wsSummary.Range("A" & Range("A65536").End(xlUp).Row + 1)
                rngCopy.Copy rngPaste
            End If
            sht.Cells.AutoFilter
        End If
    Next X
    
End Sub

Can anyone suggest how I can resolve this issue

Thanks

Good afternoon all,

I am having a problem converting a marco to a command button. When I run
this marco .... TOOLS, MARCO, MACROS..., (name).. RUN , the marco runs
correct. When I copy clip the marco to the View Code in the command button,
it does not work. Below is a attached file that might be able to explain it
better. Please help, or what can I do to fix.

Thanks,
ALP

This is what I have in the accutal MARCO. (this works, when I run the marco)

Dim myRange As Range
Set myRange = Worksheets("Work").Range("L1")

If myRange = 1 Then

Range("R4:R36").Select
Selection.Copy
Sheets("QDATA").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.SmallScroll Down:=1
Sheets("WORK").Select
ActiveWorkbook.SAVE
Range("A2").End(xlUp).Select

ElseIf myRange = 2 Then

Range("R4:R36").Select
Selection.Copy
Sheets("QDATA").Select
Range("D4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.SmallScroll Down:=1
Sheets("WORK").Select
ActiveWorkbook.SAVE
Range("A2").End(xlUp).Select

This is what happens when I try to set this marco to be on my
CommandButton1. (doesn’t work)

Private Sub CommandButton1_Click()

Dim myRange As Range
Set myRange = Worksheets("Work").Range("L1")

If myRange = 1 Then

Range("R4:R36").Select
Selection.Copy
Sheets("QDATA").Select
Range("C4").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.SmallScroll Down:=1
Sheets("WORK").Select
ActiveWorkbook.SAVE
Range("A2").End(xlUp).Select

ElseIf myRange = 2 Then

Range("R4:R36").Select
Selection.Copy
Sheets("QDATA").Select
Range("D4").Select ................ this is what is highlighted as error
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
ActiveWindow.SmallScroll Down:=1
Sheets("WORK").Select
ActiveWorkbook.SAVE
Range("A2").End(xlUp).Select

My error message is:

Run-Time error '1004':
Select Method of Range class failed.


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