VBA copy/paste range of cells not working

[beefbroth]

I've lurked around the forum many times over the years going through various solutions, and I feel a little bad that now I have to ask a question of my own, but I've reached a point where I'm completely out of ideas. Apologies up front if this runs too long.

Long story short: I can't get a VBA function to copy a range of cells from one sheet to a range starting with the active cell on another sheet.

Here's the situation: I maintain a spreadsheet for a weekly report I have to generate. The source data is 17 columns and a random number of rows, typically into the thousands. From that I use several formulas and functions to add 16 more columns of data. I'm trying to streamline the process of getting those additional columns set up and populated. The problem I'm running into involves moving data to one sheet (Sheet1) from another (Sheet2). Sheet2 is sort of a repository of certain rows, but it also contains identifiers that, if populated, need to be moved into Sheet1, and then those rows can be filtered out (so they're still present in case anyone needs to refer to them, but suppressed from weekly counts).

The data that needs to be moved from Sheet2 is a 1 row by 6 column range (AB - AG), and it needs to be moved into a matching 1x6 range on Sheet1 (AA-AF), starting from the active cell calling the formula. So the call is done from AA1, the data needs to be put in AA1 - AF1.

My first step (and I'm sure there are many better ways of doing this - blame my general ignorance of the finer points of Excel) is to use this in the active cell to determine if there's a matching row between the two sheets, and then pass the cell address of the start of Sheet2's range into a function that will do the actual moving of data:

=if(countif(Sheet2!V:V,U1)>0,move_data(address(match(u1,Sheet2!V:V,0),28,4)))

Columns U on Sheet1 and V on Sheet2 are for a concatenation code I use to compare the lines. The 28 is column AB, the column where the data to be moved starts.

Up until now, all I've been able to do is to copy the contents of the single cell idenfitied by the address(match()) into the active cell on Sheet1. Then I have to manually find the other 5 cells and move them over. I'd like to just have the move_data function do it for me, but I haven't been able to get a single method to work. I've tried setting the two ranges equal to each other:

public function move_data (start_cell as string)

dim data_range as string
data_range = Sheet2.range(start_cell).address(0,0) & ":" & Sheet2.range(start_cell).offset(0,5).address(0,0)

dim target_range as string
target_range = activecell.address(0,0) & ":" & activecell.offset(0,5).address(0,0)

Sheet1.range(target_range).value = Sheet2.range(data_range).value

End function

I've tried 2 flavors of a more proper copy function:

public function move_data (start_cell as string)

dim data_range as string
data_range = Sheet2.range(start_cell).address(0,0) & ":" & Sheet2.range(start_cell).offset(0,5).address(0,0)

dim target_range as string
target_range = activecell.address(0,0) & ":" & activecell.offset(0,5).address(0,0)

Sheet2.range(data_range).copy(target_range)
'Also tried
'Sheet2.range(data_range).copy
'Sheet1.paste(Sheet1.range(target_range))

end function

I've tried recording a macro of me moving the data manually and using exactly the same syntax/format Excel did to set it up:

public function move_data (start_cell as string)

dim data_range as string
data_range = Sheet2.range(start_cell).address(0,0) & ":" & Sheet2.range(start_cell).offset(0,5).address(0,0)

dim target_range as string
target_range = activecell.address(0,0) & ":" & activecell.offset(0,5).address(0,0)

sheets("Sheet2").select
range(data_range).select
selection.copy
sheets("Sheet1").select
range("U1").select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False

end function

I've tried getting angry at it and just using a series of manual variable assignments:

public function move_data (start_cell as string)

dim data_range as string
data_range = Sheet2.range(start_cell).address(0,0) & ":" & Sheet2.range(start_cell).offset(0,5).address(0,0)

dim target_range as string
target_range = activecell.address(0,0) & ":" & activecell.offset(0,5).address(0,0)

Dim ab As String
Dim ac As String
Dim ad As Date
Dim ae As String
Dim af As String
Dim ag As Date

ab = Sheet2.Range(start_cell).Value
ac = Sheet2.Range(start_cell).Offset(0, 1).Value
ad = Sheet2.Range(start_cell).Offset(0, 2).Value
ae = Sheet2.Range(start_cell).Offset(0, 3).Value
af = Sheet2.Range(start_cell).Offset(0, 4).Value
ag = Sheet2.Range(start_cell).Offset(0, 5).Value

Sheet1.Range(ActiveCell.Value) = ab
Sheet1.Range(ActiveCell.Offset(0, 1)).Value = ac
Sheet1.Range(ActiveCell.Offset(0, 2)).Value = ad
Sheet1.Range(ActiveCell.Offset(0, 3)).Value = ae
Sheet1.Range(ActiveCell.Offset(0, 4)).Value = af
Sheet1.Range(ActiveCell.Offset(0, 5)).Value = ag

end function

I've also tried putting each method into a separate sub and having the function call that. Regardless of the setup, I either get Wrong Data Type errors, Circular Reference errors, or the active cell just goes to 0. Copying a range of cells like this seems like it should be the simplest thing in the world, but clearly I have no idea how to do it. Can someone help me get this thing working?

 

[rlv01]

There are many different ways to do something like that. One example.

Sub move_data(start_cell As String)
    Dim CopyRange As Range, PasteRange As Range
    
    With Sheet2
        On Error Resume Next
        Set CopyRange = .Range(start_cell).Resize(, 6)
        On Error GoTo 0
        
        If Not CopyRange Is Nothing Then
            Set PasteRange = ActiveCell.Resize(CopyRange.Rows.Count, CopyRange.Columns.Count)
            CopyRange.Copy PasteRange
        Else
            MsgBox start_cell & " is an invalid range"
        End If
    End With
End Sub

 

[beefbroth]

There are many different ways to do something like that. One example.

Sub move_data(start_cell As String)
    Dim CopyRange As Range, PasteRange As Range
   
    With Sheet2
        On Error Resume Next
        Set CopyRange = .Range(start_cell).Resize(, 6)
        On Error GoTo 0
       
        If Not CopyRange Is Nothing Then
            Set PasteRange = ActiveCell.Resize(CopyRange.Rows.Count, CopyRange.Columns.Count)
            CopyRange.Copy PasteRange
        Else
            MsgBox start_cell & " is an invalid range"
        End If
    End With
End Sub

I wish this did work, because it's so much more compact and well-crafted than anything I normally Frankenstein together. Unfortunately, it behaves the same way as a lot of my previous attempts. 0 in AA, nothing in AB-AF. It doesn't go into the MsgBox, though.

 

[Joe4]

I see that you are trying to create Functions. Functions typically are just used to return a value (like a formula would).
When you want to perform a bunch of actions on your worksheet, you typically use Sub procedures, not Functions.

 

[beefbroth]

I see that you are trying to create Functions. Functions typically are just used to return a value (like a formula would).
When you want to perform a bunch of actions on your worksheet, you typically use Sub procedures, not Functions.

That was actually something I learned when I started trying to put this together. All the attempts I've made at it have been done as both Functions and Subs. It didn't seem to have any effect on the outcome, but I do understand that it's probably going to be a Sub in its final form.

 

[Joe4]

IMO, the best approach is to usually break your project down to individual tasks, and start there.
Work on the first "task" (i.e. copy data from point A to point B), and when you get that working, then start working on the next task to add to the first.

Try that, and if you need help, post a small sample of your data (or dummied-up data) showing what you are starting with and then show your desired result.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

 

[rlv01]

I wish this did work, because it's so much more compact and well-crafted than anything I normally Frankenstein together. Unfortunately, it behaves the same way as a lot of my previous attempts. 0 in AA, nothing in AB-AF. It doesn't go into the MsgBox, though.

I tested it, and it works for me. Perhaps some of your assumptions are different than mine. Are you sure that the range you are trying to copy has data? You can try this version with debug code and see what you get.

Sub move_data(start_cell As String)
    Dim CopyRange As Range, PasteRange As Range
    
    With Sheet2
        On Error Resume Next
        Set CopyRange = .Range(start_cell).Resize(, 6)
        On Error GoTo 0
        
        If Not CopyRange Is Nothing Then
            Set PasteRange = ActiveCell.Resize(CopyRange.Rows.Count, CopyRange.Columns.Count)
            
            'Begin debug code
            Dim S As String
            S = "'start_cell' value = " & start_cell & vbCr
            S = S & "Copy  range: " & CopyRange.Address(External:=True) & vbCr
            S = S & "Paste range: " & PasteRange.Address(External:=True)
            Debug.Print vbCr & "Debug Info:" & vbCr & S
            MsgBox S, vbOKOnly + vbInformation, "Debug Information"
            'end debug code
            
            CopyRange.Copy PasteRange
        Else
            MsgBox start_cell & " is an invalid range"
        End If
    End With
End Sub

 

[beefbroth]

I tested it, and it works for me. Perhaps some of your assumptions are different than mine. Are you sure that the range you are trying to copy has data? You can try this version with debug code and see what you get.

Sub move_data(start_cell As String)
    Dim CopyRange As Range, PasteRange As Range
   
    With Sheet2
        On Error Resume Next
        Set CopyRange = .Range(start_cell).Resize(, 6)
        On Error GoTo 0
       
        If Not CopyRange Is Nothing Then
            Set PasteRange = ActiveCell.Resize(CopyRange.Rows.Count, CopyRange.Columns.Count)
           
            'Begin debug code
            Dim S As String
            S = "'start_cell' value = " & start_cell & vbCr
            S = S & "Copy  range: " & CopyRange.Address(External:=True) & vbCr
            S = S & "Paste range: " & PasteRange.Address(External:=True)
            Debug.Print vbCr & "Debug Info:" & vbCr & S
            MsgBox S, vbOKOnly + vbInformation, "Debug Information"
            'end debug code
           
            CopyRange.Copy PasteRange
        Else
            MsgBox start_cell & " is an invalid range"
        End If
    End With
End Sub

Using that does show the correct range on both sheets in the debug output. I've also tried adding message boxes for CopyRange and stepped through each cell in it, to make sure the data is what I'm expecting to have moved over. Everything looks good, but the result is still 0 in AA, nothing in AB-AF.

 

[beefbroth]

IMO, the best approach is to usually break your project down to individual tasks, and start there.
Work on the first "task" (i.e. copy data from point A to point B), and when you get that working, then start working on the next task to add to the first.

Try that, and if you need help, post a small sample of your data (or dummied-up data) showing what you are starting with and then show your desired result.

MrExcel has a tool called “XL2BB” that lets you post samples of your data that will allow us to copy/paste it to our Excel spreadsheets, so we can work with the same copy of data that you are. Instructions on using this tool can be found here: XL2BB Add-in

Note that there is also a "Test Here” forum on this board. This is a place where you can test using this tool (or any other posting techniques that you want to test) before trying to use those tools in your actual posts.

That's pretty much where I am at the moment. My existing function:

Public Function move_data_original(target_cell As String) As String

If IsEmpty(Sheet2.Range(target_cell)) = False Then
move_data_original = Sheet2.Range(target_cell)

End If

End Function

...will copy that one cell the address(match()) outside gives it perfectly. I just can't seem to get anything that does more than that one cell to work.

Sadly, I'm not going to be able to use XL2BB. I can't install add-ins like that on my system. Usage policy and all.

 

[rlv01]

Using that does show the correct range on both sheets in the debug output. I've also tried adding message boxes for CopyRange and stepped through each cell in it, to make sure the data is what I'm expecting to have moved over. Everything looks good, but the result is still 0 in AA, nothing in AB-AF.

Did you use the code as I posted it, or did you convert it from a sub to a function?