Help with VBA and a simple copy/paste

Guitarmageddon

Board Regular
Joined
Dec 22, 2014
Messages
159
Hello everyone,

I am trying to write a macro to basically take a range from one tab (lets say, its always named "SOURCE" tab) and past it into another tab (lets call it "DESTINATION"). The sheet names are always fixed.

I want to be able to simply copy down columns A:Z of SOURCE, and paste it into A1 of DESTINATION sheet. My table in SOURCE will always be columns A:Z, but could be lets say 2,000 lines for one user, but another user may need 20,000 lines for example. So I basically just want it to copy down to the end of the data dynamically and just paste it over.

I came up with this, but got a debug error.

VBA Code:
    Columns("A:Z").Select
    Selection.Copy
    Sheets("DESTINATION").Select
    ActiveSheet.Paste
End Sub

I think because writing it that way forces me to call it up when I am physically in that sheet? Any help would be appreciated Im sure this is super simple. Doing this via linked cells forced it into a "calculating" tailspin and was really time consuming. Thanks!
 

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
How about
VBA Code:
Sub Guitarmageddon()
   With Sheets("Destination")
      .UsedRange.ClearContents
      Sheets("Source").UsedRange.Copy .Range("A1")
   End With
End Sub
 
Upvote 0
You're welcome & thanks for the feedback.
 
Upvote 0
You're welcome & thanks for the feedback.

Ok one change to this that I discovered I need. On the SOURCE, I do not need all the data, but rather everything Column A:Q only.

Then, on the DESTINATION tab, I need to past it there. But just not starting in A1, but rather A9.
 
Upvote 0
In that case use
VBA Code:
      Sheets("Source").UsedRange.Columns("A:Q").Copy .Range("A9")
 
Upvote 0
Ok, so i used the terms SOURCE and DESTINATION to keep things simple. When I use the above but rename to my correct sheet name

SOURCE is "Search POG by LOB or Dpt Report"
DESTINATION is "ANALYSIS"

I get the error "invalid or unqualified reference"

Does there need to be a mention of the analysis tab (aka destination) in your code? I just changed the references in your code to be this, but get the error.

VBA Code:
Sub JOANNE_COPYPASTE()
'
' JOANNE_COPYPASTE Macro

 Sheets("Search POG by LOB or Dpt Report").UsedRange.Columns("A:Q").Copy .Range("A9")
End Sub
 
Upvote 0
You need to update the previous code, as I showed in post#6
 
Upvote 0
You need to update the previous code, as I showed in post#6

Hmm... did I miss something?
Here was the previous code from #6
VBA Code:
     Sheets("Source").UsedRange.Columns("A:Q").Copy .Range("A9")

And I just replaced with the correct source tab name so now its this, but I get the error
VBA Code:
Sheets("Search POG by LOB or Dpt Report").UsedRange.Columns("A:Q").Copy .Range("A9")
 
Upvote 0
You need to update the macro in post#2
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,215
Members
448,554
Latest member
Gleisner2

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
Back
Top