PDA

View Full Version : Solved: How to have function run on both tabs



jo15765
01-29-2012, 12:12 PM
I am running the below code which will open a workbook, refresh all the queries (on both tabs), then append data to column T. The problem is the append operation is only appending to one tab. How can I have it append to both tabs?


Public Sub Testing()

Set wb = Workbooks.Open(Filename:="C:\Testing_Purposes\Testing_Code\Test_Spreadsheet.xls")
'Function that Refreshes All Queries on both tabs of the workbook
Call Refresh_All_Queries
'Function that will Append to beginning of Colum
Call Append_Prefix
'Function to delete Header Rows
Call Cleanup
'Function to Save Workbook
Call SaveFile

End Sub
Public Sub Append_Prefix()
Dim LR As Long, i As Long
LR = Range("T" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With Range("T" & i)
.Value = "Notes: " & .Value
End With
Next i
End Sub

omp001
01-29-2012, 12:49 PM
Hi. You could try this, for two tabs:
Public Sub Append_Prefix()
Dim LR As Long, i As Long, k As Long
For k = 1 To 2
With Sheets(k)
LR = .Range("T" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With .Range("T" & i)
.Value = "Notes: " & .Value
End With
Next i
End With
Next k
End Sub

mdmackillop
01-29-2012, 01:49 PM
You can pass the appropriate sheet names to the procedures.
BTW, You should use Option Explicit and Dim all your variables.

Option Explicit
Public Sub Testing()
Dim arr()
Dim wb As Workbook

arr = Array("Sheet1", "Sheet2")

Set wb = Workbooks.Open(Filename:="C:\Testing_Purposes\Testing_Code\Test_Spreadsheet.xls")
'Function that Refreshes All Queries on both tabs of the workbook
Call Refresh_All_Queries(arr)
'Function that will Append to beginning of Colum
Call Append_Prefix(arr)
'Function to delete Header Rows
Call Cleanup(arr)
'Function to Save Workbook
Call SaveFile(arr)

End Sub
Public Sub Append_Prefix(MyArray)
Dim LR As Long, i As Long
Dim a
For Each a In MyArray
With Sheets(a)
LR = .Range("T" & Rows.Count).End(xlUp).Row
For i = 1 To LR
With .Range("T" & i)
.Value = "Notes: " & .Value
End With
Next i
End With
Next
End Sub

jo15765
01-29-2012, 04:08 PM
oomp thank you that worked just as I need it to!!


You can pass the appropriate sheet names to the procedures.
BTW, You should use Option Explicit and Dim all your variables.


mdmackillop as always thank you for your continued support!! I did have my variables Dimmed I just accidentally left that part off when I was cutting/pasting my code. I however, have not been using Option Explicit, but after a quick google search I see that it is very beneficial to do so!


I also did not want to have to specify worksheet names as they differ depending on what workbook I have open, and would be quite lengthy code to take into account each worksheet that would be affected when using this function.

Again thanks to the both of you for your support and resolutions!!!