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
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.

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"))
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,164,427
Messages
5,837,173
Members
430,481
Latest member
nizambakhshi

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