Logo 
Search:

MS Office Forum

 Ask Question   UnAnswered
Home » Forum » MS Office       RSS Feeds

Newbie VB help?

  Asked By: Pedro    Date: Feb 22    Category: MS Office    Views: 664
  

I'm importing a set of data into excel, and I then want to divide the
data up into multiple worksheets based on what's in it.

I have a smattering of VB experience, but it's about 3 years old. Can
anyone help get me started, or point me to a list of example Excel
macros that I can pull apart and modify to suit my needs?

Basically in plain english, what I want to do is:

Read the first row in Worksheet1
If Column C in that row = X, then move row to first empty row in
Worksheet X
If Column C in that row = Y, then move row to first empty row in
worksheet Y
Read the next row in Worksheet1
Loop until an empty row is encountered.

Share: 

 

3 Answers Found

 
Answer #1    Answered By: Renee Lane     Answered On: Feb 22

You can try the following:

Application.ScreenUpdating = False
Sheets.Add.Name = ("SheetX")
Sheets("SheetX").Move After:=Sheets(Sheets.Count)
Sheets.Add.Name = ("SheetY")
Sheets("SheetY").Move After:=Sheets(Sheets.Count)

Dim LastCell As Range
Set LastCell = Worksheets("Sheet1").Range("C1")
Do Until IsEmpty(LastCell)
If LastCell.Value = "X" Then
LastCell.EntireRow.Copy
Sheets("SheetX").Select
Range("C65536").End(xlUp)(2, -1).Select
ActiveSheet.Paste
LastCell.EntireRow.Delete
Set LastCell = Worksheets("Sheet1").Range("C1")
End If
If LastCell.Value = "Y" Then
LastCell.EntireRow.Copy
Sheets("SheetY").Select
Range("C65536").End(xlUp)(2, -1).Select
ActiveSheet.Paste
LastCell.EntireRow.Delete
Set LastCell = Worksheets("Sheet1").Range("C1")
End If
If LastCell.Value <> "X" Then
If LastCell.Value <> "Y" Then
Set LastCell = LastCell(2, 1)
End If
End If
Loop
Application.ScreenUpdating = True

 
Answer #2    Answered By: Volney Fischer     Answered On: Feb 22

Personally I got into VBA programming by purchasing a set  of "VBA Macro
Examples". It saved me a lot of time.
Regarding your question - this code will do it:

Option Explicit
Public Sub doit()
'Sheet1 is source, Sheet2, Sheet3 are targets
Dim sourceRow As Integer
Dim targetRow() As Integer
Dim sheetNum As Integer
Dim rng As Range
'there are two target sheets
ReDim targetRow(1)
'for some obscure reason the following lines return 1 when the sheets are empty!
targetRow(0) = Worksheets("Sheet2").UsedRange.Rows.Count
targetRow(1) = Worksheets("Sheet3").UsedRange.Rows.Count
sourceRow = 1
While Worksheets("sheet1").Cells(sourceRow, 1).Value <> ""
sheetNum = Worksheets("sheet1").Cells(sourceRow, 3)
Worksheets("sheet1").Rows(sourceRow).Copy
Set rng = Worksheets("sheet" & sheetNum).Rows(targetRow(sheetNum - 2))
Worksheets("sheet1").Rows(sourceRow).Copy rng

sourceRow = sourceRow + 1
targetRow(sheetNum - 2) = targetRow(sheetNum - 2) + 1
Wend
End Sub

 
Answer #3    Answered By: Sophie Campbell     Answered On: Feb 22

What book or website did you purchase the "VBA Macro Examples"?

I've looked on Amazon.com at the "Holy Macro! 1,900 VBA Examples" and it
didn't get very good reviews.

Curious what you found to be helpful.

 
Didn't find what you were looking for? Find more on Newbie VB help? Or get search suggestion and latest updates.




Tagged: