Loop through my worksheets doing the exact same process

J

janki6566

New Member
Joined
Sep 24, 2014
Messages
21
Office Version
  1. 365
Platform
  1. Windows
All,

I just learned how to create code today at my new job. Had no idea I would be learning code so forgive me if I make no sense. I created this code below to autofill data. After writing, I learned I could do a loop through my worksheets. Can someone help in where is should put my loop? Obvisouly, since I created this and then recopied it, I would delete all the repetitive code but in BOLD is what I think I should do..

Additionally, thank you for all your help in advance. I know this is not easy!


Option Explicit


Sub NonTradeReconUpdate()




'9/22/2014
'Macro to update the MK company non trade reconciliation templates




'Variable Declarations
Dim Year As String
Dim Month As String
Dim MonthYear As String
Dim FilePath As String
Dim CurrFile As String
Dim Answer As String
Dim WS As String


Dim ReconFile As String
ReconFile = ActiveWorkbook.Name
Dim c102 As String
c102 = "c102.txt"
Dim c105 As String
c105 = "c105.txt"
Dim c110 As String
c110 = "c110.txt"
Dim c115 As String
c115 = "c115.txt"
Dim c120 As String
c120 = "c120.txt"
Dim c135 As String
c135 = "c135.txt"
Dim c140 As String
c140 = "c140.txt"
Dim c145 As String
c145 = "c145.txt"
Dim c150 As String
c150 = "c150.txt"
Dim c155 As String
c155 = "c155.txt"
Dim c160 As String
c160 = "c160.txt"
Dim c170 As String
c170 = "c170.txt"
Dim c190 As String
c190 = "c190.txt"
Dim c195 As String
c195 = "c195.txt"

For each WS in worksheets
if ws.name<>"110-REC" then​

'Retrieve Data
Year = InputBox("Please input the year you are reconciling.", "Year", "eg: 2014")
Month = InputBox("Please input the month you are reconciling.", "Month", "eg: 12")


MonthYear = Month & "-" & Right(Year, 2)
FilePath = "\\File01\busacct\Chris Casey\Monthly Non.Trade\" & Year & "\" & MonthYear & "\"
'\\File01\busacct\Chris Casey\Monthly Non.Trade\2014\08-14


'c102
Answer = InputBox("Do you want to update the c102? Yes or No?", "Update", "Yes")
If Answer = "Yes" Then
Workbooks(ReconFile).Worksheets("c102").Activate
Range("A:Z").ClearContents
CurrFile = FilePath & "c102.txt"
If Dir(CurrFile) <> "" Then
Workbooks.Open Filename:=CurrFile
Range("A:A").Select
Range("A:A").Copy
Workbooks(ReconFile).Worksheets("c102").Activate
Range("A1").Select
Range("A1").PasteSpecial xlPasteValues
Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Range("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Range("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
Dim RowCount As Long
RowCount = Worksheets("c120").Cells(Rows.Count, 1).End(xlUp).Row
Range("M1:M1").FormulaR1C1 = "=IF(TRIM(RC[-1])=""c"",-RC[-2],RC[-2])"
Range("M1").Copy
Range("M1:M" & RowCount).PasteSpecial xlPasteFormulas
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(SEARCH(""LEDGER ACCOUNT"",R[2]C[-14])),R[-1]C,MID(TRIM(R[2]C[-14]),SEARCH("":"",TRIM(R[2]C[-14]))+2,6))"
Range("O2").Copy
Range("O2:O" & RowCount).PasteSpecial xlPasteFormulas
Else: GoTo c105
End If
Else: GoTo c105
End If

I would delete form here as this is all repetitive.



c105:
Answer = InputBox("Do you want to update the c105 detail? Yes or No?", "Update", "Yes")
If Answer = "Yes" Then
Workbooks(ReconFile).Worksheets("c105").Activate
Range("A:Z").ClearContents
CurrFile = FilePath & "c105.txt"
Workbooks.Open Filename:=CurrFile
Range("A:A").Select
Range("A:A").Copy
Workbooks(ReconFile).Worksheets("c105").Activate
Range("A1").Select
Range("A1").PasteSpecial xlPasteValues
Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Range("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
RowCount = Worksheets("c120").Cells(Rows.Count, 1).End(xlUp).Row
Range("M1:M1").FormulaR1C1 = "=IF(TRIM(RC[-1])=""c"",-RC[-2],RC[-2])"
Range("M1").Copy
Range("M1:M" & RowCount).PasteSpecial xlPasteFormulas
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(SEARCH(""LEDGER ACCOUNT"",R[2]C[-14])),R[-1]C,MID(TRIM(R[2]C[-14]),SEARCH("":"",TRIM(R[2]C[-14]))+2,6))"
Range("O2").Copy
Range("O2:O" & RowCount).PasteSpecial xlPasteFormulas
Else: GoTo c110
End If




c110:
Answer = InputBox("Do you want to update the c110 detail? Yes or No?", "Update", "Yes")
If Answer = "Yes" Then
Workbooks(ReconFile).Worksheets("c110").Activate
Range("A:Z").ClearContents
CurrFile = FilePath & "c110.txt"
Workbooks.Open Filename:=CurrFile
Range("A:A").Select
Range("A:A").Copy
Workbooks(ReconFile).Worksheets("c110").Activate
Range("A1").Select
Range("A1").PasteSpecial xlPasteValues
Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Range("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
RowCount = Worksheets("c120").Cells(Rows.Count, 1).End(xlUp).Row
Range("M1:M1").FormulaR1C1 = "=IF(TRIM(RC[-1])=""c"",-RC[-2],RC[-2])"
Range("M1").Copy
Range("M1:M" & RowCount).PasteSpecial xlPasteFormulas
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(SEARCH(""LEDGER ACCOUNT"",R[2]C[-14])),R[-1]C,MID(TRIM(R[2]C[-14]),SEARCH("":"",TRIM(R[2]C[-14]))+2,6))"
Range("O2").Copy
Range("O2:O" & RowCount).PasteSpecial xlPasteFormulas
Else: GoTo c115
End If




c115:
Answer = InputBox("Do you want to update the c115 detail? Yes or No?", "Update", "Yes")
If Answer = "Yes" Then
Workbooks(ReconFile).Worksheets("c115").Activate
Range("A:Z").ClearContents
CurrFile = FilePath & "c115.txt"
Workbooks.Open Filename:=CurrFile
Range("A:A").Select
Range("A:A").Copy
Workbooks(ReconFile).Worksheets("c115").Activate
Range("A1").Select
Range("A1").PasteSpecial xlPasteValues
Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Range("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
RowCount = Worksheets("c120").Cells(Rows.Count, 1).End(xlUp).Row
Range("M1:M1").FormulaR1C1 = "=IF(TRIM(RC[-1])=""c"",-RC[-2],RC[-2])"
Range("M1").Copy
Range("M1:M" & RowCount).PasteSpecial xlPasteFormulas
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(SEARCH(""LEDGER ACCOUNT"",R[2]C[-14])),R[-1]C,MID(TRIM(R[2]C[-14]),SEARCH("":"",TRIM(R[2]C[-14]))+2,6))"
Range("O2").Copy
Range("O2:O" & RowCount).PasteSpecial xlPasteFormulas
Else: GoTo c120
End If




c120:
Answer = InputBox("Do you want to update the c120 detail? Yes or No?", "Update", "Yes")
If Answer = "Yes" Then
Workbooks(ReconFile).Worksheets("c120").Activate
Range("A:Z").ClearContents
CurrFile = FilePath & "c120.txt"
Workbooks.Open Filename:=CurrFile
Range("A:A").Select
Range("A:A").Copy
Workbooks(ReconFile).Worksheets("c120").Activate
Range("A1").Select
Range("A1").PasteSpecial xlPasteValues
Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Range("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
RowCount = Worksheets("c120").Cells(Rows.Count, 1).End(xlUp).Row
Range("M1:M1").FormulaR1C1 = "=IF(TRIM(RC[-1])=""c"",-RC[-2],RC[-2])"
Range("M1").Copy
Range("M1:M" & RowCount).PasteSpecial xlPasteFormulas
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(SEARCH(""LEDGER ACCOUNT"",R[2]C[-14])),R[-1]C,MID(TRIM(R[2]C[-14]),SEARCH("":"",TRIM(R[2]C[-14]))+2,6))"
Range("O2").Copy
Range("O2:O" & RowCount).PasteSpecial xlPasteFormulas
Else: GoTo c135
End If




