Can't paste data into second workbook

TotallyConfused

Board Regular
Joined
May 4, 2017
Messages
246
Office Version
  1. 365
Platform
  1. Windows
Hello

I need to copy a range of data from ThisWorkbook (Wb1) and paste it into a second workbook called 'MasterDataFile.xlsm' (Wb2) that this program created in a previous subroutine. Both workbooks are in the same folder. The second workbook has a sheet named Wb2ws2 that will receive the data from sheet ws2 in ThisWorkbook. All the variables are DIM in such a way that I can access their values from any macro. The code within the last 'With...End With' block, is where I try to copy and paste the data. When I run this subroutine, it opens the second workbook (Wb2) with sheet Wb2ws2 displayed, with NO error messages. However, nothing is pasted.

Question #1: Since I can run the macro and receive NO error messages, why is there nothing showing in MasterDataFile?

Question #2: In the future, it will be necessary to copy/paste the data BACK into ThisWorkbook from the second workbook. Will the code to do that be similar to what I have to use to accomplish my task now, only in 'reverse'?

Question #3: While trying to figure out why nothing is appearing in MasterDataFile, I inserted the 'MsgBox Wb2ws2.Range("A1")' command inside the last With...End With statement. Am I mistaken in thinking this should show what is in MasterDataFile cell A1? The result of that MsgBox command is exactly what SHOULD be showing in MasterDataFile cell A1.

If you can show me where I'm going wrong with my copy/paste code, I sure would appreciate it? THANK YOU in advance for any help or advice you may be able to offer. At the moment, my name is 100% true.

TotallyConfused

VBA Code:
Dim Wb1 As Workbook                        ' This will be ThisWorkbook
Dim Wb2 As Workbook                        ' Second workbook called 'MasterDataFile.xlsm'
Dim ws2 As Worksheet                         ' Sheet2 = CodeName in Wb1
Dim Wb2ws2 As Worksheet                  ' Sheet2 = CodeName in Wb2 sheet 2

Dim fPath As String                               ' Path where data and programs are at (both this program & MasterDataFile.xlsm
Dim fName As String                            ' File name of Master data file = MasterDataFile.xlsm

Sub UploadDataToMasterDataFile()     ' Upload all the data from (ws2) & paste into MasterDataFile.xlsm (Wb2ws2).
  Application.ScreenUpdating = True
  Application.DisplayAlerts = True
       
  fName = "MasterDataFile.xlsm"         ' Assign name of MasterDataFile, which stores ALL of the data, to a variable
  fPath = Application.ActiveWorkbook.Path                          ' Path for the MasterDatafile.xlsm based on Activeworkbook path
  Workbooks.Open fPath & "\" & fName, UpdateLinks:=0   ' Open MasterDataFile.xlsm
            
      Set Wb1 = ThisWorkbook
      Set Wb2 = Workbooks("MasterDataFile.xlsm")
   With Wb1     ' ThisWorkbook
        Set Wb1ws2 = Sheet2
        Set ws2 = Sheet2
   End With
  
   With Wb2     ' MasterDataFile.xlsm
        Set Wb2ws2 = Sheet2
   End With
 
With Wb2ws2    ' MasterDataFile sheet2
     Wb1ws2.UsedRange.Copy .Range("A1")   ' Should copy from Wb1ws2 over to Wb2ws2
     MsgBox Wb2ws2.Range("A1")
End With
    Workbooks(2).Close  ' Close MasterDataFile 
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True
End Sub
 

Some videos you may like

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,026
Office Version
  1. 2016
Platform
  1. Windows
You are defining the variable outside the routine

Dim Wb2 As Workbook ' Second workbook called 'MasterDataFile.xlsm'
Dim ws2 As Worksheet ' Sheet2 = CodeName in Wb1
Dim Wb2ws2 As Worksheet ' Sheet2 = CodeName in Wb2 sheet 2
Dim fPath As String ' Path where data and programs are at (both this program & MasterDataFile.xlsm
Dim fName As String ' File name of Master data file = MasterDataFile.xlsm

