!! MACRO NEEDED - TO PASTE two worksheets from closed workbook into other workbook

[drmmer07]

Hi Guru's,

This would be a BIG help and would be a solution to months of work i have been completing.

I really need a macro written that will copy two sheets (sheet 1= rep data & sheet 2= stats) from a closed workbook into another individual workbook through macro button.

Lets call the workbook that has the two sheets "the Summary" and the the workbook that needs the two sheets "schedule".

I have toyed around but cant get it right.

i have used this previously but it does not work as it OPENS the workbook then dosent even copy the worksheets over.

'
Dim sourceSheet As Worksheet
Dim destSheet As Worksheet
' ' Copy Data

Workbooks.Open Filename:="P:\Shared\CONSUMER DIVISION SALE SCHEDULE\2010 Trackers\Reporting Summary\Sales Schedule Summary - Rep II.xls"
Set sourceSheet = Worksheets("Total Sales by Rep")
sourceSheet.Activate
sourceSheet.Cells.Select
Selection.Copy

' ' Paste Data
Workbooks.Open Filename:="P:\Shared\CONSUMER DIVISION SALE SCHEDULE\2010 Trackers\Home & Garden Sales Schedule - April 2010.xls"
Set destSheet = Worksheets("Total Sales by Rep")
destSheet.Activate
destSheet.Cells.Select
destSheet.Paste

' ' Copy Data 2
Workbooks.Open Filename:="P:\Shared\CONSUMER DIVISION SALE SCHEDULE\2010 Trackers\Reporting Summary\Sales Schedule Summary - Rep II.xls"
Set sourceSheet = Worksheets("Sales Stats")
sourceSheet.Activate
sourceSheet.Cells.Select
Selection.Copy

' ' Paste Data 2

Workbooks.Open Filename:="P:\Shared\CONSUMER DIVISION SALE SCHEDULE\2010 Trackers\Home & Garden Sales Schedule - April 2010.xls"
Set destSheet = Worksheets("Sales Stats")
destSheet.Activate
destSheet.Cells.Select
destSheet.Paste
ActiveWorkbook.Save
ActiveWorkbook.Close

ANY help would be appreciated - it seems a simple macro but i just cant work it out atm

Cheers all

 

[Jon von der Heyden]

Hello

This should get you started. Notice how I use two variables to refer to the Source and Destination workbooks. This way you can refer to it's sheets and copy data between without attempting to open it multiple times. Also notice how we don't actually need to select or activate workbooks, sheets and ranges but can actuallly refer directly.

Example:

    Dim wkbSource As Workbook
    Dim wkbDestination As Workbook
    Set wkbSource = Workbooks.Open(Filename:="P:\Shared\CONSUMER DIVISION SALE SCHEDULE\2010 Trackers\Reporting Summary\Sales Schedule Summary - Rep II.xls")
    Set wkbDestination = Workbooks.Open(Filename:="P:\Shared\CONSUMER DIVISION SALE SCHEDULE\2010 Trackers\Home & Garden Sales Schedule - April 2010.xls")
    
    wkbSource.Sheets("Total Sales by Rep").Cells.Copy
    With wkbDestination.Sheets("Total Sales by Rep")
        .Cells.Clear
        .Range("A1").PasteSpecial 'xlValues if you don't want to carry formula over
    End With

 

[drmmer07]

thanks mate really appreciate it...but im still having an issue with the macro

can you provide a little more insight into this?

Cheers

 

[Legacy 146544]

Have you tried recording a macro where you perform the procedure you desire. And then copy (and adjsut or simplify) the code to wherever you need it.
?

 

[Jon von der Heyden]

thanks mate really appreciate it...but im still having an issue with the macro

can you provide a little more insight into this?

Cheers

Hi,

Can you be a little more specific please? If you are getting errors please say what the error message is and what line it breaks on.