Solved How to write an excel macro that copies data between sheets

September 11, 2015 at 12:49:23
Specs: Windows 7
I'm manually entering payroll data and I feel certain this process could be automated.

In one spreadsheet I have the list of employee names and IDs. In another sheet I have the payroll schedule. Each employee ID needs to be copied next to the 21 pay dates in the calendar year. That process repeats for every employee.

I understand how to record a macro (what to click) but don't know how to identify mistakes when my recording doesn't do what I intended.

How do I create a macro that copies information from one cell in spreadsheet one 21 times down a column in spreadhseet two before moving onto the next cell in spreadsheet one?


See More: How to write an excel macro that copies data between sheets

Report •


#1
September 11, 2015 at 18:20:33
✔ Best Answer
re: I understand how to record a macro (what to click) but don't know how to identify mistakes when my recording doesn't do what I intended."

As you probably realize, the macro recorder creates very specific code which will basically do nothing more than repeat the steps you took while creating it. To make matters worse, the code is often bloated and inefficient. It just about always need to be cleaned and modified.

One place to start is by reviewing the steps detailed in this tutorial:

http://www.computing.net/howtos/sho...

These debugging techniques will not only help you "identify mistakes" but it can also help you learn how to write VBA code. By using the debugging techniques discussed, you can reverse engineer code that you find in forums such as these as means to understand how the code does what it does.

re: "How do I create a macro that copies information from one cell in spreadsheet one 21 times down a column in spreadhseet two before moving onto the next cell in spreadsheet one?"

Without the specifics related to your spreadsheet layout, the best we can offer is a generic solution. Perhaps the following code will help you get started, especially if you use the debugging techniques from the tutorial to help you follow along as the code does it's thing.

Let's say you start with this in Sheet1:

     A 
1   Tom
2   Bob
3   Sue

This code will place Tom in A1:A21 of Sheet2, then place Bob in A22:A42, etc.

Sub Copy21Times()
'Determine last Row with data in Sheet 1 Column A
  lastRw = Sheets(1).Range("A" & Rows.Count).End(xlUp).Row
'Intialize Sheet 2 row counter
  rw2 = 1
'Loop through Sheet1!A1:A(lastRw)
    For rw1 = 1 To lastRw
'Loop to copy Sheet1 data 21 times
     For nxtRw = rw2 To rw2 + 20
       Sheets(2).Range("A" & nxtRw) = Sheets(1).Range("A" & rw1)
     Next
'Increment Sheet 2 row counter
     rw2 = rw2 + 21
    Next
End Sub

You will note my extensive use of comments. That is a great habit to get into. I didn't include the comments just to help you understand the code, I included them so that the next time *I* look at the code, I'll know what I was trying to do.

I have written hundreds of macros for myself and for other people. Without the extensive use of comments, I'd never be able to remember what all of the macros are doing.

A very wise programmer once said:

"Code Tells You How, Comments Tell You Why"
==============================


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


Report •
Related Solutions


Ask Question