Solved How do I use Excel Macros to Copy data to a new tab?

January 9, 2017 at 17:20:42
Specs: Windows 7
Hello,

Using a few of the threads here I have figured out how to copy my information to the appropriate sheets, but I would like to take it one step further. If anyone would be able to help or guide me to the correct thread, it would be very appreciated.

I have a data dump used to calculate commissions. The data dump is sorted by Territory (Column M). The first row is a header row, I have managed to create the "SortTemp" sheet and sort by the column M, (Territory) and move the data to the appropriate sheets, but I don't know how to sum Column 1. This Column can be 1 row or hundreds of rows.

My end goal is:
1. Create the SortTemp sheet (Completed)
2. Sort by Column M (Territory) (Completed)
3. Create a new Sheet in the same workbook with the Headers from the SortTemp. (Complete)
4. Copy all of the rows to the appropriate sheet. (Complete)
5. Sum Column I on each sheet
6. Two cells below the sum multiply the sum by .05
7. Two Cells to the left of that value write "Commission Payable"

Any assistance or guidance to the proper thread would be very appreciated.

Thanks and Happy New Year!!!

message edited by FCase


See More: How do I use Excel Macros to Copy data to a new tab?

Report •

✔ Best Answer
January 24, 2017 at 08:09:55
There are multiple way to loop through sheets. You can access sheets by name, by order number, etc.

For example, if you need the macro to loop through all sheets in order, then this version will do just that. Note that when you use the With method, you can place a dot (.) before each cell reference and the object referenced by the With will be automatically referenced.

In other words, these 2 snippets are equivalent:

 
 Sheets(1).Range("A1") = 5
 Sheets(1).Range("A2") = 6 
 Sheets(1).Range("A3") = 7

With Sheets(1)
 .Range("A1") = 5
 .Range("A2") = 6 
 .Range("A3") = 7
End With

Using the With method, this code will loop through all sheets:

Sub SumI_v1()
 For shtNum = 1 To Sheets.Count
  With Sheets(shtNum)
 'Determine the last Row with data in Column I
   lastRw = .Cells(Rows.Count, "I").End(xlUp).Row
'Use the lastRw variable and put the formulas and text in the proper cells
   .Cells(lastRw + 1, "I").Formula = _
     "=SUM(" & .Cells(1, "I").Address & ":" & .Cells(lastRw, "I").Address & ")"
   .Cells(lastRw + 3, "I").Formula = _
     "=" & .Cells(lastRw + 1, "I").Address & "*.05"
   .Cells(lastRw + 3, "G") = "Commission Payable"
  End With
 Next
End Sub

If you need to ignore certain sheets, you can reference them in various ways. For example, if you don't want the code to run against a Master sheet, check the sheet name before the main part of the code is reached:

 For shtNum = 1 To Sheets.Count
  If Sheet.Name <> "Master" Then
   With Sheets(shtNum) 
'Do stuff
   End With
  End If
 Next

Just like most things in VBA, there are multiple ways to reference specific worksheets, groups of worksheets, all worksheets, etc. It all depends on what you are trying to do.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03



#1
January 9, 2017 at 18:12:26
I'm confused. Your subject line asks how to use a macro to copy data to a new tab, but the body of your post seems to be asking about summing and multiplying data.

Why the discrepancy?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#2
January 9, 2017 at 18:43:04
Yes, Sorry about that. I had originally wrote asking for assistance with the first few steps, but worked through that roadblock. I edited my original posting, but I didn't see how to edit the subject header.
I actually edited code that you had wrote a few years ago doing a similar process with Month and Year.

Would you be able to provide some assistance please?

message edited by FCase


Report •

#3
January 10, 2017 at 03:44:57
Since I can't see your workbook from where I am sitting, I don't know what your data looks like. It's hard to write code without knowing what I am working with.

Can I assume that each sheet has a different amount if data in Column I and that one of your issues is that you don't know how to determine where to put the formulas?

Can I also assume that you don't know how to write the VBA to construct the formulas and then place them in the appropriate cells?

Is there anything else that you think I need to know?

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

Related Solutions

#4
January 23, 2017 at 15:55:58
You are correct, Column I could be different lengths.

You are also correct that I am not experienced with VBA, I have picked up a lot on here, but I haven't been able to put the finishing touches on. I really appreciate the help.

I don't think there is anything else that you could need. As I see it I need to complete 7 steps as I outlined above. I was able to figure out 1-4, but 5-7 is stumping me.
I would be happy to paste my code to date.

Cheers,


Report •

#5
January 23, 2017 at 20:28:15
Here are 3 of the many, many ways to accomplish steps 5-7.

The first 2 are basically the same in that they put formulas in the cells, not actual values. Those 2 versions show you how to build a formula in VBA using text (within the quotes) and cell references using VBA syntax. Once the cell references are evaluated by VBA, they will be converted to the "standard" cell references that you would use in spreadsheet formula.

The only difference between SumI_v1 and SumI_v2 is how I referenced the cells. In v1, I used the column letters "I" and "G", in v2 I used the column numbers, 9 and 7.

I did this to illustrate that the Row and Column references can be a hard coded number, a letter, a variable or even a calculated value.

Sub SumI_v1()
'Determine the last Row with data in Column I
 lastRw = Cells(Rows.Count, "I").End(xlUp).Row
