Working with 2 workbooks, how to select the correct one to save to

Hello,

I am working on developing an Excel VBA that takes data from one workbook (wb2), processes it and saves the processed results in a second workbook (wb). wb2 has data separated by a text code (for example "One", "Two" etc), which I am using to separate different sections for processing & saving to wb. This works perfectly with just "One" and "Two", but when I attempt to add "Three" using the same code as "Two", it begins to save in the original wb2, rather than saving it to the new wb. It seems to me that at some point, Excel VBA is getting confused as to which workbook to save it to. I initially had this problem with "One" and "Two" as well, but using the "Set wb2 = ActiveWorkbook" command at regular intervals solved the issue. I am going to try adding that command to the function for "Three" as well, but this seems like a inefficient and ugly solution. I am sure there is a more elegant solution to tell excel which workbook to get data from and which workbook to save it to without it getting confused. Could anyone provide some information on how to achieve this? Thank you kindly, your time is much appreciated.

I have attached wb2 with the original data here. The code to save data in the new workbook is below. If you comment out the function "Find_Three", you can see how it is supposed to run:

Option Explicit

Public range1 As Integer
Public range2 As Integer
Public range3 As Integer
Public range4 As Integer
Public wb As Workbook, wb2 As Workbook
Public ws As Worksheet
Public vFile As Variant
Public FoundCell As Range

'Range ("A" & Rng.Row & ":O" & x)

Sub test()

Set ws = ActiveSheet
'Set source workbook
Set wb = ActiveWorkbook
'Open the target workbook
vFile = "C:\Users\Co-op\Desktop\testwb2.xlsm"
'if the user didn't select a file, exit sub
Workbooks.Open vFile
'Set targetworkbook
Set wb2 = ActiveWorkbook
Find_One


'For instance, copy data from a range in the first workbook to another range in the other workbook
' wb.Worksheets("Sheet1").Range("A1:A" & FoundCell.Row).Value = wb2.Worksheets("Sheet1").Range("A1:A" & FoundCell.Row).Value
End Sub

'Find functions - Run to determine what row to separate testpoints with.
'If find_one is not zero, find_two. If find_two is not zero, find_three... and so on until you get a zero.
'This tells how many test points you have and which row to find them in
' result = WorksheetFunction.Average(Range("A10:N10"))
'
Sub Find_One()
Set wb2 = ActiveWorkbook
Dim result As Double
Dim speed As Double
Dim position As Double
Dim Counter As Integer


Const WHAT_TO_FIND As String = "One"

Set FoundCell = wb2.Worksheets("Sheet1").Range("A:A").Find(What:=WHAT_TO_FIND)
If Not FoundCell Is Nothing Then
range1 = FoundCell.Row - 1
wb.Worksheets("Sheet1").Range("A1").Value = WorksheetFunction.Average(Range("A1:A" & range1))
wb.Worksheets("Sheet1").Range("B1").Value = WorksheetFunction.Average(Range("B1:B" & range1))

Counter = 1
While Counter < range1
wb2.Worksheets("Sheet1").Range("E" & Counter).Value = (wb2.Worksheets("Sheet1").Range("C" & (Counter + 1)).Value) - (wb2.Worksheets("Sheet1").Range("C" & Counter).Value)
wb2.Worksheets("Sheet1").Range("F" & Counter).Value = (wb2.Worksheets("Sheet1").Range("D" & (Counter + 1)).Value) - (wb2.Worksheets("Sheet1").Range("D" & Counter).Value)
Counter = Counter + 1
Wend

Set wb2 = ActiveWorkbook
wb.Worksheets("Sheet1").Range("C1").Value = WorksheetFunction.Average(Range("E1:E" & range1 - 1))
Set wb2 = ActiveWorkbook
wb.Worksheets("Sheet1").Range("D1").Value = WorksheetFunction.Average(Range("F1:F" & range1 - 1))
Set wb2 = ActiveWorkbook
wb.Worksheets("Sheet1").Range("E1").Value = (wb.Worksheets("Sheet1").Range("C1").Value) / (wb.Worksheets("Sheet1").Range("D1").Value)
Set wb2 = ActiveWorkbook
Find_Two
Else
range1 = 0
End If
End Sub


Sub Find_Two()
Set wb2 = ActiveWorkbook
Dim result As Double
Dim Counter As Integer

Const WHAT_TO_FIND As String = "Two"

