A Few Simple VB Questions w/ multiple workbooks

campbbri

New Member
Joined
Oct 27, 2004
Messages
36
Hi everyone. I'm building a macro that performs a lot of functions, but for one area in particular I need your help. Mostly I don't know the right syntax for this step.

The first workbook (call it "File1.xls") has only one worksheet called "Inputs". Based on a few user settings, it opens a target workbook ("File2.xls") with an unknown number of worksheets and unknown worksheet names.

I want to add these worksheets to File1.xls in the same order with the same names, copy the formatting (but not the data) to each newly created worksheet, and finally read in the number of rows and columns that are actually used in each worksheet.

I can add the correct number of worksheets using the code below, and I can read the worksheet names. Unfortunately the new worksheets get added to the left of the inputs tab, while I want them to be added to the right. Also, I can't figure out how to apply the names to the newly created worksheets. Finally, I don't know how to copy the formats over or read the number of used rows and columns in each worksheet.

Code:
Workbooks.Open FileName:="C:\Path\File2.xls", ReadOnly:=True

Windows("File1.xls").Activate

For Each Sheet In Workbooks("File1.xls").Worksheets
    Worksheets.Add
    WorkSheetName = Sheet.Name
Next Sheet

Thanks!
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
1) You can simply copy the sheets...don't need to add a new sheet and then copy onto it. Perhaps the only reason not to use the copy method is if you want to not copy code on the sheets when you make the copy.

2) Use the index of the worksheets. The first sheet is worksheet(1), the next is worksheet(2), and so on.

3) I copy them in backwards to maintain the same order

4) I don't see what you want done with the used columns and rows. What I did here was to clear the cell contents (same as delete in native Excel) - this would preserve the formatting on the sheet.

5) you will error out if any sheet has a name the same - or get a (1) thrown in to the name by Excel. If this is a danger or a problem, you will want to add a test at the beginning of the routine to check for name duplicates.


Code:
Sub test()
Dim wb As Workbook, wb2 As Workbook
Dim intCountSheets As Integer, intInputsIndex As Integer

'wb2 is where the code runs and sheets are imported to
Set wb2 = ThisWorkbook

'wb is where the sheets are copied from
Set wb = Workbooks("test.xls")

'Get worksheet index of "Inputs" - sheets will be copied to the right
intInputsIndex = wb2.Worksheets("Inputs").Index

'copy sheets
intCountSheets = wb.Worksheets.Count
For x = intCountSheets To 1 Step -1
    
    'copy sheet
    wb.Worksheets(x).Copy After:=wb2.Worksheets(intInputsIndex)
    
    'Clear contents - preserves formatting
    ActiveSheet.Cells.ClearContents

Next x

Worksheets("Inputs").Activate

End Sub

Will this help you?
 

campbbri

New Member
Joined
Oct 27, 2004
Messages
36
Alexander,

Thank you so much for your help. I really appreciate you taking the time to write out the code for me.

This gets me most of the way there, but I don't think I was desciptive enough regarding the columns and rows. I didn't want to get into the long and unecessary details, but essentially I will be replacing the deleted cells with a long formula. The formula will reproduce headers and text if they exist (by linking to the other sheet), and for numerical values it will make comparisons and calculations with yet other workbooks depending on the user settings in the "inputs" sheet.

The problem is I don't know the number of rows and columns used in the target workbook so I don't know what range to paste the formula over.

Thanks again.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,491
Messages
5,764,681
Members
425,229
Latest member
Rashid mahmood

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top