VBA : Copying data from one workbook to another, where the second workbook name is different other than the date

[ExcelVBNoob]

Hi Hope you guys can help,

1) I open a new workbook that i do not save.

2) I open a second workbook that contains the name "SALES" in the filename.

3) I want some VBA code to copy data from the "SALES" file into the file that i initially opened.

So far i have the below and it works up until i get to the point of copying the data. It fails to switch back to the original workbook and paste the data.


+++++++++++++++++++++++++++++++

Dim wbkMain

Set wbkMain = ThisWorkbook

Dim wb As Workbook

For Each wb In Application.Workbooks
If wb.Name Like "*SALES*" Then
wb.Activate

Exit For
End If
Next wb

ActiveWorkbook.ActiveSheet.Range("A1:R6788").Copy

ThisWorkbook.Activate

wbkMain.Activate
ActiveSheet.Paste

+++++++++++++++++++++++++++++++


What am i doing wrong?

 

[JoeMo]

In what workbook is the code you posted?

 

[ExcelVBNoob]

In what workbook is the code you posted?

Hi, the code is in the first workbook that i open

 

[JoeMo]

Hi, the code is in the first workbook that i open

This works for me:

Dim wbkMain As Workbook, wb As Workbook
Set wbkMain = ThisWorkbook
For Each wb In Application.Workbooks
    If wb.Name Like "*SALES*" Then
        wb.Activate
        Exit For
    End If
Next wb
ActiveSheet.Range("A1:R6788").Copy
wbkMain.Activate
ActiveSheet.Paste

 

[ExcelVBNoob]

Thanks Joe, that works to the same point. It copies the data from the SALES spreadsheet but does not switch back to the workbook where the macro was executed.

It fails to switch back and paste the copied cells.

Background : I'm running a Macro (within which the VBA code is) and this is available in any workbook i open.

 

[JoeMo]

Thanks Joe, that works to the same point. It copies the data from the SALES spreadsheet but does not switch back to the workbook where the macro was executed.

It fails to switch back and paste the copied cells.

Background : I'm running a Macro (within which the VBA code is) and this is available in any workbook i open.

Did you try running just the code I posted, or is what I posted embedded in another macro? If it's within another macro I can't help you further w/o benefit of seeing the full macro. What I posted, when executed as written, definitely activates the workbook where the code resides before ending.

 

[ExcelVBNoob]

I ran the code you wrote, its not embedded in any other code/macro. It succesfully copies the data from the sales workbook, then switches back to the workbook from where i ran the macro, but doesnt paste the copied data.

 

[JoeMo]

I ran the code you wrote, its not embedded in any other code/macro. It succesfully copies the data from the sales workbook, then switches back to the workbook from where i ran the macro, but doesnt paste the copied data.

Remember that the copied data is pasted to whatever cell is active on the active sheet when the paste occurs. Possibly the paste has taken place on an area of that sheet that's outside the area you are expecting it to be in because the active cell wasn't where you were expecting it to be.