c135:
Answer = InputBox("Do you want to update the c135 detail? Yes or No?", "Update", "Yes")
If Answer = "Yes" Then
Workbooks(ReconFile).Worksheets("c135").Activate
Range("A:Z").ClearContents
CurrFile = FilePath & "c135.txt"
Workbooks.Open Filename:=CurrFile
Range("A:A").Select
Range("A:A").Copy
Workbooks(ReconFile).Worksheets("c135").Activate
Range("A1").Select
Range("A1").PasteSpecial xlPasteValues
Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Range("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
RowCount = Worksheets("c120").Cells(Rows.Count, 1).End(xlUp).Row
Range("M1:M1").FormulaR1C1 = "=IF(TRIM(RC[-1])=""c"",-RC[-2],RC[-2])"
Range("M1").Copy
Range("M1:M" & RowCount).PasteSpecial xlPasteFormulas
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(SEARCH(""LEDGER ACCOUNT"",R[2]C[-14])),R[-1]C,MID(TRIM(R[2]C[-14]),SEARCH("":"",TRIM(R[2]C[-14]))+2,6))"
Range("O2").Copy
Range("O2:O" & RowCount).PasteSpecial xlPasteFormulas
Else: GoTo c140
End If


c140:
Answer = InputBox("Do you want to update the c140 detail? Yes or No?", "Update", "Yes")
If Answer = "Yes" Then
Workbooks(ReconFile).Worksheets("c140").Activate
Range("A:Z").ClearContents
CurrFile = FilePath & "c140.txt"
Workbooks.Open Filename:=CurrFile
Range("A:A").Select
Range("A:A").Copy
Workbooks(ReconFile).Worksheets("c140").Activate
Range("A1").Select
Range("A1").PasteSpecial xlPasteValues
Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Range("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
RowCount = Worksheets("c120").Cells(Rows.Count, 1).End(xlUp).Row
Range("M1:M1").FormulaR1C1 = "=IF(TRIM(RC[-1])=""c"",-RC[-2],RC[-2])"
Range("M1").Copy
Range("M1:M" & RowCount).PasteSpecial xlPasteFormulas
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(SEARCH(""LEDGER ACCOUNT"",R[2]C[-14])),R[-1]C,MID(TRIM(R[2]C[-14]),SEARCH("":"",TRIM(R[2]C[-14]))+2,6))"
Range("O2").Copy
Range("O2:O" & RowCount).PasteSpecial xlPasteFormulas
Else: GoTo c145
End If






c145:
Answer = InputBox("Do you want to update the c145 detail? Yes or No?", "Update", "Yes")
If Answer = "Yes" Then
Workbooks(ReconFile).Worksheets("c145").Activate
Range("A:Z").ClearContents
CurrFile = FilePath & "c145.txt"
Workbooks.Open Filename:=CurrFile
Range("A:A").Select
Range("A:A").Copy
Workbooks(ReconFile).Worksheets("c145").Activate
Range("A1").Select
Range("A1").PasteSpecial xlPasteValues
Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Range("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
RowCount = Worksheets("c120").Cells(Rows.Count, 1).End(xlUp).Row
Range("M1:M1").FormulaR1C1 = "=IF(TRIM(RC[-1])=""c"",-RC[-2],RC[-2])"
Range("M1").Copy
Range("M1:M" & RowCount).PasteSpecial xlPasteFormulas
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(SEARCH(""LEDGER ACCOUNT"",R[2]C[-14])),R[-1]C,MID(TRIM(R[2]C[-14]),SEARCH("":"",TRIM(R[2]C[-14]))+2,6))"
Range("O2").Copy
Range("O2:O" & RowCount).PasteSpecial xlPasteFormulas
Else: GoTo c150
End If