Therefore the Wb1, Wb2, Wb2ws2, etc are actually not defined. They become variants I supposed.

Therefore it is good practice to put Option Explicit in Public declaration (outside the subroutine frame). Then it will tell you what was wrong to you code and helps to debug.

Outside subroutine, the declaration should be Public Wb2 As Workbook, not Dim Wb2 As Workbook. However, the variable will become common to all subroutines in the whole project.

The way declaring the sheet also not correct.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,026
Office Version
  1. 2016
Platform
  1. Windows
This is how I would write the code

VBA Code:
Option Explicit

Sub UploadDataToMasterDataFile2()

Dim FName As Variant
Dim Wb1 As Workbook                        ' This will be ThisWorkbook
Dim Wb2 As Workbook                        ' Second workbook called 'MasterDataFile.xlsm'
Dim Wb1ws2 As Worksheet                  ' Sheet2 = CodeName in Wb1
Dim Wb2ws2 As Worksheet                  ' Sheet2 = CodeName in Wb2 sheet 2

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Set Wb1 = ActiveWorkbook       ' This is the "MasterDataFile.xlsm"
Set Wb1ws2 = Wb1.Sheets("Sheet2")

' Select source file to copy data from. This will provide complete data path
FName = Application.GetOpenFilename(FileFilter:="Excel Files (*.xlsx), *.xlsx", Title:="Select a File")
If FName = False Then                          'CANCEL is clicked
    Exit Sub
End If

' Declare Wb1 while opening the workbook
Set Wb2 = Workbooks.Open(Filename:=FName, UpdateLinks:=False, ReadOnly:=True, IgnoreReadOnlyRecommended:=True)
Set Wb2ws2 = Wb2.Sheets("Sheet2")

' Copy data from Wb2ws2 to Wb1ws2
Wb1ws2.Range("A1") = Wb2ws2.Range("A1")

' Closing Wb2 without saving and without prompt
Wb2.Close False

Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub
 

TotallyConfused

Board Regular
Joined
May 4, 2017
Messages
246
Office Version
  1. 365
Platform
  1. Windows
Hello Zot

I want to THANK YOU for working with the code I posted and your explanations. This is my first attempt at using Public variables. I had read someplace about using 'Public' rather than 'Dim' but had forgotten about doing so. I pasted your code into my workbook and also changed all my DIM statements to Public. So there wouldn't be any chance of a conflict with your code, I then commented out all of my Public statements.

1 ) When I tried running your code, I received an error message saying: "Subscript out of range" with your code: Set Wb1ws2 = Wb1.Sheets("Sheet2") highlighted.

2 ) Further down you have the code of Wb1ws2.Range("A1") = Wb2ws2.Range("A1") This would copy the contents of Wb2ws2 into Wb1ws2 which may be useful later, however, now I need to copy from Workbook #1 over to Workbook #2. Number 2 is blank at the moment. I reversed the order. Also, when I hover over that line of code, the VBA editor shows the error message of: Object variable or with block variable not set. I'm not sure, but this may be caused because of the problem I mentioned in #1.

3 ) Won't your code I mentioned in #2 ONLY copy/paste the cell 'A1'? If you notice from my code, I need to copy 'UsedRange' from Book #1 starting at 'A1' and paste that into Book #2 starting at 'A1'. Not just a single cell.

Thanks again. Any other suggestions you may have, I'd sure appreciate.

TotallyConfused
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,026
Office Version
  1. 2016
Platform
  1. Windows
My code more like example. It is not exactly what your code would do. My workbooks were different I guess. I ran my code with no error.

You defined
Set Wb2 = Workbooks("MasterDataFile.xlsm")

in my code the Wb1 = MasterDataFile, which is the activeworkbook. The macro in MasterDataFile. Then I use open file dialog to look for the other workbook and copy data from wb2 into wb1 (i.e from ws2 range A1 to ws2 range A1 same location). Your code copy from wb1 to wb2, which should be the same but you use UsedRange. Not sure if that would work though.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,847
Messages
5,627,239
Members
416,232
Latest member
Ash1432

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