Help..I need code..Don't know much about VB..Help


Posted by StevieBoy on June 26, 2001 10:37 AM

Hope this isnt too confusing, i need code to attach to a button that...

Here is the code to make the pages,

Sub Copy_sheets()
'Written by Barrie Davidson and edited by Steve Dawson
Dim New_Sheet_Name As String
Dim New_Sheet_Suffix As String
Dim Input_Sheet As String
Dim Sheet_Count As Integer
Range("O6").Select
Input_Sheet = ActiveSheet.Name
Do Until ActiveCell.Row > 24
If Selection.Value = "y" Then
New_Sheet_Suffix = "-" & ActiveCell.Offset(0, -1).Value
Sheet_Count = ActiveWorkbook.Sheets.Count
Sheets("Eng.Assum.").Copy after:=Sheets(Sheet_Count)
New_Sheet_Name = "Eng.Assum." & New_Sheet_Suffix
ActiveSheet.Name = New_Sheet_Name
Sheet_Count = ActiveWorkbook.Sheets.Count
Sheets("Eng.Wksheet").Copy after:=Sheets(Sheet_Count)
New_Sheet_Name = "Eng.Wksheet" & New_Sheet_Suffix
ActiveSheet.Name = New_Sheet_Name
Application.Worksheets(Input_Sheet).Select
ActiveCell.Offset(1, 0).Select
Else
ActiveCell.Offset(1, 0).Select
End If
Loop
End Sub


After the code makes the pages, I need different code (to attach to a different button) to take all the pages with "Eng.Assum.-"(there will be different text for each after the "-" and put it on a new sheet called "Eng.Assum.-Roll" and the same thing for "Eng.Wksheet-" on a page called "Eng.Wksheet-Roll".

The trick is that "Eng.Assum.-" pages, all the info is in cell A3, It needs to copy that cell onto the "Eng.Assum.-Roll" into cell A1 and the next on A3 skipping 1 cell every time.

This is where i think it gets really trickey, Everything with "Eng.Wksheet-" needs to be somewhat the same but different. There is info in cells A4 to N4 and down, the code needs to test for a blank spot in cell D4(then D5,D6, ect), when it is detected, the code all above info are copied to "Eng.Wksheet-Roll".
Until all worksheets are copyied over.

Thanks for bearing with me on theis one. It would be helpful for copy/paste code, due to the fact i know not very much vb programming, Thanks!



Posted by Kristen on July 01, 2001 9:53 PM

You can find out all this from the VBA help files.

try looking up info on:
For... Next
Offset

As to your copy paste request:

Range("A1").copy (range("B2"))
where A1 is the source and B2 is the destination.