c150:
Answer = InputBox("Do you want to update the c150 detail? Yes or No?", "Update", "Yes")
If Answer = "Yes" Then
Workbooks(ReconFile).Worksheets("c150").Activate
Range("A:Z").ClearContents
CurrFile = FilePath & "c150.txt"
Workbooks.Open Filename:=CurrFile
Range("A:A").Select
Range("A:A").Copy
Workbooks(ReconFile).Worksheets("c150").Activate
Range("A1").Select
Range("A1").PasteSpecial xlPasteValues
Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Range("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
RowCount = Worksheets("c120").Cells(Rows.Count, 1).End(xlUp).Row
Range("M1:M1").FormulaR1C1 = "=IF(TRIM(RC[-1])=""c"",-RC[-2],RC[-2])"
Range("M1").Copy
Range("M1:M" & RowCount).PasteSpecial xlPasteFormulas
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(SEARCH(""LEDGER ACCOUNT"",R[2]C[-14])),R[-1]C,MID(TRIM(R[2]C[-14]),SEARCH("":"",TRIM(R[2]C[-14]))+2,6))"
Range("O2").Copy
Range("O2:O" & RowCount).PasteSpecial xlPasteFormulas
Else: GoTo c155
End If




c155:
Answer = InputBox("Do you want to update the c155 detail? Yes or No?", "Update", "Yes")
If Answer = "Yes" Then
Workbooks(ReconFile).Worksheets("c155").Activate
Range("A:Z").ClearContents
CurrFile = FilePath & "c155.txt"
Workbooks.Open Filename:=CurrFile
Range("A:A").Select
Range("A:A").Copy
Workbooks(ReconFile).Worksheets("c155").Activate
Range("A1").Select
Range("A1").PasteSpecial xlPasteValues
Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Range("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
RowCount = Worksheets("c120").Cells(Rows.Count, 1).End(xlUp).Row
Range("M1:M1").FormulaR1C1 = "=IF(TRIM(RC[-1])=""c"",-RC[-2],RC[-2])"
Range("M1").Copy
Range("M1:M" & RowCount).PasteSpecial xlPasteFormulas
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(SEARCH(""LEDGER ACCOUNT"",R[2]C[-14])),R[-1]C,MID(TRIM(R[2]C[-14]),SEARCH("":"",TRIM(R[2]C[-14]))+2,6))"
Range("O2").Copy
Range("O2:O" & RowCount).PasteSpecial xlPasteFormulas
Else: GoTo c160
End If




c160:
Answer = InputBox("Do you want to update the c160 detail? Yes or No?", "Update", "Yes")
If Answer = "Yes" Then
Workbooks(ReconFile).Worksheets("c160").Activate
Range("A:Z").ClearContents
CurrFile = FilePath & "c160.txt"
Workbooks.Open Filename:=CurrFile
Range("A:A").Select
Range("A:A").Copy
Workbooks(ReconFile).Worksheets("c160").Activate
Range("A1").Select
Range("A1").PasteSpecial xlPasteValues
Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Range("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
RowCount = Worksheets("c120").Cells(Rows.Count, 1).End(xlUp).Row
Range("M1:M1").FormulaR1C1 = "=IF(TRIM(RC[-1])=""c"",-RC[-2],RC[-2])"
Range("M1").Copy
Range("M1:M" & RowCount).PasteSpecial xlPasteFormulas
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(SEARCH(""LEDGER ACCOUNT"",R[2]C[-14])),R[-1]C,MID(TRIM(R[2]C[-14]),SEARCH("":"",TRIM(R[2]C[-14]))+2,6))"
Range("O2").Copy
Range("O2:O" & RowCount).PasteSpecial xlPasteFormulas
Else: GoTo c170
End If


