VBA copy/paste range of cells not working

beefbroth

New Member
Joined
Aug 4, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
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:

Excel Formula:
=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:

VBA Code:
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:

VBA Code:
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:

VBA Code:
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:

VBA Code:
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?
 

Excel Facts

How to fill five years of quarters?
Type 1Q-2023 in a cell. Grab the fill handle and drag down or right. After 4Q-2023, Excel will jump to 1Q-2024. Dash can be any character.

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,079
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Is your destination worksheet protected? What about merged cells?
 

Joe4

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,499
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 4, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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

MrExcel MVP, Junior Admin
Joined
Aug 1, 2002
Messages
59,499
Office Version
  1. 365
Platform
  1. Windows
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

New Member
Joined
Aug 4, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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:

Excel Formula:
=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.
 

Attachments

  • sheet1.png
    sheet1.png
    17.3 KB · Views: 3
  • sheet2.png
    sheet2.png
    15.1 KB · Views: 3

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,079
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.

Excel Formula:
=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

New Member
Joined
Aug 4, 2021
Messages
10
Office Version
  1. 2016
Platform
  1. Windows
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.

Excel Formula:
=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

Board Regular
Joined
Dec 29, 2012
Messages
86
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
 

Forum statistics

Threads
1,144,337
Messages
5,723,796
Members
422,516
Latest member
mahab

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top