Cant paste - Excel VBA

Question

I am working on a code that will automate a process. I want it to copy from various files to other files with formulas, calculate, and then back again.

I have encountered, a message 'Run-time error '1004', the pastespecial method of range class failed' , when tried to paste. The message appears ONLY when I am using variables to declare the first cell, in order to copy a range of values. When I use a direct cell description everything works fine. I'm also using a custom function for obtaining the column letter, of a given field name.

Function ActiveColumnName(fieldname As String, fieldnames_line   As Integer) As String

Range("A" & fieldnames_line & ":AB" & fieldnames_line).NumberFormat = "@"

Cells.find(What:=fieldname, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate  

ActiveColumnNumber = ActiveCell.Column  


Dim m As Integer
Dim ActiveColumnName As String

ActiveColumnName  = ""

Do While (ActiveColumnNumber > 0)
    m = (ActiveColumnNumber - 1) Mod 26
    ActiveColumnName  = Chr(65 + m) + ActiveColumnName 
    ActiveColumnNumber = Int((ActiveColumnNumber - m) / 26)
Loop

End Function


sub main ()

Dim firstrow_data_main As Integer
Dim firstrow_fieldnames_main As Integer

firstrow_data_main = 16
firstrow_fieldnames_main = 15


Range(ActiveColumnName("<FIELDNAME>", firstrow_fieldnames_main) & firstrow_data_main, Range(ActiveColumnName("ÄÅÔÅ", firstrow_fieldnames_main) & Rows.Count).End(xlUp).Offset(-1)).Select  
Application.CutCopyMode = False
Selection.Copy

Workbooks.Open help_file '"help_file" is any given .xls path with formulas


Dim firstrow_data_help As Integer
Dim firstrow_fieldnames_help As Integer

firstrow_data_help = 7
firstrow_fieldnames_help = 4

'NOW WHEN I USE THIS, DOESN'T WORK:

-> Range(ActiveColumnName("<FIELDNAME>", firstrow_fieldnames_help) & firstrow_data_help).Select 

'WHEN I USE THIS, WORKS FINE:

-> Range("L7").Select 

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
   Application.CutCopyMode = False

End Sub

When it doesn't work, it opens the .xls, and the desirable cell is indeed selected, but no pate. I understand that has something to do with the clipboard, but I cannot figure it out. Any suggestions?

Answer 1

1. Remove all the Select and Activate by referring to the cells directly, See HERE for more information.
2. Look into Cells() instead of Range and avoid the whole need to convert column numbers to letters, as Cells() uses numbers.
3. Avoid the Clipboard when values are the only thing you want and simply assign the value to the new cells (This will require that both ranges are the same size, so use Resize())
4. Always denote the parent sheet of the range, it will cut down on the errors.

Code refactored

Sub main()

Dim firstrow_data_main As Integer
Dim firstrow_fieldnames_main As Integer
Dim rng As Range

Dim tWb As Workbook
Dim ws As Worksheet
Dim tWs As Worksheet
Dim firstrow_data_help As Integer
Dim firstrow_fieldnames_help As Integer



Set ws = ThisWorkbook.ActiveSheet
Set tWb = Workbooks.Open(help_file)
Set tWs = tWb.ActiveSheet

firstrow_data_main = 16
firstrow_fieldnames_main = 15
firstrow_data_help = 7
firstrow_fieldnames_help = 4

With ws
    Set rng = .Range(.Cells(firstrow_data_main, firstrow_fieldnames_main), .Cells(.Rows.Count, firstrow_fieldnames_main).End(xlUp).Offset(-1))
    tWs.Cells(firstrow_data_help, firstrow_fieldnames_help).Resize(rng.Rows.Count, rng.Columns.Count).Value = rng.Value
End With

End Sub

Answer 2

I think the problem may be here:

ActiveColumnName("<FIELDNAME>", firstrow_fieldnames_main)

This implies that ActiveColumnName is a matrix with n x (1 to 2) dimension. If you want to concatenate a name to a variable, you have to use (example):

"YourStringHere" & YourVariableHere & "AnotherString"

Which in your case would be:

ActiveColumnName("<FIELDNAME>" & firstrow_fieldnames_main)

So if I correctly understood (<FIELDNAME> is a bit obscure), the whole command should be:

Range(ActiveColumnName("<FIELDNAME>" & firstrow_fieldnames_help) & "," & firstrow_data_help).Select 

Answer 3

First of all, you should compile your VBA before running. The VBA compiler caught this off the bat:
Dim ActiveColumnName As String
is unnecessary because you assigned ActiveColumnName As String when you defined the function in line 1.

You use a lot of references to active cells and selecting cells. This is known to cause runtime errors. See this post: "How to avoid using Select in Excel Vba Macros".

I suspect the fieldname is not where you think it should be in your help_file, i.e. it isn't in Row 4. This would mean the code wouldn't know where to paste the data. In general, the best way to debug is to piece the code into the smallest action possible to see what's causing the error (see SpreadSheet Guru Strategies). Can you run the following code to see what the output is?

Function ActiveColumnName(fieldname As String, fieldnames_line As Integer) As String

Range("A" & fieldnames_line & ":AB" & fieldnames_line).NumberFormat = "@"
Cells.Find(What:=fieldname, After:=ActiveCell, LookIn:=xlFormulas, LookAt _
    :=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
    False, SearchFormat:=False).Activate
ActiveColumnNumber = ActiveCell.Column
Dim m As Integer
ActiveColumnName = ""

Do While (ActiveColumnNumber > 0)
    m = (ActiveColumnNumber - 1) Mod 26
    ActiveColumnName = Chr(65 + m) + ActiveColumnName
    ActiveColumnNumber = Int((ActiveColumnNumber - m) / 26)
Loop

End Function
Sub main()

Workbooks.Open "help_file" '"help_file" is any given .xls path with formulas
Dim firstrow_data_help As Integer
Dim firstrow_fieldnames_help As Integer
firstrow_data_help = 7
firstrow_fieldnames_help = 4

MessageBox = ActiveColumnName("FIELDNAME", firstrow_fieldnames_help) & firstrow_data_help

End Sub

Answer 4

thank you all for your response! I've tried all of your suggestions one by one but encountered various issues along the way. Nevertheless all of your suggestions helped me to grow a different perspective on the subject. The solution I've end up with, derives from your suggestion to discard ".select" as a way of reference and to use "rng" variables and of course to get rid of the double reference "ActiveColumnName".I know i have a long way to go but for the moment the this thing works!!thanks!!

Sub main()

Dim firstrow_data_main As Integer
Dim firstrow_fieldnames_main As Integer
Dim firstrow_data_help As Integer
Dim firstrow_fieldnames_help As Integer



firstrow_data_main = 16
firstrow_fieldnames_main = 15
firstrow_data_help = 7
firstrow_fieldnames_help = 4


Dim rng1 As Range
Dim rng2 As Range

Set rng1 = Range(ActiveColumnName("<FIELDNAME>", firstrow_fieldnames_main) & firstrow_data_main, Range(ActiveColumnName("<FIELDNAME>", firstrow_fieldnames_main) & Rows.Count).End(xlUp).Offset(-1))

cells_selected = rng1.Rows.Count

Workbooks.Open <help_file>


Set rng2 = Range(ActiveColumnName("<FIELDNAME>", firstrow_fieldnames_help) & firstrow_data_help, Range(ActiveColumnName("<FIELDNAME>", firstrow_fieldnames_help) & cells_selected + firstrow_data_help - 1))

rng1.Copy rng2  

End Sub