c170:
Answer = InputBox("Do you want to update the c170 detail? Yes or No?", "Update", "Yes")
If Answer = "Yes" Then
Workbooks(ReconFile).Worksheets("c170").Activate
Range("A:Z").ClearContents
CurrFile = FilePath & "c170.txt"
Workbooks.Open Filename:=CurrFile
Range("A:A").Select
Range("A:A").Copy
Workbooks(ReconFile).Worksheets("c170").Activate
Range("A1").Select
Range("A1").PasteSpecial xlPasteValues
Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Range("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
RowCount = Worksheets("c120").Cells(Rows.Count, 1).End(xlUp).Row
Range("M1:M1").FormulaR1C1 = "=IF(TRIM(RC[-1])=""c"",-RC[-2],RC[-2])"
Range("M1").Copy
Range("M1:M" & RowCount).PasteSpecial xlPasteFormulas
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(SEARCH(""LEDGER ACCOUNT"",R[2]C[-14])),R[-1]C,MID(TRIM(R[2]C[-14]),SEARCH("":"",TRIM(R[2]C[-14]))+2,6))"
Range("O2").Copy
Range("O2:O" & RowCount).PasteSpecial xlPasteFormulas
Else: GoTo c190
End If




c190:
Answer = InputBox("Do you want to update the c190 detail? Yes or No?", "Update", "Yes")
If Answer = "Yes" Then
Workbooks(ReconFile).Worksheets("c190").Activate
Range("A:Z").ClearContents
CurrFile = FilePath & "c190.txt"
Workbooks.Open Filename:=CurrFile
Range("A:A").Select
Range("A:A").Copy
Workbooks(ReconFile).Worksheets("c190").Activate
Range("A1").Select
Range("A1").PasteSpecial xlPasteValues
Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Range("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
RowCount = Worksheets("c120").Cells(Rows.Count, 1).End(xlUp).Row
Range("M1:M1").FormulaR1C1 = "=IF(TRIM(RC[-1])=""c"",-RC[-2],RC[-2])"
Range("M1").Copy
Range("M1:M" & RowCount).PasteSpecial xlPasteFormulas
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(SEARCH(""LEDGER ACCOUNT"",R[2]C[-14])),R[-1]C,MID(TRIM(R[2]C[-14]),SEARCH("":"",TRIM(R[2]C[-14]))+2,6))"
Range("O2").Copy
Range("O2:O" & RowCount).PasteSpecial xlPasteFormulas
Else: GoTo c195
End If




c195:
Answer = InputBox("Do you want to update the c195 detail? Yes or No?", "Update", "Yes")
If Answer = "Yes" Then
Workbooks(ReconFile).Worksheets("c195").Activate
Range("A:Z").ClearContents
CurrFile = FilePath & "c195.txt"
Workbooks.Open Filename:=CurrFile
Range("A:A").Select
Range("A:A").Copy
Workbooks(ReconFile).Worksheets("c195").Activate
Range("A1").Select
Range("A1").PasteSpecial xlPasteValues
Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
Range("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
:=Array(1, 1), TrailingMinusNumbers:=True
RowCount = Worksheets("c120").Cells(Rows.Count, 1).End(xlUp).Row
Range("M1:M1").FormulaR1C1 = "=IF(TRIM(RC[-1])=""c"",-RC[-2],RC[-2])"
Range("M1").Copy
Range("M1:M" & RowCount).PasteSpecial xlPasteFormulas
Range("O2").Select
ActiveCell.FormulaR1C1 = _
"=IF(ISERROR(SEARCH(""LEDGER ACCOUNT"",R[2]C[-14])),R[-1]C,MID(TRIM(R[2]C[-14]),SEARCH("":"",TRIM(R[2]C[-14]))+2,6))"
Range("O2").Copy
Range("O2:O" & RowCount).PasteSpecial xlPasteFormulas
End If
End Sub
 
Sort by date Sort by votes
welcome to the forum. I hope you will find it as useful as I do.

First, when posting code, please enclose it in code tags. they look like [ c o d e ] and [ / c o d e ] without the spaces.


Now let me describe your code and see if I understand it.

1) You have a workbook (recon) that has a number of sheets in it.
2) You have folders that have text files that match the sheet names in the recon file
3) you want to input the year and month where the text files exists
4) for each sheet in the file you want to ask if it should be updated and then copy the contents from the text file and do some other things.

