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?

 

[beefbroth]

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

Exactly as posted, ran it as a Sub.

 

[rlv01]

Is your destination worksheet protected? What about merged cells?

 

[beefbroth]

Is your destination worksheet protected? What about merged cells?

Nope. No protected sheets, no merged cells.

 

[Joe4]

How about posting a sample of your data, so we can try to recreate your layout here on our sides, and try it out on your data structure?

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.

 

[beefbroth]

How about posting a sample of your data, so we can try to recreate your layout here on our sides, and try it out on your data structure?

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.

I'm not going to be able to install XL2BB, my system is under a pretty restrictive policy about those kinds of things. If there's some other way to get the test data to you, I'm happy to try.

 

[Joe4]

You can copy/paste images here. But then you would just need to tell us what range it is in (as it probably won't show row/column headers).

Or you could upload the file to a file sharing site and provide a link to it here. Just be sure to remove any sensitive data first, if any exists.

 

[beefbroth]

Okay, hopefully these aren't too bare-bones. For the purposes of what I'm trying to do, it's only that column with the comparison code that matters on each sheet.

So what should happen is, in cell AA2:

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

The if(countif()) compares U2 with all the values in Sheet2 column V. If there's a match, it calls the sub and uses the address(match()) to determine the specific cell address on Sheet2 where the data starts, and that's what gets passed in (so in this case, it's passing AB3).

All of that happens without any problem. It's that next part, copying the values from Sheet2!AB3:AG3 to Sheet1!AA2:AF2, where it all falls apart.

 

[rlv01]

As Joe4 said back in post #4 functions are designed to return a value, and they cannot be used to move or copy groups of cell data*. You are trying to use a function for something better suited to a Sub. But subs cannot be used in a formula in this way.

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

(*There are a few dodgy/questionable ways to get around this, but I'll leave it to others get into that if they want. )

Subs have to be initiated in some way (use of a button or worksheet event for example), or called by other Subs. Trying to use a function here is kind of the classic XY Problem scenario. It might be time to reassess your chosen solution.

 

[beefbroth]

As Joe4 said back in post #4 functions are designed to return a value, and they cannot be used to move or copy groups of cell data*. You are trying to use a function for something better suited to a Sub. But subs cannot be used in a formula in this way.

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

(*There are a few dodgy/questionable ways to get around this, but I'll leave it to others get into that if they want. )

Subs have to be initiated in some way (use of a button or worksheet event for example), or called by other Subs. Trying to use a function here is kind of the classic XY Problem scenario. It might be time to reassess your chosen solution.

I think I'm at the point where I'd consider dodgy/questionable ways of doing it. After a weekend of trying, I really have no idea how to reconfigure what I have for a button. I feel like what I'm trying to do can't be as frustrating and complicated as this, it's just copying 6 cells of data to 6 other cells.

 

[B-Man]

Can you not just do a simple

Dim wbDest as workbook
Dim wbSource as workbook

Dim wsDest as worksheet
Dim wsSource as worksheet

Set wbSource = 'book with data'
Set wsSource = 'sheet with data'

Set wbDest = 'book you want data to go'
Set wsDest = 'sheet you want data to go'

wsDest.range("AA2:AF2") = wsSource.range("AB3:AG3").value