VBA - copy rows and cols from one sheet to another

[manc]

Good day Demigods,

Hoping you can help with this.

'Workbook' is a macro-enabled template.

What i want to happen is have a button on 'SheetA!' within 'Workbook', that when clicked on, it opens a new instance of 'Workbook' and copies the contents of a specific range of cells from the original 'Workbook' to the new version. I can then manually save and close the original, working on the new version instead.

Look forward to hearing from you.

Best regards
manc

 

[Joe4]

Try something like this.

Change the ranges to suit your needs.

Sub MyMacro()

    Dim srcWB As Workbook
    Dim destWB As Workbook
    
'   Capture current file as source workbook
    Set srcWB = ActiveWorkbook

'   Add new workbook
    Workbooks.Add
    
'   Capture destination workbook
    Set destWB = ActiveWorkbook
    
'   Copy data range from source to destination
    srcWB.Sheets("SheetA").Range("A1:A10").Copy
    destWB.Activate
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
End Sub

 

[manc]

Dear Joe4,

Thank-you for your reply.

I have tried the code but i receive the error message "Subscript out of range".
Also, let's say my workbook is called Test.xltx.
I can't see in the code where it refers to opening another instance of Test.xltx. From what i can gather, the code above opens a blank workbook?

Any advice greatly appreciated.

Best regards
manc

 

[Joe4]

I can't see in the code where it refers to opening another instance of Test.xltx. From what i can gather, the code above opens a blank workbook?

Sorry, I missed the word "template".

Change this line:

    Workbooks.Add

to

    Workbooks.Open Filename:="your path\Test.xltx"

where "your path" is the full file path of where this template is located.

I have tried the code but i receive the error message "Subscript out of range".

Which line of code returns that error?
As I mentioned, you will need change the range (and possibly sheet name) to suit your conditions.
Since you did not provide any information on the range you want to copy, I just provided an example.

 

[manc]

Dear Joe4,

Thanks for the amendment.

Having made the changes, specified the range etc, running the code from the VBA debugging window returns "Run-time error '1004': Application-defined or object-defined error". running the code from a form control button returns "error 400".

Best regards
manc

 

[Joe4]

Where exactly have you placed this VBA code (what is the name of the Module it is in)?
You want to be sure to place it in a standard (general) module, and not in one of the sheet modules.

 

[manc]

Dear Joe4,

You were right. I had placed the code in the sheet module and not a standard module of its own.

I have done that now, but now get yet a further error "Run-time error '1004': We can't do that to a merged cell.'
Unfortunately, it is not possible to un-merge the cells as this will have repercussions on many different levels, all of which is too much work to put right.
Also, since putting in the actual path of the destination workbook, excel does not open a new instance of anything - it appears to be trying to paste the copied cells into the correct range but within the source workbook, although this is probably linked to the first issue.

Here is the code i am using:

Sub MyMacro()

    Dim srcWB As Workbook
    Dim destWB As Workbook
    
'   Capture current file as source workbook
    Set srcWB = ActiveWorkbook


'   Add new workbook
    Workbooks.Open Filename:="C:\Users\manc\manc work\manc work Team Site - Documents\Warehouse\Warehouse Invoice.xltm"
    
'   Capture destination workbook
    Set destWB = ActiveWorkbook
    
'   Copy data range from source to destination
    srcWB.Sheets("I1").Range("CP26:DP60").Copy
    destWB.Activate
    Range("F26:AL60").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
End Sub

Best regards
manc