Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through list of workbooks to open & sheets to move
I've just taken over the task of putting together a monthly reporting package
that combines about 80 separate workbooks into one. I want to automate this incredibly boring task and was hoping that I could get some help here. From what I've seen so far, you all are amazing! Here's basically what I want my VBA code to do: 1. From my main workbook (which will be open), turn off automatic calculation and then go to the Table of Contents sheet and delete the sheets marked with an X in column F. There will be blank cells in this column as well as X's. The end of the list is indicated by "End". 2. Go to cell G6 and open the file that I have listed there. Select the active sheet, change the name to the value in cell E6, and then copy it into my main workbook after the sheet listed in cell E5. 3. Close the file that I just copied the sheet from, and move on to the next one listed in cell G7. If G7 is blank (and does not equal "End"), go to cell G8 and open the file listed there. Stop when it reaches "End". Any assistance would be greatly appreciated! Beverly |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through list of workbooks to open & sheets to move
The code below is close to what you need. You mention a main worksheett and
a table of contents but don't specifically say what the name of these sheets are. Also where you delete worksheets you don't specify the starting row but when you are copyying the worksheets you specify you sttart in row 6. Modify the code as necessary, I will help if you need more help. You need to specifically name the table of contents sheet because when yoi uopen a workbook and copy sheets the active worksheet gets changed. Sub update_workbook() Set main = ThisWorkbook.ActiveSheet main.EnableCalculation = False Set TOC = ThisWorkbook.Sheets("Table of Contents") RowCount = 1 Application.DisplayAlerts = False With TOC Do While .Range("G" & RowCount) < "End" If .Range("F" & RowCount) = "X" Then Sheets(.Range("F" & RowCount)).Delete End If RowCount = RowCount + 1 Loop End With RowCount = 6 With main Do While .Range("G" & RowCount) < "End" If .Range("G" & RowCount) < "" Then Workbooks.Open Filename:=.Range("G" & RowCount) Set openbk = ActiveWorkbook openbk.ActiveSheet.Copy _ after:=.Range("E" & (RowCount - 1)) ActiveSheet.Name = .Range("E" & RowCount) openbk.Close savechanges:=False End If RowCount = RowCount + 1 Loop End With main.EnableCalculation = True Application.DisplayAlerts = True End Sub "Beverly" wrote: I've just taken over the task of putting together a monthly reporting package that combines about 80 separate workbooks into one. I want to automate this incredibly boring task and was hoping that I could get some help here. From what I've seen so far, you all are amazing! Here's basically what I want my VBA code to do: 1. From my main workbook (which will be open), turn off automatic calculation and then go to the Table of Contents sheet and delete the sheets marked with an X in column F. There will be blank cells in this column as well as X's. The end of the list is indicated by "End". 2. Go to cell G6 and open the file that I have listed there. Select the active sheet, change the name to the value in cell E6, and then copy it into my main workbook after the sheet listed in cell E5. 3. Close the file that I just copied the sheet from, and move on to the next one listed in cell G7. If G7 is blank (and does not equal "End"), go to cell G8 and open the file listed there. Stop when it reaches "End". Any assistance would be greatly appreciated! Beverly |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through list of workbooks to open & sheets to move
This is defintely close to what I need. Thank you so much! I made some
modifications and am now testing the code, but I am running into a "type mismatch" error on this line: Sheets(.Range("F" & RowCount)).Delete I tried putting the sheet name into a variable and using Sheets(wsname).Delete, Sheets("wsname").Delete, Sheets("'" & wsname & "'').Delete, setting the wsname to "'" & .Range("F" & RowCount)).Value & "'", but nothing works. Any ideas? As I am stepping through the macro and watching the wsname variable change, it looks correct (that is, it shows as "Summ_fct_bgt" when it hits my first sheet to be deleted)... Beverly "Joel" wrote: The code below is close to what you need. You mention a main worksheett and a table of contents but don't specifically say what the name of these sheets are. Also where you delete worksheets you don't specify the starting row but when you are copyying the worksheets you specify you sttart in row 6. Modify the code as necessary, I will help if you need more help. You need to specifically name the table of contents sheet because when yoi uopen a workbook and copy sheets the active worksheet gets changed. Sub update_workbook() Set main = ThisWorkbook.ActiveSheet main.EnableCalculation = False Set TOC = ThisWorkbook.Sheets("Table of Contents") RowCount = 1 Application.DisplayAlerts = False With TOC Do While .Range("G" & RowCount) < "End" If .Range("F" & RowCount) = "X" Then Sheets(.Range("F" & RowCount)).Delete End If RowCount = RowCount + 1 Loop End With RowCount = 6 With main Do While .Range("G" & RowCount) < "End" If .Range("G" & RowCount) < "" Then Workbooks.Open Filename:=.Range("G" & RowCount) Set openbk = ActiveWorkbook openbk.ActiveSheet.Copy _ after:=.Range("E" & (RowCount - 1)) ActiveSheet.Name = .Range("E" & RowCount) openbk.Close savechanges:=False End If RowCount = RowCount + 1 Loop End With main.EnableCalculation = True Application.DisplayAlerts = True End Sub "Beverly" wrote: I've just taken over the task of putting together a monthly reporting package that combines about 80 separate workbooks into one. I want to automate this incredibly boring task and was hoping that I could get some help here. From what I've seen so far, you all are amazing! Here's basically what I want my VBA code to do: 1. From my main workbook (which will be open), turn off automatic calculation and then go to the Table of Contents sheet and delete the sheets marked with an X in column F. There will be blank cells in this column as well as X's. The end of the list is indicated by "End". 2. Go to cell G6 and open the file that I have listed there. Select the active sheet, change the name to the value in cell E6, and then copy it into my main workbook after the sheet listed in cell E5. 3. Close the file that I just copied the sheet from, and move on to the next one listed in cell G7. If G7 is blank (and does not equal "End"), go to cell G8 and open the file listed there. Stop when it reaches "End". Any assistance would be greatly appreciated! Beverly |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through list of workbooks to open & sheets to move
Ok, I searched on "type mismatch" & "sheet" and found one of your older
posts, Joel, and made a minor adjustment and now it's working. Yay!!! Thank you again!! Beverly "Beverly" wrote: This is defintely close to what I need. Thank you so much! I made some modifications and am now testing the code, but I am running into a "type mismatch" error on this line: Sheets(.Range("F" & RowCount)).Delete I tried putting the sheet name into a variable and using Sheets(wsname).Delete, Sheets("wsname").Delete, Sheets("'" & wsname & "'').Delete, setting the wsname to "'" & .Range("F" & RowCount)).Value & "'", but nothing works. Any ideas? As I am stepping through the macro and watching the wsname variable change, it looks correct (that is, it shows as "Summ_fct_bgt" when it hits my first sheet to be deleted)... Beverly "Joel" wrote: The code below is close to what you need. You mention a main worksheett and a table of contents but don't specifically say what the name of these sheets are. Also where you delete worksheets you don't specify the starting row but when you are copyying the worksheets you specify you sttart in row 6. Modify the code as necessary, I will help if you need more help. You need to specifically name the table of contents sheet because when yoi uopen a workbook and copy sheets the active worksheet gets changed. Sub update_workbook() Set main = ThisWorkbook.ActiveSheet main.EnableCalculation = False Set TOC = ThisWorkbook.Sheets("Table of Contents") RowCount = 1 Application.DisplayAlerts = False With TOC Do While .Range("G" & RowCount) < "End" If .Range("F" & RowCount) = "X" Then Sheets(.Range("F" & RowCount)).Delete End If RowCount = RowCount + 1 Loop End With RowCount = 6 With main Do While .Range("G" & RowCount) < "End" If .Range("G" & RowCount) < "" Then Workbooks.Open Filename:=.Range("G" & RowCount) Set openbk = ActiveWorkbook openbk.ActiveSheet.Copy _ after:=.Range("E" & (RowCount - 1)) ActiveSheet.Name = .Range("E" & RowCount) openbk.Close savechanges:=False End If RowCount = RowCount + 1 Loop End With main.EnableCalculation = True Application.DisplayAlerts = True End Sub "Beverly" wrote: I've just taken over the task of putting together a monthly reporting package that combines about 80 separate workbooks into one. I want to automate this incredibly boring task and was hoping that I could get some help here. From what I've seen so far, you all are amazing! Here's basically what I want my VBA code to do: 1. From my main workbook (which will be open), turn off automatic calculation and then go to the Table of Contents sheet and delete the sheets marked with an X in column F. There will be blank cells in this column as well as X's. The end of the list is indicated by "End". 2. Go to cell G6 and open the file that I have listed there. Select the active sheet, change the name to the value in cell E6, and then copy it into my main workbook after the sheet listed in cell E5. 3. Close the file that I just copied the sheet from, and move on to the next one listed in cell G7. If G7 is blank (and does not equal "End"), go to cell G8 and open the file listed there. Stop when it reaches "End". Any assistance would be greatly appreciated! Beverly |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Looping through list of workbooks to open & sheets to move
I would make the following modification
from Sheets(.Range("F" & RowCount)).Delete to ThisWorkbook.Sheets(.Range("F" & RowCount)).Delete This would only be a problem if you have more than one workbook opend. Thisworkbook will specify a sheet in the same workbook as the macro is running would get deleted. Do your data start in row 1 and do you have any blank rows of data? Check what rowCount is when you get an error. Then verify the name in column F matches exactly one of the sheets in the workbook. "Beverly" wrote: Ok, I searched on "type mismatch" & "sheet" and found one of your older posts, Joel, and made a minor adjustment and now it's working. Yay!!! Thank you again!! Beverly "Beverly" wrote: This is defintely close to what I need. Thank you so much! I made some modifications and am now testing the code, but I am running into a "type mismatch" error on this line: Sheets(.Range("F" & RowCount)).Delete I tried putting the sheet name into a variable and using Sheets(wsname).Delete, Sheets("wsname").Delete, Sheets("'" & wsname & "'').Delete, setting the wsname to "'" & .Range("F" & RowCount)).Value & "'", but nothing works. Any ideas? As I am stepping through the macro and watching the wsname variable change, it looks correct (that is, it shows as "Summ_fct_bgt" when it hits my first sheet to be deleted)... Beverly "Joel" wrote: The code below is close to what you need. You mention a main worksheett and a table of contents but don't specifically say what the name of these sheets are. Also where you delete worksheets you don't specify the starting row but when you are copyying the worksheets you specify you sttart in row 6. Modify the code as necessary, I will help if you need more help. You need to specifically name the table of contents sheet because when yoi uopen a workbook and copy sheets the active worksheet gets changed. Sub update_workbook() Set main = ThisWorkbook.ActiveSheet main.EnableCalculation = False Set TOC = ThisWorkbook.Sheets("Table of Contents") RowCount = 1 Application.DisplayAlerts = False With TOC Do While .Range("G" & RowCount) < "End" If .Range("F" & RowCount) = "X" Then Sheets(.Range("F" & RowCount)).Delete End If RowCount = RowCount + 1 Loop End With RowCount = 6 With main Do While .Range("G" & RowCount) < "End" If .Range("G" & RowCount) < "" Then Workbooks.Open Filename:=.Range("G" & RowCount) Set openbk = ActiveWorkbook openbk.ActiveSheet.Copy _ after:=.Range("E" & (RowCount - 1)) ActiveSheet.Name = .Range("E" & RowCount) openbk.Close savechanges:=False End If RowCount = RowCount + 1 Loop End With main.EnableCalculation = True Application.DisplayAlerts = True End Sub "Beverly" wrote: I've just taken over the task of putting together a monthly reporting package that combines about 80 separate workbooks into one. I want to automate this incredibly boring task and was hoping that I could get some help here. From what I've seen so far, you all are amazing! Here's basically what I want my VBA code to do: 1. From my main workbook (which will be open), turn off automatic calculation and then go to the Table of Contents sheet and delete the sheets marked with an X in column F. There will be blank cells in this column as well as X's. The end of the list is indicated by "End". 2. Go to cell G6 and open the file that I have listed there. Select the active sheet, change the name to the value in cell E6, and then copy it into my main workbook after the sheet listed in cell E5. 3. Close the file that I just copied the sheet from, and move on to the next one listed in cell G7. If G7 is blank (and does not equal "End"), go to cell G8 and open the file listed there. Stop when it reaches "End". Any assistance would be greatly appreciated! Beverly |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to move and save all sheets to seperate workbooks | Excel Programming | |||
List of open workbooks | Excel Programming | |||
Getting list of open workbooks | Excel Programming | |||
List Open Workbooks in VBA | Excel Programming | |||
VBA code for looping through open workbooks and worksheets | Excel Programming |