Closing workbook after pasting

[Chewyhairball]

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.

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

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

 

[Alex Blakenburg]

Since you are doing paste values there is no need to even use copy and paste.
Try this.

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

 

[Peter_SSs]

Try this code in workbook1 instead

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]

cool! i will have a look at these when im back at my PC after lunch. Thanks

 

[Alex Blakenburg]

ADVERTISEMENT

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

 

[Chewyhairball]

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]

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.

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]

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]

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.

 

[Alex Blakenburg]

Alex, I don't follow that.

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.