Here is how it would look. Now there is a line in RED the I left in. I think it was a typo but I left it to be sure.

Code: 
Option Explicit




Sub NonTradeReconUpdate()
'9/22/2014
'Macro to update the MK company non trade reconciliation templates


'Variable Declarations
Dim Year As String
Dim Month As String
Dim MonthYear As String
Dim FilePath As String
Dim CurrFile As String
Dim Answer As String
Dim WS As Worksheet




Dim ReconFile As String
ReconFile = ActiveWorkbook.Name


'Retrieve Data
Year = InputBox("Please input the year you are reconciling.", "Year", "eg: 2014")
Month = InputBox("Please input the month you are reconciling.", "Month", "eg: 12")




MonthYear = Month & "-" & Right(Year, 2)
FilePath = "\\File01\busacct\Chris Casey\Monthly Non.Trade\" & Year & "\" & MonthYear & "\"
'\\File01\busacct\Chris Casey\Monthly Non.Trade\2014\08-14


For Each WS In Worksheets
    If WS.Name <> "110-REC" Then
        If (MsgBox("Do you want to update the " & WS.Name & "?", vbYesNo, "Update") = vbYes) Then
        WS.Activate
        Range("A:Z").ClearContents
        CurrFile = FilePath & WS.Name
        If Dir(CurrFile) <> "" Then
            Workbooks.Open Filename:=CurrFile
            Range("A:A").Copy
        WS.Activate
        Range("A1").PasteSpecial xlPasteValues
        Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
        Range("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
        Range("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
        Dim RowCount As Long
[COLOR=#ff0000]        '????[/COLOR]
[COLOR=#ff0000]        RowCount = Worksheets("c120").Cells(Rows.Count, 1).End(xlUp).Row
        'if the should have been Wordsheets("C102") then it would be
        ' RowCount = WS.Cells(Rows[/COLOR][COLOR=#FF0000].Count, 1).End(xlUp).Row[/COLOR]
        Range("M1:M1").FormulaR1C1 = "=IF(TRIM(RC[-1])=""c"",-RC[-2],RC[-2])"
        Range("M1").Copy
        Range("M1:M" & RowCount).PasteSpecial xlPasteFormulas
        Range("O2").Select
        ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(SEARCH(""LEDGER ACCOUNT"",R[2]C[-14])),R[-1]C,MID(TRIM(R[2]C[-14]),SEARCH("":"",TRIM(R[2]C[-14]))+2,6))"
        Range("O2").Copy
        Range("O2:O" & RowCount).PasteSpecial xlPasteFormulas
        End If
    End If
Next
End Sub
 
Upvote 0
Thank you for your help. I will remember to put the code in tags from now on. I am having one struggle, it keeps giving me a [next with for] error. I tried moving around the end if and next but I keep getting the same error. any thoughts?

Again, thank you for all your help.












par60056 said:
welcome to the forum. I hope you will find it as useful as I do.

First, when posting code, please enclose it in code tags. they look like [ c o d e ] and [ / c o d e ] without the spaces.


Now let me describe your code and see if I understand it.

1) You have a workbook (recon) that has a number of sheets in it.
2) You have folders that have text files that match the sheet names in the recon file
3) you want to input the year and month where the text files exists
4) for each sheet in the file you want to ask if it should be updated and then copy the contents from the text file and do some other things.

Here is how it would look. Now there is a line in RED the I left in. I think it was a typo but I left it to be sure.

Code: 
Option Explicit




Sub NonTradeReconUpdate()
'9/22/2014
'Macro to update the MK company non trade reconciliation templates


