TotallyConfused
Board Regular
- Joined
- May 4, 2017
- Messages
- 247
- Office Version
- 365
- Platform
- 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
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