Create macro to copy-n-paste all data from an existing worksheet to another existing works

I am very new to Visual Basic and writing macros. I tried to record a macro, where I clicked on the worksheet, "Q1 - Pivot Table", that has the originating data that I want to copy, which is a pivot table. I then did a copy of "all cells", then I clicked on the other destination worksheet, "Q1 Pivot Report", and paste all the data, and then stop the recording of the macro. The Q1 Pivot Report worksheet does have all the data and formatting correctly. I then go into the macro I just recorded and copy-n-paste the programming into a COMMAND BUTTON that I have created called "Q1", and I save it. When I click the Q1 command button to run this macro, I get an error: "Run-time error '1004': Select method of Range class failed". When I click to "debug", the programming for that command button comes up and has the line, "Range ("A1").Select" is highlighted in yellow. Below is a copy of the programming that I recorded and I am trying to use for this command button. It seems that it would be a simple copy-n-paste, taking all the data on one worksheet and copying it into another worksheet. I just cannot figure why it is not working. I know it's probably something very simple.

[B]Private Sub CommandButton1_Click()
'
' Macro1 Macro
' Refresh and copy Q1 Pivot Table to Q1 PIVOT REPORT
'
'Select the filtered data and copy it

ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Q1 - PIVOT Table").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy

Sheets("Q1 PIVOT REPORT").Activate
Range("A1").Select
Selection.PasteSpecial
ActiveSheet.Range("A1").Select
'

End Sub


If someone can please help me out with this simple macro, I would greatly appreciate it. Regards.

Are you trying to select all of the cells in "Q1 - Pivot Table" or only cells with data? Looking at the code you are attempting to copy all cells with data. I dont know how your data is setup in the spreadsheet but you should be able to use this.

Please Login or Register  to view this content.

Don't for get to use code Wrap "#" located at the top of the thread window, when posting code.

thank you for helping me. I just copy and pasted this code in the module for the control button I want to use this macro for. I clicked the Q1 control button and still received a Run-Time error 1001. I clicked the "debug" button and the debugging landed on line: "Range("A1",Cells(RCount,CCount)).Copy". I am not sure if the data we are trying to copy is from a pivot table or not. I don't think that should matter, but just throwing it out there.

Private Sub CommandButton1_Click()
'
' Macro1 Macro
' Refresh and copy Q1 Pivot Table to Q1 PIVOT REPORT
'
'Select the filtered data and copy it

Dim RCount 'Number of rows with data
Dim CCount 'Number of columns with data

ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Q1 - PIVOT Table").Select
RCount = WorksheetFunction.CountA(Range("A:A")) 'Counts nonblank cells
CCount = WorksheetFunction.CountA(Range("1:1")) 'Counts nonblank cells
Range("A1", Cells(RCount, CCount)).Copy


Sheets("Q1 PIVOT REPORT").Activate
ActiveSheet.Range("A1").PasteSpecial


End Sub

Try this.

Please Login or Register  to view this content.

I found this code on the microsoft forum. There was someone asking the same question. Here is the link.
http://answers.microsoft.com/en-us/o...5-f4d8e74828a6