Set FoundCell = wb2.Worksheets("Sheet1").Range("A:A").Find(What:=WHAT_TO_FIND)
If Not FoundCell Is Nothing Then
range2 = FoundCell.Row - 1
range1 = range1 + 2
wb.Worksheets("Sheet1").Range("A2").Value = WorksheetFunction.Average(Range("A" & range1 & ":A" & range2))
wb.Worksheets("Sheet1").Range("B2").Value = WorksheetFunction.Average(Range("B" & range1 & ":B" & range2))

Counter = range1
While Counter < range2
wb2.Worksheets("Sheet1").Range("E" & Counter).Value = (wb2.Worksheets("Sheet1").Range("C" & (Counter + 1)).Value) - (wb2.Worksheets("Sheet1").Range("C" & Counter).Value)
wb2.Worksheets("Sheet1").Range("F" & Counter).Value = (wb2.Worksheets("Sheet1").Range("D" & (Counter + 1)).Value) - (wb2.Worksheets("Sheet1").Range("D" & Counter).Value)
Counter = Counter + 1
Wend

Set wb2 = ActiveWorkbook
wb.Worksheets("Sheet1").Range("C2").Value = WorksheetFunction.Average(Range("E1:E" & range2 - 1))
Set wb2 = ActiveWorkbook
wb.Worksheets("Sheet1").Range("D2").Value = WorksheetFunction.Average(Range("F1:F" & range2 - 1))
Set wb2 = ActiveWorkbook
wb.Worksheets("Sheet1").Range("E2").Value = (wb.Worksheets("Sheet1").Range("C2").Value) / (wb.Worksheets("Sheet1").Range("D2").Value)

Find_Three
Else
range2 = 0
End If
End Sub




Sub Find_Three()
Set wb2 = ActiveWorkbook
Dim result As Double
Dim Counter As Integer

Const WHAT_TO_FIND As String = "Three"

Set FoundCell = wb2.Worksheets("Sheet1").Range("A:A").Find(What:=WHAT_TO_FIND)
If Not FoundCell Is Nothing Then
range3 = FoundCell.Row - 1
range2 = range2 + 2
wb.Worksheets("Sheet1").Range("A3").Value = WorksheetFunction.Average(Range("A" & range2 & ":A" & range3))
wb.Worksheets("Sheet1").Range("B3").Value = WorksheetFunction.Average(Range("B" & range2 & ":B" & range3))

Counter = range2
While Counter < range3
wb2.Worksheets("Sheet1").Range("E" & Counter).Value = (wb2.Worksheets("Sheet1").Range("C" & (Counter + 1)).Value) - (wb2.Worksheets("Sheet1").Range("C" & Counter).Value)
wb2.Worksheets("Sheet1").Range("F" & Counter).Value = (wb2.Worksheets("Sheet1").Range("D" & (Counter + 1)).Value) - (wb2.Worksheets("Sheet1").Range("D" & Counter).Value)
Counter = Counter + 1
Wend

Set wb2 = ActiveWorkbook
wb.Worksheets("Sheet1").Range("C3").Value = WorksheetFunction.Average(Range("E1:E" & range3 - 1))
Set wb2 = ActiveWorkbook
wb.Worksheets("Sheet1").Range("D3").Value = WorksheetFunction.Average(Range("F1:F" & range3 - 1))
Set wb2 = ActiveWorkbook
wb.Worksheets("Sheet1").Range("E3").Value = (wb.Worksheets("Sheet1").Range("C3").Value) / (wb.Worksheets("Sheet1").Range("D3").Value)

'Find_Four
Else
range3 = 0
End If
End Sub





Regards,
X

Nevermind. I found the issue in my code: I forgot to call "Set wb2 = ActiveWorkbook" before calling "Find_Three". This solves the issue.

However, I still feel like this is an inefficient and ugly way to solve the problem, and I am likely missing something that makes it easier to switch between the workbooks. Or is the way I am doing it the best way?

Thanks again,
X

Originally Posted by xlhelpplease

Nevermind. I found the issue in my code: I forgot to call "Set wb2 = ActiveWorkbook" before calling "Find_Three". This solves the issue.

However, I still feel like this is an inefficient and ugly way to solve the problem, and I am likely missing something that makes it easier to switch between the workbooks. Or is the way I am doing it the best way?

Thanks again,
X

Glad you found your error. I think an easier way to deal with the ugliness is to set your workbooks as variables and activate as you need to.

Please Login or Register  to view this content.

Whenever you want to switch books, just use the wb1.activate or wb2.activate so you dont have to set the wb each time. I currently use this syntax on my spreadsheets to switch back and forth. I hope this helps.

Thank you for the reply! That looks like a better way, I'll give it a shot.