You need to attach an example workbook.
Posts by royUK
-
-
Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post
All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.
How to use code tags
Note: no apostrophe in the tags, just used for demonstration here.
['code]
your code goes between these tags
['/code]
Or, just highlight all of the code and press the <> in the post menu above button to add the code tags.
Thanks.
-
Is this homework?
I would call this re-inventing the wheel. Built in Excel Functions can be used and will be more efficient than any UDF.
-
Can you attach an example of the workbook. I don't understand how the formulas relating to the frst table expand in the second table, unless you have entered then manually into the column first.
-
Pleased to help.
-
I'll take a look for you
-
The code should be saving as the worksheet's name
Display MoreCode- Sub CreateWorkbookFromSheets()
- Dim ws As Worksheet
- Dim wb As Workbook
- Dim path As String, nm As String
- With Application
- .ScreenUpdating = False
- .DisplayAlerts = False
- path = "C:\VBA\SMA4\Control Files SMA4\Files with orders"
- For Each ws In ThisWorkbook.Worksheets
- If Not ws.Name Like "*T" And ws.Name <> "Sheet1" Then
- nm = ws.Name
- ws.Copy
- ActiveSheet.Name = nm
- ''/// this line saves the new workbook with the sheet name
- ActiveWorkbook.SaveAs path & "\" & nm & ".xlsx", FileFormat:=51
- ''/// this line closes the new workbook and saves it
- ' ActiveWorkbook.Close True
- End If
- Next ws
- .ScreenUpdating = True
- .DisplayAlerts = True
- End With
- End Sub
-
Do you mean that you want to have a MultiPage control in the userform booking to replace having two userforms.
-
Your code seems to rely on named ranges, but there aren't any in the example workbook.
-
Just because code is on YouTube doesn't mean it's good code. There's so many different styles of code it's difficult to follow.
I can't see how to tell if a computer is booked or not, nor how you would. How do you want to view the status?
I can't see why you need 3 UserForms. If you want to view a specific record from the Booking form I would have the data loaded into TextBoxes by clicking the record in the ListBox.
I think the only way to get help is to tackle this one part at a time.
-
I will see it
-
I think it's best to start a new question.
I would need a complete explanation of what you expect to do.
-
You can make Word and Excel read aloud.
-
What do you mean by hidden rows? AutoFilter hides the unwanted rows, so AutoFilter at the end of the code will unhide the rows.
Are they within the data that is being filtered?
Are you copying the cells and pasting special on the same sheet?
Display MoreCode- Sub PopulatePartsList()
- ''/// copy data to another sheet based on first 3 letters of entry
- Dim rData As Range
- ' On Error GoTo err_quit
- Application.ScreenUpdating = 0
- With Sheets("Incoming")
- ''/// check if AutoFilter is on, if not switch on
- If Not .AutoFilterMode Then .Range("A1").AutoFilter
- ''/// add range to filter
- Set rData = .Range("V40:AF417")
- ''/// autofilter data
- rData.AutoFilter Field:=4, Criteria1:="<>"
- ''/// copy filtered data to destination sheet
- .AutoFilter.Range.Resize(, rData.Columns.Count).Copy
- .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 2).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
- :=False, Transpose:=False 'paste values 1 row and 2 columns over from the last last non-blank cell
- End With
- err_quit:
- Application.ScreenUpdating = 1
- End Sub
-
Why not just use Excel?
-
Welcome to the Forum. Please read the Forum Rules to understand how the Forum works and why I have added Code Tags to your post
All VBA code posted in the forum must be wrapped in code tags, which you omitted, including single-line code snippets.Be sure to use them in future posts.
How to use code tags
Note: no apostrophe in the tags, just used for demonstration here.
['code]
your code goes between these tags
['/code]
Or, just highlight all of the code and press the <> in the post menu above button to add the code tags.
Thanks.
-
Why do you need the CheckBoxes?
-
If you use ws.Move it will automatically move that sheet to a new workbook, which would simply need renaming.
Display MoreCode- Option Explicit
- Sub CreateWorkbookFromSheets()
- Dim ws As Worksheet
- Dim wb As Workbook
- Dim path As String
- With Application
- .ScreenUpdating = False
- .DisplayAlerts = False
- path = "C:\VBA\SMA4\Control Files SMA4\Files with orders"
- For Each ws In ThisWorkbook.Worksheets
- If Not ws.Name Like "*T" And ws.Name <> "Sheet1" Then
- ws.Move
- ActiveWorkbook.SaveAs path & "\" & ws.Name
- ActiveWorkbook.Close True
- End If
- Next ws
- .ScreenUpdating = True
- .DisplayAlerts = True
- End With
- End Sub
-
It's not clear from your example which table you are referring to.
-
Where are you entering the year?