Closing workbook after pasting

Chewyhairball

Board Regular
Joined
Nov 30, 2017
Messages
202
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi

I am copying data from workbook 2(name changes) to workbook1(fixed name).
My code is stored in workbook1 and run from workbook 2 window. It has to be this way as workbook 2 is not mine and is locked.

Once the data has been copied from workbook2 it is pasted into workbook1.
VBA Code:
Application.ScreenUpdating = False
  Sheets("Sheet1").Select
    Range("A1").Select
    Range(Selection, Selection.End(xlToRight)).Select
    Range("A1:DZ321").Select
    Selection.Copy

     Windows("Workbook1"). _
        Activate
       
    Sheets("Sheet1").Select
  
    Range("A3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Application.CutCopyMode = False

What I would like to do is shutdown workbook2( or whatever it is called) without saving it.
I have tried doing it using
VBA Code:
ActiveWorkbook.Close savechanges:=False
before activating worksheet1 which works to an extent and does close workbook2.
However it comes up with a message about large amounts of data on the clipboard and doesnt paste the data into worksheet 1.

any help is appreciated

thank Rory
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,245
Office Version
  1. 365
Platform
  1. Windows
Since you are doing paste values there is no need to even use copy and paste.
Try this.

VBA Code:
Sub testcopy()

    Dim srcWB As Workbook
    Dim srcSht As Worksheet
    Dim srcRng As Range
    Dim destWB As Workbook
    Dim destSht As Worksheet
    
    Set srcWB = ActiveWorkbook
    ' If the name of the workbook doesn't change the following would be better
    ' Set srcWB = Workbooks("Workbook2.xlsx")
    Set srcSht = srcWB.Worksheets("Sheet1")
    
    'Set srcRng = srcSht.Range("A1:DZ321")
    'use dynamic range
    Set srcRng = srcSht.Range("A1").CurrentRegion

    Set destWB = ThisWorkbook
    Set destSht = destWB.Worksheets("Sheet1")
    
    Application.ScreenUpdating = False
    
    destSht.Range("A3").Resize(srcRng.Rows.Count, srcRng.Columns.Count).Value = srcRng.Value
  
    srcWB.Close savechanges:=False
    
End Sub
 
Solution

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,714
Office Version
  1. 365
Platform
  1. Windows
Try this code in workbook1 instead

VBA Code:
Sub Copy_Data()
  Dim wb2 As Workbook
  
  Set wb2 = ActiveWorkbook
  Application.ScreenUpdating = False
  wb2.Sheets("Sheet1").Range("A1:DZ321").Copy
  ThisWorkbook.Sheets("Sheet1").Range("A3").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
  Application.CutCopyMode = False
  Application.DisplayAlerts = False
  wb2.Close SaveChanges:=False
  Application.DisplayAlerts = True
  Application.ScreenUpdating = True
End Sub
 

Chewyhairball

Board Regular
Joined
Nov 30, 2017
Messages
202
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
cool! i will have a look at these when im back at my PC after lunch. Thanks
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,245
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I forgot to add the following to the end of mine.
Application.ScreenUpdating = True
 

Chewyhairball

Board Regular
Joined
Nov 30, 2017
Messages
202
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Thanks Gents

Pleased to say both of your codes worked for my needs. I have gone with Alex's as it doesnt use copy and paste....though I cant figure out how this works :) :)

thanks again.

Rory
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,714
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

it doesnt use copy and paste..
If you don't want copy/paste, another version of my code which seems to do the same job is below.
Also, given that you are running this from workbook 2, I don't think that you need to worry about screen updating.

VBA Code:
Sub Copy_Data_v2()
  With ActiveWorkbook.Sheets("Sheet1").Range("A1").CurrentRegion
    ThisWorkbook.Sheets("Sheet1").Range(.Address).Offset(2).Value = .Value
    .Parent.Parent.Close Savechanges:=False
  End With
End Sub
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,245
Office Version
  1. 365
Platform
  1. Windows
though I cant figure out how this works
Let me see if I can explain it.
I will use​
srcRng for Source Range (copy from)​
destRng for Destination Range (copy to)​

Using copy paste it essentially looks like this:-
srcRng .Copy​
destRng .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=False​
Since you are only pasting values you can just assign (using equals) one range of values to the other.
In it's most basic form.​
destRng.Value = srcRng.Value​

The additional complexity arises from the fact that to transfer all the data that is in the source range, you need to match the size of the source range and the destination range.
One way of doing that is to resize the destination range using the number of rows and column that are in the source range.​
Which what adding this to the destination range does.​
.Resize(srcRng.Rows.Count, srcRng.Columns.Count).Value​
in a more complete statement piecing together the above​
destRng.Resize(srcRng.Rows.Count, srcRng.Columns.Count).Value = srcRng.Value​
Peter has done a similar thing using a different approach.​
He has used the "source range" as the range to use in the destination sheet,​
and then since the source range started in "A1" and you want the destination to start in "A3", he has offset it by 2 rows (= A1 then 2 rows down).​
The downside of this in your case is that your output / destination location will be dependent on your source location.​
eg if they insert 5 rows into the source (workbook2), it will also move down 5 rows in your destination workbook (workbook1)​
So you might be intending A3 but it will output A8. (source workbook was A1 + 5 rows added + the offset being applied of 2)​
destSht.Range(srcRng.Address).Offset(2).Value = srcRng.Value​
 

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
51,714
Office Version
  1. 365
Platform
  1. Windows
The downside of this in your case is that your output / destination location will be dependent on your source location.eg if they insert 5 rows into the source (workbook2), it will also move down 5 rows in your destination workbook (workbook1)So you might be intending A3 but it will output A8. (source workbook was A1 + 5 rows added + the offset being applied of 2)
Alex, I don't follow that. :unsure:
 

Alex Blakenburg

Well-known Member
Joined
Feb 23, 2021
Messages
2,245
Office Version
  1. 365
Platform
  1. Windows
Alex, I don't follow that. :unsure:
The OPs initial macro positions the output to be starting at "A3", so I assume that position has some significance in terms of the layout of the destination sheet.
The OP has also indicated that the source workbook (workbooks2) is outside of her control.

Sure if the owner of workbook2 inserted rows at the top or columns on the left that the macro would need to be changed anyway, since "A1" is hardcoded.
But should the programmer have to remember to also change the Offset value because it was tied to A1 in the workbook2. Wouldn't it be clearer to hard code A3 as the destination and remove the dependent relationship created through the use of offset, since in reality no relationship exists.
:unsure:
 

Forum statistics

Threads
1,147,518
Messages
5,741,634
Members
423,674
Latest member
Charles2dodo

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