I have several columns were there are 2 types for each category.
Examples:
Federal Tax Due........................Federal Tax Paid
State Tax Due...........................State Tax Paid
Local 1 Tax Due.........................Local 1 Tax Paid
Local 2 Tax Due.........................Local 2 Tax Paid
Social Security Tax Due.............Social Security Tax Paid
Medicare Tax Due......................Medicare Tax Paid
In every case, the "Due" values should equal the "Paid" values. How do I auto populate the "Due" columns when all the columns (Due and Paid) are in different positons with each file? I know I need to reference a header value, but not sure how to do it. In layman's terms, it would read like this:
If column header is Federal Tax Due, then populate column with values from Federal Tax Paid. (do this for all Tax categories as listed above)
Full Version: Conditionally Populate Column Values From Another Column
Any chance you can attach even a small sample file? maybe with data manipulated to de sensitize if necessary? I'd love to help but I'm having trouble visualizing what you mean.
Here is a sample. Due needs to equal Paid for all corrsponding columns
Oh sorry, I meant data. But that's OK, leads me to my next clarification question. Ok so are you talking about VBA, where when you select for example the Due version of col A, via a filter, then col E automatically populates with that value?
In other words, the question isn't about data already being there and filtering it a certain way. the need is that as you actually type something in cell A2, cell E2 immediately populates the same thing.
And you can't use a formula in E2 that simply says =a2 ?
In other words, the question isn't about data already being there and filtering it a certain way. the need is that as you actually type something in cell A2, cell E2 immediately populates the same thing.
And you can't use a formula in E2 that simply says =a2 ?
The data comes to me with PAID already being populated. When I get the file, I have to move the data into our company template and then populate the DUE columns with the values found in the PAID columns.
Does that answer your question?
Yes, I can use the formula A2 = C2. I can also copy the whole PAID column and paste into the DUE column if I wanted to (need to rewrite the column header though). But the columns move everytime. They are not always in A and C (for example).
I want to find a way to create a macro in VB that, when launched, will find these 4 PAID and 4DUE columns, match them up accordingly, and populate the DUE column. Doing this manually is really such a waste of time and leaves room for human error. Either the wrong columns are copied or columns are completely forgotten about. I need to streamline, simplify and dummy-proof.
Does that answer your question?
Yes, I can use the formula A2 = C2. I can also copy the whole PAID column and paste into the DUE column if I wanted to (need to rewrite the column header though). But the columns move everytime. They are not always in A and C (for example).
I want to find a way to create a macro in VB that, when launched, will find these 4 PAID and 4DUE columns, match them up accordingly, and populate the DUE column. Doing this manually is really such a waste of time and leaves room for human error. Either the wrong columns are copied or columns are completely forgotten about. I need to streamline, simplify and dummy-proof.
Kind of - what about the formula that just says =A2 ? Are you wanting something more automated - is that why you're not using that formula? Please forgive me and bear with me as I try to make sure I understand.
If you want something automated, then what is the exact situation of the file when you first receive it....is it exactly like you showed me in the screenshot, only difference that the actual values in columns A, B, C, D, I and J are empty?
If you want something automated, then what is the exact situation of the file when you first receive it....is it exactly like you showed me in the screenshot, only difference that the actual values in columns A, B, C, D, I and J are empty?
See my edits above...
ok, will post back in a few min
Thanks. PS: I just noticed that my sample included local taxes...ignore those. Just the other 4 are needed.
so you have no idea which columns will be which? the macro must find which columns are which, and create/insert new ones, matching the paid corresponding one? I can put these new columns wherever I want?
Yep!
thanks, can you post a sample sheet just the way you get it? to help me work with it as i develop the macro
Here's a routine for you. Put it in a separate workbook and run it...
CODE
Sub corrieann2()
Dim wb As Workbook
Dim myfile As String
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim myRange As Range
Dim myString As String
Dim NewSheet As Worksheet
Dim fedtaxdue As Range
Dim statetaxdue As Range
Dim sstaxdue As Range
Dim medtaxdue As Range
Dim lastrow As Integer
Set Dlg = Application.FileDialog(msoFileDialogFilePicker)
With Dlg
.Title = "Please select your source workbook"
.AllowMultiSelect = False
If .Show = -1 Then
txtFilePath = .InitialFileName
Else
Exit Sub
End If
End With
For i = 1 To Dlg.SelectedItems.Count
myfile = Dlg.SelectedItems(i)
Next i
Set wb = Workbooks.Open(myfile)
'find which sheet in book it is
For Each ws2 In wb.Worksheets
For Each myRange In ws2.Range("A1:Z1")
If StrConv(myRange.Value, vbLowerCase) = "federal tax due" Then
Set ws = ws2
Exit For
End If
Next myRange
Next ws2
lastrow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
'find federal tax due
Set fedtaxdue = ws.Cells.Find("Federal Tax Due", LookIn:=xlValues)
'find state tax due
Set statetaxdue = ws.Cells.Find("State Tax Due", LookIn:=xlValues)
'find ss tax due
Set sstaxdue = ws.Cells.Find("Social Security Tax Due", LookIn:=xlValues)
'find medicare tax due
Set medtaxdue = ws.Cells.Find("Medicare Tax Due", LookIn:=xlValues)
Set NewSheet = Worksheets.Add
NewSheet.Name = "NewSheet1"
fedtaxdue.EntireColumn.Copy NewSheet.Range("A1")
statetaxdue.EntireColumn.Copy NewSheet.Range("B1")
sstaxdue.EntireColumn.Copy NewSheet.Range("C1")
medtaxdue.EntireColumn.Copy NewSheet.Range("D1")
'put fed tax paid
lastrow = NewSheet.Range("A" & NewSheet.Rows.Count).End(xlUp).Row
For Each myRange In NewSheet.Range("A2:A" & lastrow)
myRange.Offset(0, 4).Value = myRange.Value
Next myRange
NewSheet.Range("E1").Value = "Federal Tax Paid"
'put state tax paid
lastrow = NewSheet.Range("B" & NewSheet.Rows.Count).End(xlUp).Row
For Each myRange In NewSheet.Range("B2:B" & lastrow)
myRange.Offset(0, 4).Value = myRange.Value
Next myRange
NewSheet.Range("F1").Value = "State Tax Paid"
'put ss tax paid
lastrow = NewSheet.Range("C" & NewSheet.Rows.Count).End(xlUp).Row
For Each myRange In NewSheet.Range("c2:c" & lastrow)
myRange.Offset(0, 4).Value = myRange.Value
Next myRange
NewSheet.Range("G1").Value = "Social Security Tax Paid"
'put medicare tax paid
lastrow = NewSheet.Range("D" & NewSheet.Rows.Count).End(xlUp).Row
For Each myRange In NewSheet.Range("D2:D" & lastrow)
myRange.Offset(0, 4).Value = myRange.Value
Next myRange
NewSheet.Range("H1").Value = "Medicare Tax Paid"
NewSheet.Columns.AutoFit
MsgBox "Done", vbExclamation, "UtterAccess.com"
End Sub
Dim wb As Workbook
Dim myfile As String
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim myRange As Range
Dim myString As String
Dim NewSheet As Worksheet
Dim fedtaxdue As Range
Dim statetaxdue As Range
Dim sstaxdue As Range
Dim medtaxdue As Range
Dim lastrow As Integer
Set Dlg = Application.FileDialog(msoFileDialogFilePicker)
With Dlg
.Title = "Please select your source workbook"
.AllowMultiSelect = False
If .Show = -1 Then
txtFilePath = .InitialFileName
Else
Exit Sub
End If
End With
For i = 1 To Dlg.SelectedItems.Count
myfile = Dlg.SelectedItems(i)
Next i
Set wb = Workbooks.Open(myfile)
'find which sheet in book it is
For Each ws2 In wb.Worksheets
For Each myRange In ws2.Range("A1:Z1")
If StrConv(myRange.Value, vbLowerCase) = "federal tax due" Then
Set ws = ws2
Exit For
End If
Next myRange
Next ws2
lastrow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
'find federal tax due
Set fedtaxdue = ws.Cells.Find("Federal Tax Due", LookIn:=xlValues)
'find state tax due
Set statetaxdue = ws.Cells.Find("State Tax Due", LookIn:=xlValues)
'find ss tax due
Set sstaxdue = ws.Cells.Find("Social Security Tax Due", LookIn:=xlValues)
'find medicare tax due
Set medtaxdue = ws.Cells.Find("Medicare Tax Due", LookIn:=xlValues)
Set NewSheet = Worksheets.Add
NewSheet.Name = "NewSheet1"
fedtaxdue.EntireColumn.Copy NewSheet.Range("A1")
statetaxdue.EntireColumn.Copy NewSheet.Range("B1")
sstaxdue.EntireColumn.Copy NewSheet.Range("C1")
medtaxdue.EntireColumn.Copy NewSheet.Range("D1")
'put fed tax paid
lastrow = NewSheet.Range("A" & NewSheet.Rows.Count).End(xlUp).Row
For Each myRange In NewSheet.Range("A2:A" & lastrow)
myRange.Offset(0, 4).Value = myRange.Value
Next myRange
NewSheet.Range("E1").Value = "Federal Tax Paid"
'put state tax paid
lastrow = NewSheet.Range("B" & NewSheet.Rows.Count).End(xlUp).Row
For Each myRange In NewSheet.Range("B2:B" & lastrow)
myRange.Offset(0, 4).Value = myRange.Value
Next myRange
NewSheet.Range("F1").Value = "State Tax Paid"
'put ss tax paid
lastrow = NewSheet.Range("C" & NewSheet.Rows.Count).End(xlUp).Row
For Each myRange In NewSheet.Range("c2:c" & lastrow)
myRange.Offset(0, 4).Value = myRange.Value
Next myRange
NewSheet.Range("G1").Value = "Social Security Tax Paid"
'put medicare tax paid
lastrow = NewSheet.Range("D" & NewSheet.Rows.Count).End(xlUp).Row
For Each myRange In NewSheet.Range("D2:D" & lastrow)
myRange.Offset(0, 4).Value = myRange.Value
Next myRange
NewSheet.Range("H1").Value = "Medicare Tax Paid"
NewSheet.Columns.AutoFit
MsgBox "Done", vbExclamation, "UtterAccess.com"
End Sub
A couple of things:
1) I need to launch the macro from the open book, as opposed to browsing for the file
2) The DUE columns already exist in the template. I just need to populate them from the PAID columns in the template.
1) I need to launch the macro from the open book, as opposed to browsing for the file
2) The DUE columns already exist in the template. I just need to populate them from the PAID columns in the template.
I reversed the Due/Paid logic in your script. I probably wasn't clear on this. Paid is provided. We need to create DUE.
CODE
Sub TransactionTaxesDue_Paid()
Dim wb As Workbook
Dim myfile As String
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim myRange As Range
Dim myString As String
Dim NewSheet As Worksheet
Dim fedtaxdue As Range
Dim statetaxdue As Range
Dim sstaxdue As Range
Dim medtaxdue As Range
Dim lastrow As Integer
Set Dlg = Application.FileDialog(msoFileDialogFilePicker)
With Dlg
.Title = "Please select your source workbook"
.AllowMultiSelect = False
If .Show = -1 Then
txtFilePath = .InitialFileName
Else
Exit Sub
End If
End With
For I = 1 To Dlg.SelectedItems.Count
myfile = Dlg.SelectedItems(I)
Next I
Set wb = Workbooks.Open(myfile)
'find which sheet in book it is
For Each ws2 In wb.Worksheets
For Each myRange In ws2.Range("A1:Z1")
If StrConv(myRange.Value, vbLowerCase) = "federal tax due" Then
Set ws = ws2
Exit For
End If
Next myRange
Next ws2
lastrow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
'find federal tax paid
Set fedtaxdue = ws.Cells.Find("Federal Tax Paid", LookIn:=xlValues)
'find state tax paid
Set statetaxdue = ws.Cells.Find("State Tax Paid", LookIn:=xlValues)
'find ss tax paid
Set sstaxdue = ws.Cells.Find("Social Security Tax Paid", LookIn:=xlValues)
'find medicare tax paid
Set medtaxdue = ws.Cells.Find("Medicare Tax Paid", LookIn:=xlValues)
Set NewSheet = Worksheets.Add
NewSheet.Name = "NewSheet1"
fedtaxdue.EntireColumn.Copy NewSheet.Range("A1")
statetaxdue.EntireColumn.Copy NewSheet.Range("B1")
sstaxdue.EntireColumn.Copy NewSheet.Range("C1")
medtaxdue.EntireColumn.Copy NewSheet.Range("D1")
'put fed tax due
lastrow = NewSheet.Range("A" & NewSheet.Rows.Count).End(xlUp).Row
For Each myRange In NewSheet.Range("A2:A" & lastrow)
myRange.Offset(0, 4).Value = myRange.Value
Next myRange
NewSheet.Range("E1").Value = "Federal Tax Due"
'put state tax Due
lastrow = NewSheet.Range("B" & NewSheet.Rows.Count).End(xlUp).Row
For Each myRange In NewSheet.Range("B2:B" & lastrow)
myRange.Offset(0, 4).Value = myRange.Value
Next myRange
NewSheet.Range("F1").Value = "State Tax Due"
'put ss tax due
lastrow = NewSheet.Range("C" & NewSheet.Rows.Count).End(xlUp).Row
For Each myRange In NewSheet.Range("c2:c" & lastrow)
myRange.Offset(0, 4).Value = myRange.Value
Next myRange
NewSheet.Range("G1").Value = "Social Security Tax Due"
'put medicare tax due
lastrow = NewSheet.Range("D" & NewSheet.Rows.Count).End(xlUp).Row
For Each myRange In NewSheet.Range("D2:D" & lastrow)
myRange.Offset(0, 4).Value = myRange.Value
Next myRange
NewSheet.Range("H1").Value = "Medicare Tax Due"
NewSheet.Columns.AutoFit
MsgBox "Done", vbExclamation, "UtterAccess.com"
End Sub
Dim wb As Workbook
Dim myfile As String
Dim ws As Worksheet
Dim ws2 As Worksheet
Dim myRange As Range
Dim myString As String
Dim NewSheet As Worksheet
Dim fedtaxdue As Range
Dim statetaxdue As Range
Dim sstaxdue As Range
Dim medtaxdue As Range
Dim lastrow As Integer
Set Dlg = Application.FileDialog(msoFileDialogFilePicker)
With Dlg
.Title = "Please select your source workbook"
.AllowMultiSelect = False
If .Show = -1 Then
txtFilePath = .InitialFileName
Else
Exit Sub
End If
End With
For I = 1 To Dlg.SelectedItems.Count
myfile = Dlg.SelectedItems(I)
Next I
Set wb = Workbooks.Open(myfile)
'find which sheet in book it is
For Each ws2 In wb.Worksheets
For Each myRange In ws2.Range("A1:Z1")
If StrConv(myRange.Value, vbLowerCase) = "federal tax due" Then
Set ws = ws2
Exit For
End If
Next myRange
Next ws2
lastrow = ws.Range("A" & ws.Rows.Count).End(xlUp).Row
'find federal tax paid
Set fedtaxdue = ws.Cells.Find("Federal Tax Paid", LookIn:=xlValues)
'find state tax paid
Set statetaxdue = ws.Cells.Find("State Tax Paid", LookIn:=xlValues)
'find ss tax paid
Set sstaxdue = ws.Cells.Find("Social Security Tax Paid", LookIn:=xlValues)
'find medicare tax paid
Set medtaxdue = ws.Cells.Find("Medicare Tax Paid", LookIn:=xlValues)
Set NewSheet = Worksheets.Add
NewSheet.Name = "NewSheet1"
fedtaxdue.EntireColumn.Copy NewSheet.Range("A1")
statetaxdue.EntireColumn.Copy NewSheet.Range("B1")
sstaxdue.EntireColumn.Copy NewSheet.Range("C1")
medtaxdue.EntireColumn.Copy NewSheet.Range("D1")
'put fed tax due
lastrow = NewSheet.Range("A" & NewSheet.Rows.Count).End(xlUp).Row
For Each myRange In NewSheet.Range("A2:A" & lastrow)
myRange.Offset(0, 4).Value = myRange.Value
Next myRange
NewSheet.Range("E1").Value = "Federal Tax Due"
'put state tax Due
lastrow = NewSheet.Range("B" & NewSheet.Rows.Count).End(xlUp).Row
For Each myRange In NewSheet.Range("B2:B" & lastrow)
myRange.Offset(0, 4).Value = myRange.Value
Next myRange
NewSheet.Range("F1").Value = "State Tax Due"
'put ss tax due
lastrow = NewSheet.Range("C" & NewSheet.Rows.Count).End(xlUp).Row
For Each myRange In NewSheet.Range("c2:c" & lastrow)
myRange.Offset(0, 4).Value = myRange.Value
Next myRange
NewSheet.Range("G1").Value = "Social Security Tax Due"
'put medicare tax due
lastrow = NewSheet.Range("D" & NewSheet.Rows.Count).End(xlUp).Row
For Each myRange In NewSheet.Range("D2:D" & lastrow)
myRange.Offset(0, 4).Value = myRange.Value
Next myRange
NewSheet.Range("H1").Value = "Medicare Tax Due"
NewSheet.Columns.AutoFit
MsgBox "Done", vbExclamation, "UtterAccess.com"
End Sub
QUOTE
The DUE columns already exist in the template. I just need to populate them from the PAID columns in the template
I realize that, but the way I came up with it was the way I was able to code it best. You can just delete the other columns or the entire sheet for that matter. what I gave you pretty much re-creates everything. Why is that an issue? As long as you end up with what you started with + the due columns populated..doesn't that achieve your objective?
CODE
I need to launch the macro from the open book, as opposed to browsing for the file
I thought you said this file came from someone else and was given to you? So you'd rather have a .bas file that you have to import into the file you receive, each time?
That's fine..just adjust my code to suit.
Oh, ok...I wasn't sure what exactly the code did because it gets caught on this line:
...so, I was trying to read the actual script (not my strong side) and determine what it was doing. I guess I didn't figure it right.
I commented out the part that browses for the file so that I can launch the script from an open workbook -- there is a lot of work we have to do to the file before we even get to this point of dealing with DUE and PAID -- but it still hangs on the line I mentioned above. The error reads: Run-time error '91': Object variable or With block variable not set.
CODE
For I = 1 To Dlg.SelectedItems.Count
myfile = Dlg.SelectedItems(I)
Next I
myfile = Dlg.SelectedItems(I)
Next I
...so, I was trying to read the actual script (not my strong side) and determine what it was doing. I guess I didn't figure it right.
I commented out the part that browses for the file so that I can launch the script from an open workbook -- there is a lot of work we have to do to the file before we even get to this point of dealing with DUE and PAID -- but it still hangs on the line I mentioned above. The error reads: Run-time error '91': Object variable or With block variable not set.
Comment that out too. and change
Set wb = Workbooks.Open(myfile)
to
Set wb = ThisWorkbook
Set wb = Workbooks.Open(myfile)
to
Set wb = ThisWorkbook
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.