Is it possible to create a table copy function?

Glitch5618

Board Regular
Joined
Nov 6, 2015
Messages
105
I'm not very experienced with making custom functions. I have the following code to copy tables without the use of the clipboard. I have a large project with many copy & paste events that I would like to replace with this code to speed up my program. It would be must faster if I had a customer function I could call instead of repeating the same code throughout my project. The only thing that would change would be the worksheet name, the table name, and the destination cell/worksheet. My question would be, how do I turn this code into a custom function?

Rich (BB code):
Dim SourceRng as range
Dim DestinationRng as range

'Table you want to copy
Set SourceRng = cWS.ListObjects("CallVolume").DataBodyRange
 
'Only need to specify the top/left destination cell, then resize range to match SourceRng
Set DestinationRng = bWS.Range("A" & (LR + 1)).Resize(SourceRng.Rows.Count, SourceRng.Columns.Count)
 
'Then copy the values from source to destination
DestinationRng.Value = SourceRng.Value
 

Some videos you may like

Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You could create a procedure with arguments for cWS, bWs and the name of the ListObject. Example:

Code:
Sub CopyTable(cWS As Worksheet, TableName As String, bWS As Worksheet)
    Dim SourceRng As Range
    Dim DestinationRng As Range

'   Table you want to copy
    Set SourceRng = cWS.ListObjects(TableName).DataBodyRange
 
'   Only need to specify the top/left destination cell, then resize range to match SourceRng
    Set DestinationRng = bWS.Range("A" & (LR + 1)).Resize(SourceRng.Rows.Count, SourceRng.Columns.Count)
     
'   Then copy the values from source to destination
    DestinationRng.Value = SourceRng.Value
End Sub

The variable LR is neither declared nor assigned a value in the code you posted but I assume you know how to correct that.
 
Last edited:

Glitch5618

Board Regular
Joined
Nov 6, 2015
Messages
105
Oh sorry, I forgot to include that, its simply the last row of where the data is being placed. This wouldn't be needed in every section of my code.

Now that you mention it I forgot to state that the range itself would be dynamic, would it be possible to make that an argument? Also how exactly would I call this? Please forgive my ignorance, I'm starting to think this may be more trouble than its worth really. :confused:
 

Andrew Poulsom

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
You would call it like this:

Call CopyTable(Worksheets("Sheet1"), "CallVolume", Worksheets("Sheet2"))
 

Watch MrExcel Video

Forum statistics

Threads
1,122,464
Messages
5,596,288
Members
414,052
Latest member
Dual Showman

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