'Variable Declarations
Dim Year As String
Dim Month As String
Dim MonthYear As String
Dim FilePath As String
Dim CurrFile As String
Dim Answer As String
Dim WS As Worksheet




Dim ReconFile As String
ReconFile = ActiveWorkbook.Name


'Retrieve Data
Year = InputBox("Please input the year you are reconciling.", "Year", "eg: 2014")
Month = InputBox("Please input the month you are reconciling.", "Month", "eg: 12")




MonthYear = Month & "-" & Right(Year, 2)
FilePath = "\\File01\busacct\Chris Casey\Monthly Non.Trade\" & Year & "\" & MonthYear & "\"
'\\File01\busacct\Chris Casey\Monthly Non.Trade\2014\08-14


For Each WS In Worksheets
    If WS.Name <> "110-REC" Then
        If (MsgBox("Do you want to update the " & WS.Name & "?", vbYesNo, "Update") = vbYes) Then
        WS.Activate
        Range("A:Z").ClearContents
        CurrFile = FilePath & WS.Name
        If Dir(CurrFile) <> "" Then
            Workbooks.Open Filename:=CurrFile
            Range("A:A").Copy
        WS.Activate
        Range("A1").PasteSpecial xlPasteValues
        Range("A:A").TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
        :="|", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True
        Range("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
        Range("J:J").TextToColumns Destination:=Range("J1"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=True, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=False, FieldInfo _
        :=Array(1, 1), TrailingMinusNumbers:=True
        Dim RowCount As Long
[COLOR=#ff0000]        '????[/COLOR]
[COLOR=#ff0000]        RowCount = Worksheets("c120").Cells(Rows.Count, 1).End(xlUp).Row
        'if the should have been Wordsheets("C102") then it would be
        ' RowCount = WS.Cells(Rows[/COLOR][COLOR=#FF0000].Count, 1).End(xlUp).Row[/COLOR]
        Range("M1:M1").FormulaR1C1 = "=IF(TRIM(RC[-1])=""c"",-RC[-2],RC[-2])"
        Range("M1").Copy
        Range("M1:M" & RowCount).PasteSpecial xlPasteFormulas
        Range("O2").Select
        ActiveCell.FormulaR1C1 = _
        "=IF(ISERROR(SEARCH(""LEDGER ACCOUNT"",R[2]C[-14])),R[-1]C,MID(TRIM(R[2]C[-14]),SEARCH("":"",TRIM(R[2]C[-14]))+2,6))"
        Range("O2").Copy
        Range("O2:O" & RowCount).PasteSpecial xlPasteFormulas
        End If
    End If
Next
End Sub
Click to expand...
 
Upvote 0
janki6566 said:
Thank you for your help. I will remember to put the code in tags from now on. I am having one struggle, it keeps giving me a [next with for] error. I tried moving around the end if and next but I keep getting the same error. any thoughts?

Again, thank you for all your help.
Click to expand...
This is usually caused by an if/endif imbalance.

I think everything from the line:
WS.Activate

to the line:
Range("O2:O" & RowCount)

should be indented and another "End If" added.

I see 3 IF and only 2 End IF
 
Upvote 0
Thank you !!!! I am learning so much!!!


par60056 said:
This is usually caused by an if/endif imbalance.

I think everything from the line:
WS.Activate

to the line:
Range("O2:O" & RowCount)

should be indented and another "End If" added.

I see 3 IF and only 2 End IF
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

I
  • Solved
Text to Columns VBA Optimizing
Replies
3
Views
220
MARK858
MARK858
datastudent
  • Solved
VBA: Auto answer pop up message
Replies
3
Views
55
Joe4
Joe4
S
  • Solved
Text to Columns Question
Replies
2
Views
177
Steve001
S
F
  • Solved
Delimiters in VBA has different result than manual
Replies
7
Views
297
Peter_SSs
Peter_SSs
Z
  • Solved
Date and Time Not Sorting
2
Replies
19
Views
411
MARK858
MARK858

Share this page

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
Back
Top