First I want to say Hello all. I am just now really getting into heavly using excel and VBA's. I am taking a class on excel this semester and classes on VBA and SQL programming next semester. I am excited to learn a new and valuable skillset. I am also looking forward to contribuiting to the forum.
Ok so here is my question. I have a workbook that changes month to month on the number of tabs it has. The reason for this is it is used to import Journal Entries into an accounting software. It is much faster doing it this way rather than have someone else manually enter them. All of the Workbooks contain the same number of columns. However, the number of rows varry. The workbooks are set up like this:
A B C D I
Account number Debits Credits Description Reference
First I want to name all worksheets in the workbook based on the value in cell I1. Second step the accounting software we use sees Credits as Negative numbers so what I need to happen first is for the macro to look in column C and when it finds a cell with a number in it, copy the Account number in A and move it to the next open cell in Column A and then in the same row in column B copy the number from C and paste as a Negitive value in B and so on. Then I want the macro to delete the entire column D and shift all other columns to the left so that D now becomes C. Also deleting columns H and I. I would also like the macro to save each worksheet as a new workbook in G:\Users\Andy\2011\GJ's, Pr's, GJTR's, GJTPr's
I have created the following macros but only the rename worksheets macro does all worksheets at once. I can not figure out the part where it will find a value in C and copy the account number and move it to the next open cell in A and copy and paste the value in C as a neg. In case this doesnt make sense Ill give an illustration.
A B C D
1 60 0 Example
5 55 10 Example
3 40 0 Example
9 0 20 Example
I need it to look like this:
1 60 0 Example
5 55 10 Example
3 40 0 Example
9 0 20 Example
5 -10 0 Example
9 -20 0 Example
and so on there may be only 2 rows but there could be 100 it varies.
I have the following macros already. For explanation purposes I am going to number each macro.
Macro 1.
Change name of worksheets in workbook
Sub Worksheet_Name_GJ_Import_Uses_Cell_I1()
Dim ws As Worksheet
For Each ws In Worksheets
On Error Resume Next
ws.Name = ws.Range("I1").Value
If Err.Number <> 0 Then
Err.Clear
Exit Sub
End If
Next ws
End Sub
Macro 2.
And
used to ensure all cells in B are a value and to delete C and move D to C then Delete H and I.
Sub GJ_CLEANUP_PRIOR_TO_IMPORT()
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("H:I").Select
Selection.Delete Shift:=xlToLeft
Dim LastRow As Long, n As Long
LastRow = Range("B65536").End(xlUp).Row
For n = LastRow To 1 Step -1
If Cells(n, 2).Value = 0 Then Cells(n, 2).EntireRow.Delete
Next n
End Sub
Macro 3.
And then I use this to delete all rows that have a zero in B.
Sub Delete_ALL_ROWS_EQUAL_TO_0_Column_B()
Dim LastRow As Long, n As Long
LastRow = Range("B65536").End(xlUp).Row
For n = LastRow To 1 Step -1
If Cells(n, 2).Value = 0 Then Cells(n, 2).EntireRow.Delete
Next n
End Sub
I am asking for help putting all of these together into 1 macro and adding the things from above added in. The Copy and pasting based on C would go inbetween Macro 1 and 2. And the make each worksheet a new workbook and saved in G:\Users\Andy\2011\GJ's, Pr's, GJTR's, GJTPr's Would be last. I have played around with trying to build a macro for this but I have not been successful. I hope What I am asking is clear. Thanks a ton and hopefully someday soon I will be able to be one of the ones answering the questions instead of asking them. Andy
Ok so here is my question. I have a workbook that changes month to month on the number of tabs it has. The reason for this is it is used to import Journal Entries into an accounting software. It is much faster doing it this way rather than have someone else manually enter them. All of the Workbooks contain the same number of columns. However, the number of rows varry. The workbooks are set up like this:
A B C D I
Account number Debits Credits Description Reference
First I want to name all worksheets in the workbook based on the value in cell I1. Second step the accounting software we use sees Credits as Negative numbers so what I need to happen first is for the macro to look in column C and when it finds a cell with a number in it, copy the Account number in A and move it to the next open cell in Column A and then in the same row in column B copy the number from C and paste as a Negitive value in B and so on. Then I want the macro to delete the entire column D and shift all other columns to the left so that D now becomes C. Also deleting columns H and I. I would also like the macro to save each worksheet as a new workbook in G:\Users\Andy\2011\GJ's, Pr's, GJTR's, GJTPr's
I have created the following macros but only the rename worksheets macro does all worksheets at once. I can not figure out the part where it will find a value in C and copy the account number and move it to the next open cell in A and copy and paste the value in C as a neg. In case this doesnt make sense Ill give an illustration.
A B C D
1 60 0 Example
5 55 10 Example
3 40 0 Example
9 0 20 Example
I need it to look like this:
1 60 0 Example
5 55 10 Example
3 40 0 Example
9 0 20 Example
5 -10 0 Example
9 -20 0 Example
and so on there may be only 2 rows but there could be 100 it varies.
I have the following macros already. For explanation purposes I am going to number each macro.
Macro 1.
Change name of worksheets in workbook
Sub Worksheet_Name_GJ_Import_Uses_Cell_I1()
Dim ws As Worksheet
For Each ws In Worksheets
On Error Resume Next
ws.Name = ws.Range("I1").Value
If Err.Number <> 0 Then
Err.Clear
Exit Sub
End If
Next ws
End Sub
Macro 2.
And
used to ensure all cells in B are a value and to delete C and move D to C then Delete H and I.
Sub GJ_CLEANUP_PRIOR_TO_IMPORT()
Cells.Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Columns("C:C").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Columns("H:I").Select
Selection.Delete Shift:=xlToLeft
Dim LastRow As Long, n As Long
LastRow = Range("B65536").End(xlUp).Row
For n = LastRow To 1 Step -1
If Cells(n, 2).Value = 0 Then Cells(n, 2).EntireRow.Delete
Next n
End Sub
Macro 3.
And then I use this to delete all rows that have a zero in B.
Sub Delete_ALL_ROWS_EQUAL_TO_0_Column_B()
Dim LastRow As Long, n As Long
LastRow = Range("B65536").End(xlUp).Row
For n = LastRow To 1 Step -1
If Cells(n, 2).Value = 0 Then Cells(n, 2).EntireRow.Delete
Next n
End Sub
I am asking for help putting all of these together into 1 macro and adding the things from above added in. The Copy and pasting based on C would go inbetween Macro 1 and 2. And the make each worksheet a new workbook and saved in G:\Users\Andy\2011\GJ's, Pr's, GJTR's, GJTPr's Would be last. I have played around with trying to build a macro for this but I have not been successful. I hope What I am asking is clear. Thanks a ton and hopefully someday soon I will be able to be one of the ones answering the questions instead of asking them. Andy
