Re: Macro to use correct workbook
Well it seems to be working now. Thanks everyone for your imput and patience. Have a good weekend now
Re: Macro to use correct workbook
Well it seems to be working now. Thanks everyone for your imput and patience. Have a good weekend now
Re: Macro to use correct workbook
Im probably being real slow here? So whats the way around it? Ive tried using activeworkbook, but it doesnt support range. The loop doesnt seem to actually move through workbooks.
Is there no other way?
Re: Macro to use correct workbook
That would work, except that I have no way of telling what workbooks will be open, nor what the names of the sheets will be. Some may have Sheet 1, some may not. For example, I have one that I need open with worksheet named Area 1. So i get the good old subscript out of range.
Re: Macro to use correct workbook
I was really trying to avoid using distinguishing values or anything. But i try to use it and it didn't work. The code i used was
For Each wkb In Application.Workbooks
If Range("E9").Value = "a" Then
strOrderWkbID = wkb.Name
Exit For
End If
Next wkb
Windows(strOrderWkbID).Activate
However, when I do this it goes through the loop the number of times that I have workbooks. Eg 4 workbooks open, goes though the loop 4 times. But it keeps checking the same workbook. Not sure why. Any other way to do it.??
Me again. The code for that other question I had worked great, silly me just realised i solved a problem i didnt have.
My problem is. I have a macro ---> Mname
I have work books. (All open simultaneously)
Iwant.xls
Iuse.xls
NotUsed.xls
Notused2.xls
Personal.xls
Now Mname is stored in Personal
I need Mname to select Iwant without referring to it directly as Mname is used with many workbooks, and the name changes. I kind of asked this question before. But badly.
I have tried going through all workbooks, but i dont know how to make Iwant be distinguishable from NotUsed.
Thanks anyone who can help
Re: In Str with numeric as a string
I will try it thanks
Hi, I was wondering what the proper syntax for testing a variable on if the first letter is a number or not. However, the variable is stored as a string.
Eg. I want it to return.
True if strNme = "612504"
False if strNme = "Not a number"
i thought the syntax was somewhere along the lines of.
if Instr(strNme, Left, 1) = 1 or 2 etc then
Blah
end if
Thanks guys, Its pretty simple, but i cant remember
Re: Multiple Workbooks using one Macro
The button is in the order form, and copies summarised information from itself to the summary workbook. Yeah, I got a lot of fiddling around to do. Thanks for your help, i should be able to get it to work from here, work as far as i want it too anyway.
Thanks a lot
Re: Multiple Workbooks using one Macro
Looks like it might work, unless i have more than one other workbook open. I was going to use Windows(nme).activatenext . Ill give it a go, thanks a lot
Re: Multiple Workbooks using one Macro
Its meant to be run from a button, but while im testing it I've been running it through the module editor itself. Uusally in debug mode to catch it at where it never picks up the name. Its actually meant for someone who doesnt use excel much at all and she just needs to press a button for it all to happen.
Re: Multiple Workbooks using one Macro
Ok, yeah, im not the best at explaining myself am u :).
Unfortunately my macro currently is not very efficient, with some hardcoding and redundant coding. But this is generally meant to be a quick fix for a problem i didnt need it to be coded well. Though its hard now.
Sub Newest()
'
' Switching Sheets
Dim smry As String
Dim nme As String
smry = Range("E10").Value
nme = smry + ".xls"
Windows(nme).Activate
Sheets("Area 1").Select
Windows("ALLORA ROSE GARDEN.xls").Activate 'I want this to be dynamic
' Order Details
Range("G22").Select
Selection.Copy
Windows(nme).Activate
ActiveWindow.SmallScroll ToRight:=-6
Range("A7").Select
While ActiveCell.Offset(0, 0).Range("A1").FormulaR1C1 <> ""
ActiveCell.Offset(1, 0).Range("A1").Select
Wend
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Range("A1").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
ActiveCell.Offset(0, -2).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows(nme).Activate
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
Windows(nme).Activate
Windows("ALLORA ROSE GARDEN.xls").Activate
ActiveCell.Offset(-3, -3).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows(nme).Activate
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
ActiveCell.Offset(0, 2).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows(nme).Activate
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
ActiveCell.Offset(-9, -2).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Select
Application.CutCopyMode = False
Selection.Copy
Windows(nme).Activate
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
Windows(nme).Activate
Windows("ALLORA ROSE GARDEN.xls").Activate
Windows(nme).Activate
Windows("ALLORA ROSE GARDEN.xls").Activate
ActiveCell.Offset(12, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows(nme).Activate
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
Windows(nme).Activate
Windows("ALLORA ROSE GARDEN.xls").Activate
ActiveCell.Offset(2, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows(nme).Activate
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
ActiveCell.Offset(-2, 2).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows(nme).Activate
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.ColumnWidth = 6.25
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.ColumnWidth = 5.88
ActiveCell.Offset(0, 2).Range("A1").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
ActiveCell.Offset(5, 2).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows(nme).Activate
ActiveSheet.Paste
ActiveCell.Offset(0, 2).Range("A1").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
ActiveSheet.Paste
' Moving Orders
Count = 0
While Count < 6
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.Copy
Windows(nme).Activate
ActiveSheet.Paste
ActiveCell.Offset(0, 2).Range("A1").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
Count = Count + 1
Wend
' Switching Sheets
Windows(nme).Activate
Sheets("Area 2").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
' Order Details
Range("G22").Select
Selection.Copy
Windows(nme).Activate
Range("A7").Select
While ActiveCell.Offset(0, 0).Range("A1").FormulaR1C1 <> ""
ActiveCell.Offset(1, 0).Range("A1").Select
Wend
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Range("A1").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
ActiveCell.Offset(0, -2).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows(nme).Activate
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
Windows(nme).Activate
Windows("ALLORA ROSE GARDEN.xls").Activate
ActiveCell.Offset(-3, -3).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows(nme).Activate
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
ActiveCell.Offset(0, 2).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows(nme).Activate
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
ActiveCell.Offset(-9, -2).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "ALLORA ROSE GARDEN"
Selection.Copy
Windows(nme).Activate
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
Windows(nme).Activate
Windows("ALLORA ROSE GARDEN.xls").Activate
Windows(nme).Activate
Windows("ALLORA ROSE GARDEN.xls").Activate
ActiveCell.Offset(12, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows(nme).Activate
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
Windows(nme).Activate
Windows("ALLORA ROSE GARDEN.xls").Activate
ActiveCell.Offset(2, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows(nme).Activate
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
ActiveCell.Offset(-2, 2).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows(nme).Activate
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.ColumnWidth = 6.25
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.ColumnWidth = 5.88
ActiveCell.Offset(0, 2).Range("A1").Select
' Moving Orders
Count = 0
Windows("ALLORA ROSE GARDEN.xls").Activate
Range("F33").Select
While Count < 10
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.Copy
Windows(nme).Activate
ActiveSheet.Paste
ActiveCell.Offset(0, 2).Range("A1").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
Count = Count + 1
Wend
' Switching Sheets
Windows(nme).Activate
Sheets("Area 3").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
' Order Details
Range("G22").Select
Selection.Copy
Windows(nme).Activate
Range("A7").Select
While ActiveCell.Offset(0, 0).Range("A1").FormulaR1C1 <> ""
ActiveCell.Offset(1, 0).Range("A1").Select
Wend
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Range("A1").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
ActiveCell.Offset(0, -2).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows(nme).Activate
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
Windows(nme).Activate
Windows("ALLORA ROSE GARDEN.xls").Activate
ActiveCell.Offset(-3, -3).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows(nme).Activate
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
ActiveCell.Offset(0, 2).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows(nme).Activate
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
ActiveCell.Offset(-9, -2).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
ActiveCell.Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = "ALLORA ROSE GARDEN"
Selection.Copy
Windows(nme).Activate
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
Windows(nme).Activate
Windows("ALLORA ROSE GARDEN.xls").Activate
Windows(nme).Activate
Windows("ALLORA ROSE GARDEN.xls").Activate
ActiveCell.Offset(12, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows(nme).Activate
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
Windows(nme).Activate
Windows("ALLORA ROSE GARDEN.xls").Activate
ActiveCell.Offset(2, 0).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows(nme).Activate
Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveCell.Offset(0, 1).Range("A1").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
ActiveCell.Offset(-2, 2).Range("A1").Select
Application.CutCopyMode = False
Selection.Copy
Windows(nme).Activate
ActiveSheet.Paste
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.ColumnWidth = 6.25
ActiveCell.Offset(0, 1).Columns("A:A").EntireColumn.ColumnWidth = 5.88
ActiveCell.Offset(0, 2).Range("A1").Select
' Moving Orders
Windows("ALLORA ROSE GARDEN.xls").Activate
Range("F43").Select
Count = 0
While Count < 5
Application.CutCopyMode = False
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.Copy
Windows(nme).Activate
ActiveSheet.Paste
ActiveCell.Offset(0, 2).Range("A1").Select
Windows("ALLORA ROSE GARDEN.xls").Activate
Count = Count + 1
Wend
End Sub
Display More
Re: Multiple Workbooks using one Macro
I just tried using ActiveWorkbook and it activates the wrong workbook, it activates the summary workbook instead of the order workbook, still can't seem to figure it out. If anyone knows how to make global variables between multiple projects and workbooks that would solve it. Thanks
Re: Multiple Workbooks using one Macro
Thanks everyone. I still havnt gotten around to having a look at it as it was the weekend Hope yous had a good one. I'll post how I go.
Re: Multiple Workbooks using one Macro
I will have a look at it. However, the code doesn't need to be in a loop because I'm only running the code once for one work book. It depends on the work book.
Scenario:
I have suppliers who make orders. Each supplier has a seperate workbook. Each order for each supplier goes onto a summary work book. I want it to when they click on the button, that it runs a macro (that is universal) that copies the summarised data to the summary order workbook. So I need to run macros as I make orders.
Re: Find unique values where criteria is met
Sounds like it would be done a lot easier with a database program and exporting data than messing around with code. Thats just my opinion, wish I could help more.
I have around 40-50 odd workbooks and I need them to use one macro. Now that isn't that much of a problem using the personal macro workbook (which i dont like that much).
Currently when I need to update the macro i need to do it 40-50 times because they are all using seperate macros. I wish to stop this, my problem is that they refer to their own workbook to activate and I dont know how to do this dynamicly.
ThisWorkBook.name property won't work because the macro is in the "Personal.xls" macro. I was trying to use a global variable but declaring a variable with 'Public' in front caused errors unlike the MS help said it would. I know there is probably a quick easy answer to this. I did search, but couldnt find exactly what i was looking for.