#1
|
|||
|
|||
How to Loop through all open Workbboks
Hi,
I'm trying to loop through all open workbooks excluding the one I'm working in as the master workbook and want to copy the first two cells (A1:A2) in each workbook in each sheet, back to the workbook that I'm working in. Here is the code I have so far, but I keep getting a runtime error '438': Sub Macro1() ' ' Macro1 Macro ' Dim wb As Workbook Dim ws As Worksheet Dim myrange As Range Dim i As Long For Each wb In Application.Workbooks If wb.Name <> "Book1.xlsx" Then For Each ws In wb.Sheets() For i = 3 To i + 2 ws.Range("A1:A2").Copy Destination:=Windows("Book1").Sheets("Sheet1").Ran ge(Cells(i, 1), Cells((i + 1), 1)).String Next i Next ws End If Next wb End Sub |
#2
|
||||
|
||||
Please ALWAYS wrap code with code tags ( Edit- select code - click #).Thx
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#3
|
|||
|
|||
Where is your code located ?
Are you sure "Book1.xlsx" is right ? Does something like this work Code:
Sub Macro1() Dim wb As Workbook, ws As Worksheet, i As Long 'cycle through all open workbooks For Each wb In Application.Workbooks 'check if it's thisworkbook <~~ ie: the one holding this code If wb.Name <> ThisWorkbook.Name Then 'when it's not thisworkbook With wb 'cycle through the sheets in wb For Each ws In wb.Sheets 'determine where to write data in thisworkbook i = ThisWorkbook.Sheets("Sheet1").Range("A" & Rows.Count).End(xlUp).Offset(1).Row 'first write is to start at row 3 If i < 3 Then i = 3 'copy values to thisworkbook from ws ThisWorkbook.Sheets("Sheet1").Cells(i, 1).Resize(2).Value = ws.Range("A1:A2").Value Next ws End With End If Next wb End Sub |
#4
|
|||
|
|||
Hi,
@Pecoflyer, yeah sorry I didn't know I had to wrap the code. I will do that next time. @No Sparks, thanks for your reply. Yes my code is located in Book.xlsx. I haven't tried your code yet but I will let you know if it works as soon as I try and run it. Also, just a quick question. I'm new to VBA and want to know when to use the "With" command. |
#5
|
|||
|
|||
Quote:
Code can only be saved in an .xlsm (macro enabled) file. Quote:
A quick Google search for "VBA when to use with" returned this as one of the top items. Last edited by NoSparks; 09-09-2018 at 06:05 AM. Reason: spelling |
#6
|
||||
|
||||
Quote:
Now that you do know, could you please add them in your first post ? Thanks
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#7
|
|||
|
|||
Hey,
Thanks so much, the code worked brilliantly. Also I hate to ask but how do I edit my original post to include code formatting? I appreciate your help and the tips. |
#8
|
||||
|
||||
Click the "Edit button then " Go advanced". You will find the # button there.
It also appears when you create a new post. Thanks
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#9
|
|||
|
|||
Quote:
I just tried following the instructions Pecoflyer is giving you and while I could edit my post at #5 (posted yesterday) there is no Edit button at post #3 (posted two days ago) so I can't edit that one. I've never started a topic so don't know if the situation is different for you. Perhaps one of the moderators will elaborate on what the parameters are that allow editing. |
#10
|
|||
|
|||
@NoSparks
Hey, no worries. I actually tried looking up the FAQ's to see if I could edit it, but I think you only have a limited amount of time to edit it until it becomes permanent. Again, thanks for all your help. |
#11
|
||||
|
||||
To be honest I never tried it as a normal member. Perhaps as Mod I have more options.
I'll check this with admin. Thanks for letting me know
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#12
|
||||
|
||||
After checking, Admin confirmed the Edit possibility only lasts 24hr to prevent spamming
__________________
Did you know you can thank someone who helped you? Click on the tiny scale in the right upper hand corner of your helper's post |
#13
|
|||
|
|||
Quote:
No worries, thanks for checking that up. I'll make sure to remember that in the future. |
Tags |
copy, for each loop, for loop |
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Do Loop bug | donlincolnmsof | Word VBA | 11 | 09-07-2017 10:07 PM |
Macro Loop Help | Twizzle008 | Word VBA | 15 | 09-18-2015 03:20 PM |
What's wrong with my loop? | Irrma | Word VBA | 2 | 06-17-2014 06:25 AM |
How to a For loop in VBA | Jennifer Murphy | Word VBA | 1 | 01-29-2013 03:30 AM |
Continuous Loop | cksm4 | Word | 6 | 01-06-2011 09:03 PM |