cforrester1981
New Member
- Joined
- Aug 22, 2020
- Messages
- 11
- Office Version
- 2016
- Platform
- Windows
I am at wits end. I have no idea what is going on here.
I have posted previously that I am a VBA noob and using macros to learn VBA but the bit of programming experience that I have clearly isn't helping me find the problem with my code.
Some context: My client uses our software to pull a variety of different reports. However, the limitation of the software means that the one piece of data they require is tied up in a text string within a cell. I want to create a macro-enabled Excel file with different buttons that will extract the data from each of the different report formats. I have managed (with the help of this forum) to work out how extract my data. However, before I extract the data, I need to create a column to put the data in.
Below is my code. The problem I am having is that after the unmerging of the cells (the report is generated with merged cells), I need to copy the contents of cell A1 in the target file to the cell B1 in the target file. The code is correctly selecting and copying cell A1. However, when it has to select cell B2, it somehow switches back to the macro file and then the paste instruction pastes the contents to that file instead. I've marked where the problem lies. I've also included some screenshots so that you can see the outcome
Before starting the macro
The unmerge, select and copy work correctly
It's pasted the copied text in the wrong file.
I have even tried to use wb = ActiveWorkBook before the select and paste. That didn't work either.
If I get this last bit working, the rest of my code works and I can carry on.
Thanks again for all your help. This noob feels like a complete dunce. ?
I have posted previously that I am a VBA noob and using macros to learn VBA but the bit of programming experience that I have clearly isn't helping me find the problem with my code.
Some context: My client uses our software to pull a variety of different reports. However, the limitation of the software means that the one piece of data they require is tied up in a text string within a cell. I want to create a macro-enabled Excel file with different buttons that will extract the data from each of the different report formats. I have managed (with the help of this forum) to work out how extract my data. However, before I extract the data, I need to create a column to put the data in.
Below is my code. The problem I am having is that after the unmerging of the cells (the report is generated with merged cells), I need to copy the contents of cell A1 in the target file to the cell B1 in the target file. The code is correctly selecting and copying cell A1. However, when it has to select cell B2, it somehow switches back to the macro file and then the paste instruction pastes the contents to that file instead. I've marked where the problem lies. I've also included some screenshots so that you can see the outcome
VBA Code:
Sub OpenFile()
Dim StrFile As String
Dim wb As Workbook
StrFile = Application.GetOpenFilename(fileFilter:="Excel files (*.xls*), *.xls*", Title:="Choose an Excel file to open")
Set wb = Workbooks.Open(StrFile)
DoWork wb
wb.Close SaveChanges:=True
End Sub
Sub DoWork(wb As Workbook)
With wb
' Unmerge all cells
Cells.Select
Selection.UnMerge
' Copy Referring Doctor label to B1
Range("A1").Select
Selection.Copy
Range("B1").Select '<-- PROBLEM LIES HERE!!!!
ActiveSheet.Paste ' <-- AND HERE!!!!
' Delete column A
Columns("A:A").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
' Copy column B to column I (so that the cell formatting is able to be duplicated)
Columns("B:B").Select
Selection.Copy
Columns("I:I").Select
Selection.Insert Shift:=xlToRight
Application.CutCopyMode = False
' Clear contents of column I and add new heading
Selection.ClearContents
Range("I1").Select
ActiveCell.FormulaR1C1 = "Practice Number"
' Move column I and insert it between column A & B
Selection.Cut
Columns("B:B").Select
Selection.Insert Shift:=xlToRight
End Sub
I have even tried to use wb = ActiveWorkBook before the select and paste. That didn't work either.
If I get this last bit working, the rest of my code works and I can carry on.
Thanks again for all your help. This noob feels like a complete dunce. ?