'Use the lastRw variable and put the formulas and text in the proper cells
 Cells(lastRw + 1, "I").Formula = _
       "=SUM(" & Cells(1, "I").Address & ":" & Cells(lastRw, "I").Address & ")"
 Cells(lastRw + 3, "I").Formula = "=" & Cells(lastRw + 1, "I").Address & "*.05"
 Cells(lastRw + 3, "G") = "Commission Payable"
End Sub


Sub SumI_v2()
'Determine the last Row with data in Column 9 (I)
 lastRw = Cells(Rows.Count, 9).End(xlUp).Row
'Put the formulas and text in the proper cells
 Cells(lastRw + 1, 9).Formula = _
       "=SUM(" & Cells(1, 9).Address & ":" & Cells(lastRw, 9).Address & ")"
 Cells(lastRw + 3, 9).Formula = "=" & Cells(lastRw + 1, 9).Address & "*.05"
 Cells(lastRw + 3, 7) = "Commission Payable"
End Sub

In SumI_v3, I let VBA do the math and then place the values in the cells instead of the formulas. This method simply illustrates that you can reference the spreadsheet cells, then do the math in VBA, then use the answer however you want. You can put the result in a cell, you could use it within VBA to do further calculations, you could use it within VBA to run different parts of the code based on the result, etc.

Sub SumI_v3()
'Determine the last Row with data in Column I
  lastRw = Cells(Rows.Count, "I").End(xlUp).Row
'Let VBA do the math then place the values in the proper cells
  Cells(lastRw + 1, "I") = _
    WorksheetFunction.Sum(Range(Cells(1, "I"), Cells(lastRw, "I")))
  Cells(lastRw + 3, "I") = _
    WorksheetFunction.Sum(Range(Cells(1, "I"), Cells(lastRw, "I"))) * 0.05
  'or
  'Cells(lastRw + 3, "I") = Cells(lastRw + 1, "I") * 0.05
  Cells(lastRw + 3, "G") = "Commission Payable"
End Sub

Let me know if this answers your questions.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#6
January 24, 2017 at 07:20:20
I am sorry, I did miss one detail earlier, there are multiple sheets that require column I to be summed with the Commission payable to be calculated. What is the best way to apply the calculations above to each sheet. (number of sheets will vary).

I really appreciate the help.


Report •

#7
January 24, 2017 at 08:09:55
✔ Best Answer
There are multiple way to loop through sheets. You can access sheets by name, by order number, etc.

For example, if you need the macro to loop through all sheets in order, then this version will do just that. Note that when you use the With method, you can place a dot (.) before each cell reference and the object referenced by the With will be automatically referenced.

In other words, these 2 snippets are equivalent:

 
 Sheets(1).Range("A1") = 5
 Sheets(1).Range("A2") = 6 
 Sheets(1).Range("A3") = 7

With Sheets(1)
 .Range("A1") = 5
 .Range("A2") = 6 
 .Range("A3") = 7
End With

Using the With method, this code will loop through all sheets:

Sub SumI_v1()
 For shtNum = 1 To Sheets.Count
  With Sheets(shtNum)
 'Determine the last Row with data in Column I
   lastRw = .Cells(Rows.Count, "I").End(xlUp).Row
'Use the lastRw variable and put the formulas and text in the proper cells
   .Cells(lastRw + 1, "I").Formula = _
     "=SUM(" & .Cells(1, "I").Address & ":" & .Cells(lastRw, "I").Address & ")"
   .Cells(lastRw + 3, "I").Formula = _
     "=" & .Cells(lastRw + 1, "I").Address & "*.05"
   .Cells(lastRw + 3, "G") = "Commission Payable"
  End With
 Next
End Sub

If you need to ignore certain sheets, you can reference them in various ways. For example, if you don't want the code to run against a Master sheet, check the sheet name before the main part of the code is reached:

 For shtNum = 1 To Sheets.Count
  If Sheet.Name <> "Master" Then
   With Sheets(shtNum) 
'Do stuff
   End With
  End If
 Next

Just like most things in VBA, there are multiple ways to reference specific worksheets, groups of worksheets, all worksheets, etc. It all depends on what you are trying to do.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.

message edited by DerbyDad03


Report •

#8
January 24, 2017 at 12:36:23
This is great, thank you very much!

When the script sums column I the value isn't displayed in the cell, however if you click on the cell you can see the formula in the formula bar. Is it possible to show the sum?

Also I really appreciate all of your help. Is there a way to contact you to send you a gift card?


Report •

#9
January 24, 2017 at 13:14:21
There is nothing that needs to be added to the macro to "show the sum" - assuming that there are actually numeric values in Column I.

It works fine in all of my tests where I manually enter some values in Column I and then run the code.

What happens if you manually enter a formula in a cell to SUM the same range?

Is the sheet set up for auto-calculate?

Try the code in new sheet after you manually enter numeric values in Column I.

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Report •

#10
January 24, 2017 at 13:44:09
Turns out it was the format of the cells, I changed the format on the original sheet before the macro was run and it worked.

Thanks again, I really appreciate all of the help and if you give me your contact info I would be happy to send you a gift card for helping out.

Cheers,
Frank


Report •

#11
Report •

Ask Question