Excel Macro help

[Kasturi]

Since I havent written a macro in quite some time, I am having a little difficulty getting started

I am needing to write a macro that will take info from one spreadsheet and place it into another spreadsheet in a different order (column wise).
Plus I want the macro to loop thru the data until all info is brought over.

Im sure this would probably me easier in Access but I need it done in Excel.



Say I have info in Column A from Sheet 1 that I want to place in Column D on Sheet 2, then I want the the info from Sheet 1 columns E & F to go into Sheet 2 columns H & I.


My next problem is creating something that will take a name and split it into two seperate columns. For example take the name John Doe and seperate it into Last Name (column B) and First Name (Column C)

Lets call the columns (A-D )in Sheet 1 (Number, Employee Name, T total, Ind Total,)

And the columns (B,C,D, H,I) in Sheet 2 (Last name, First Name, Number, Ind total, T total)


Any Help would be greatly appreciated.

[jdeb]

Try this link

Excel VBA Macro Code. Excel Macro Examples Page 2

[strollin]

Here's a macro that should be pretty close to what you want:

Sub CopyData()

Dim Row As Integer
Dim Name, FirstName, LastName As String

Row = 2 'Skip Column Header in Row 1
Do
   With ThisWorkbook.Worksheets(1)
      ' Stop when a blank row is encountered
      If .Cells(Row, 1) = "" Or .Cells(Row, 1) = " " Then
         Exit Do
      Else
         ' Copy Number value to 2nd wks
         Worksheets(2).Cells(Row, 3).Value = .Cells(Row, 1)

         ' Split Name into First & Last copy to 2nd wks
         Call SplitName(.Cells(Row, 2), FirstName, LastName)
         Worksheets(2).Cells(Row, 1).Value = LastName
         Worksheets(2).Cells(Row, 2).Value = FirstName

         ' Copy T Total to 2nd wks
         Worksheets(2).Cells(Row, 5).Value = .Cells(Row, 3)

         ' Copy Ind Total to 2nd wks
         Worksheets(2).Cells(Row, 4).Value = .Cells(Row, 4)
   
         ' Advance ro next Row
         Row = Row + 1

      End If
   End With
Loop

' Show 2nd wks when done
Worksheets(2).Activate

End Sub

Sub SplitName(Name, FirstName, LastName)
' Name is assumed to be in the format of "John Doe" or John M Doe"
' No allowance has been made for "John M Doe, Jr"
' The first part of the name is considered the First Name and the last is
' considered the Last Name

Dim NameParts() As String

NameParts = Split(trim(Name))
FirstName = NameParts(0)
LastName = NameParts(UBound(NameParts))

End Sub

[Kasturi]

Thanks very much strollin, this helped out quite a bit.

Another couple questions if you dont mind.

1. How to you create a macro to prompt the user to enter a date into a column that only has data in it. For example say I have 30 lines and I need to place the date 04/30/11 on each line under Column F titled End Date?

This date will be different every month.


2. I am trying to add a border around the information that was copied over and I am having difficulty doing so. I was able to get it to create a border around my coulmn headings, but I feel that this isnt correct. I am not sure how to place a border around everything without typing in a certain range. This is what I have written(below). As you can see my range is set to A1:M1 and I want it to place a border around everything. I thought about just using "A:M" but this would select the whole spreadsheet all 65k lines. I am having issues getting the borders to stop after it gets to the last line.

I could put a prederdimend range in there (A1:M60) for example, but some months I may have more items and some months I may have less.

Range("A1:M1").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideVertical)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With
    With Selection.Borders(xlInsideHorizontal)
        .LineStyle = xlContinuous
        .ColorIndex = 0
        .TintAndShade = 0
        .Weight = xlThin
    End With

[strollin]

1. I don't think I understand, you want to prompt user to replace data in a column with a date? Can you elaborate?

2. You could use something like this (Provided M is fixed and just the Row varies):

Dim MaxRange As String
MaxRange = "M" & Trim(Str(Cells.Find(What:="*", After:=[A1], SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row))
Range("A1:" & MaxRange).Select

[Kasturi]

strollin - your the best.

For number 1. The spreadsheet that I have been working on has a column titled End Date. In this column I want the user to be able to enter a date into a prompt that will automatically fiill this column with that date.

So if I have 10 rows that have data, I want the date entered into the prompt to only fill in those rows.

Or if a prompt can not be easily implemented, is there a formula that one could use to fill in the date? I need the date to be the last day of the month. So for example if the date in coulmn E is 4-1-11, the End Date in column F should be 4-30-11, If its 5-1-11,then the end date should be 5-31-11 and so on.


I hope that makes sense.

[strollin]

I found this using google:

=DATE(YEAR(A1),MONTH(A1)+1,0)
Where, we are adding 1 month to the date in A1 (June becomes July) and using zero for the day which forces Excel to return the last day of the month before.

Hopefully, you can use that to get the date you need.

[Kasturi]

thanks again strollin. You have been a big help. I havent worked with macros in I dont know how long. It is slowly coming back to me thanks to you.

I will try this formula out and see if it works.

[Kasturi]

Ok - One more question then I think Im done.

The macro that has been created works great thanks to the help of strollin.

Now the only issue I am having is getting this macro to work on other worksheets.

I want to create a button (or something) that allows me to click on it and run the macro.


I followed the directions in doing this, but when I have a new spreadsheet that I want the macro to run on, it is pulling the information from the spreadsheet where the macro was originally created.

How do I get it to run without being referenced to the old data?



Im not sure if that made any sense at all. I will try to explain again if need be.

[strollin]

You can use Activesheet.Index to find out the index of the currently active sheet. Add 1 to it to access the next sheet.

ThisWorkbook.Worksheets(Activesheet.Index) will reference the current worksheet.