Posts by royUK

royUK · 2020-09-29T08:11:07+01:00

Try this, I haven't tested it

Code

Option Explicit
Dim wsActiveListing As Worksheet
Dim LastRow As Long
Dim collection_UniqueList As Collection
'unique list is for automation to be applied to any column
Sub MainProgram()
Dim wb As Workbook
Dim oWs As Worksheet
Dim instance As Long, iX As Integer
Const Output_Folder_Path As String = "C:\Users\NoelleB\Desktop\Report112\"
For iX = 1 To ThisWorkbook.Worksheets.Count
If iX = 1 Then
''/// add new workbook only once
Set wb = Workbooks.Add
Application.SheetsInNewWorkbook = ThisWorkbook.Worksheets.Count
End If
For Each oWs In ThisWorkbook.Worksheets
Set wsActiveListing = oWs
With wsActiveListing
LastRow = .Cells(Rows.Count, "A").End(xlUp).Row
'using column A to figure out the last row number
If 2 > LastRow Then Exit Sub
Set collection_UniqueList = New Collection
Call UniqueList(collection_UniqueList)
For instance = 1 To collection_UniqueList.Count
.AutoFilterMode = False
.Range("A1:Z" & LastRow).AutoFilter .Range("G7").Column, collection_UniqueList.Item(instance)
.Range("A1").CurrentRegion.Copy wb.Worksheets(iX).Range("A1")
.AutoFilterMode = False
Next instance
End With
Next oWs
Next iX
wb.SaveAs Filename:=Output_Folder_Path & "_" & collection_UniqueList.Item(instance) & ".xlsx", FileFormat:=51
wb.Close False
Set wb = Nothing
MsgBox "Macro complete.", vbInformation
Set collection_UniqueList = Nothing
Set wsActiveListing = Nothing
End Sub

Display More

Have you read the Forum Rules and understand about "Hijacking"? I am deleteing your post in the other person's Thread.

royUK · 2020-09-29T06:24:24+01:00

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.

royUK · 2020-09-29T06:20:41+01:00

Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

Read this to understand why we ask you to do this

Attach an example workbook

royUK · 2020-09-28T16:56:57+01:00

Make sure that the macro to run is declared as Public

For the button workbook add a macro to call the other macro and assign that to the button

Code

Sub RunMacro{}
Application.Run "ConsolidateDischargeRequests.xlsm!NameOfMacroHere"
End Sub

This will work on addins, but for a workbook I should think that both workbooks need to be open

royUK · 2020-09-28T16:46:53+01:00

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 are you trying to dd a button to run code from another workbook?

royUK · 2020-09-28T13:12:21+01:00

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.

You might want to provide your solution which might help others or maybe you will get better code offered.

royUK · 2020-09-28T11:25:46+01:00

Check this, I have added code to unhide the rows in each block. Let me know if this works and I'll have a look at the next step

royUK · 2020-09-28T06:12:45+01:00

jbanks

We don't delete threads like that! Read and follow the Rules

royUK · Saturday, 7:40 am

Your code is not improved or faster by the way you write it using : instead of separating lins. All that does is make it harder to under stand what you are doing.

The code doesn't seem to do much so it should be fairly fast.

Instead of

Code

Lr1 = ws1.Range("A" & ws1.Rows.Count & "").End(xlUp).Row

Then changing the value later without using it first

Code

Let Lenf1 = Lr1 - 1

Why not simply

Code

Lr1 = ws1.Range("A" & ws1.Rows.Count & "").End(xlUp).Row-1

I can't see why you use Let either

royUK · Friday, 7:12 pm

Glad it worked.

royUK · Friday, 1:47 pm

Pleased to help

royUK · Friday, 10:59 am

I really don't see why you need VBA for this. I don't know why you have the IF statement in your code?

royUK · Friday, 9:36 am

The number is stored in A1 of the Inward sheet and the colour of the number matches the background.

The Job number is built up using formulas, you don't need VBA to do thi part.

The button code increments the number in A1 when clicked

royUK · Friday, 9:24 am

That's what I said, you cannot increment a string and that's what the entry in D7 is, the number part needs to be stored elsewhere

royUK · Friday, 8:58 am

You don't set a Range for Target in Event Code. Target refers to the selection in such code.

Try the calculate event

Code

Private Sub Worksheet_Calculate()
If Me.Range("E1").Value > 2 Then MsgBox "MI 14.10.2020 / 0900-1000 bereits ausgebucht! Bitte wählen Sie ein anderes Datum!"
If Me.Range("E2").Value > 2 Then MsgBox "FR 16.10.2020 / 1100-1200 bereits ausgebucht! Bitte wählen Sie ein anderes Datum!"
If Me.Range("E3").Value > 2 Then MsgBox "DI 20.10.2020 / 1600-1700 bereits ausgebucht! Bitte wählen Sie ein anderes Datum!"
If Me.Range("E4").Value > 2 Then MsgBox "DO 22.10.2020 / 1330-1430 bereits ausgebucht! Bitte wählen Sie ein anderes Datum!"
End Sub

royUK · Friday, 8:50 am

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.

royUK · Friday, 8:49 am

It should be reasonably fast

royUK · Friday, 8:48 am

And the string part will stay the same for each entry?

royUK · Friday, 6:38 am

The Job number is a String.

How is the string made up?

Do you want 001 to become 002, etc?

royUK · Friday, 6:37 am

Pleased to help

Posts by royUK

AutoFilter Multiple Sheets, Copy, New Book, Paste to Multiple Sheets

AutoFilter Multiple Sheets, Copy, New Book, Paste to Multiple Sheets

Formula for Conditional Formatting in Tables

Path to macro corrupted in On Action code

Path to macro corrupted in On Action code

ComboBox Value to send text to correct worksheet

Automatically hide/unhide row by calling module

Top 10 from list

Looking to make this macro Faster

Rename folders from list..

Separate individual actions in VBA Code

Generate next Job no automatically while opening

Generate next Job no automatically while opening

Generate next Job no automatically while opening

Separate individual actions in VBA Code

Separate individual actions in VBA Code

Looking to make this macro Faster

Generate next Job no automatically while opening

Generate next Job no automatically while opening

Else If Statement Issue