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

ExcelVBNoob

New Member
Joined
Jul 11, 2020
Messages
4
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?
 

Some videos you may like

Excel Facts

How to show all formulas in Excel?
Press Ctrl+` to show all formulas. Press it again to toggle back to numbers. The grave accent is often under the tilde on US keyboards.

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,414
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
In what workbook is the code you posted?
 

JoeMo

MrExcel MVP
Joined
May 26, 2009
Messages
17,414
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Hi, the code is in the first workbook that i open
This works for me:
VBA Code:
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

New Member
Joined
Jul 11, 2020
Messages
4

ADVERTISEMENT

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

MrExcel MVP
Joined
May 26, 2009
Messages
17,414
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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

New Member
Joined
Jul 11, 2020
Messages
4
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

MrExcel MVP
Joined
May 26, 2009
Messages
17,414
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,921
Messages
5,627,635
Members
416,257
Latest member
salomon

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