473,289 Members | 1,866 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,289 software developers and data experts.

How to format multiple sheets in Excel?

anoble1
245 128KB
Hello - I have made something that opens an excel document that was just exported. The workbook has 10 tabs. I wish there was someway to edit all the tabs instead of doing it tab by tab in the code. Is there a way to do that? Right now I am only able to do "Sheet1" of course.

Expand|Select|Wrap|Line Numbers
  1. Private Sub OpenAndFormatExcel()
  2.  
  3.     Dim filePath As String
  4.  
  5.     filePath = "C:\Users\Andy\Documents\Andy\Test.xlsx"
  6.  
  7.     Dim XL As Excel.Application
  8.     Dim xlBook As Excel.Workbook
  9.     Dim xlSheet As Excel.Worksheet
  10.  
  11.     Set XL = New Excel.Application
  12.  
  13.     Set xlBook = XL.Workbooks.Open(filePath)
  14.  
  15.     Set xlSheet = xlBook.Worksheets("Sheet1")
  16.  
  17.     XL.Visible = True
  18.     FreezeRow
  19.  
  20.     With xlSheet
  21.  
  22.         With .Range("A1:L1")
  23.             .HorizontalAlignment = Excel.xlCenter
  24.             .Font.Bold = True
  25.             .Columns.AutoFit
  26.             .EntireColumn.AutoFit
  27.         End With
  28.  
  29.     End With
  30.  
  31.     XL.DisplayAlerts = False
  32.  
  33.     xlBook.Save
  34.     '*** uncomment to keep open
  35.     'xlBook.Close True
  36.  
  37.     XL.DisplayAlerts = True
  38.  
  39.     Set xlSheet = Nothing
  40.     Set xlBook = Nothing
  41.     Set XL = Nothing
  42.  
  43. End Sub
  44.  
  45. Sub FreezeRow()
  46. Rows("2:2").Select
  47. ActiveWindow.FreezePanes = True
  48. End Sub
  49.  
Feb 1 '23 #1
6 8224
NeoPa
32,554 Expert Mod 16PB
Have you tried recording doing what you want in Excel first? You can Select multiple Worksheets in Excel, as you may know, so then you add your formatting. When you stop recording the macro you have some example code that is usually very poor code, TBF, but nevertheless gives you hints at the steps you will need in your proper code. Does that make sense?

Separately, when converting from Excel code into something that works from within Access you need to ensure any defaults that are used by Excel are specified explicitly when running from Access. Check out Application Automation.
Feb 1 '23 #2
tmudgal16
10 Byte
I want to format all the worksheets like Sheet1. Here's what I'll do:

1) Group the worksheets by selecting Sheet1, holding down the Shift key and selecting Sheet4. This will group all the worksheets together:
Notice in the header it now says "Group":

2) Go to Home > Editing > Fill > Across Worksheets…
3) A dialog box will pop up. Select "Formats" and click OK:
Feb 6 '23 #3
NeoPa
32,554 Expert Mod 16PB
While that last post doesn't deal with the question directly, as it isn't code-related at all, it is valid as it does give an example of how to duplicate formatting across Worksheets that can be captured by the macro recorder.

It's not exactly the approach I had in mind as applying specific format commands to a selection of Worksheets makes more sense to me and I suspect it would be easier to control exactly which formats are included across the selected Worksheets.
Feb 6 '23 #4
SioSio
272 256MB
At the beginning of your code, insert the code to select all sheets.
Expand|Select|Wrap|Line Numbers
  1.     Dim selSht As Variant
  2.     Dim i As Long
  3.     ReDim selSht(1 To Sheets.Count)
  4.     For i = 1 To Sheets.Count
  5.         selSht(i) = Sheets(i).Name
  6.     Next i
  7.     Sheets(selSht).Select
Feb 7 '23 #5
Varsha1285
16 16bit
1.Ctrl + Click each sheet tab at the bottom of your worksheet (selected sheets will turn white).

2. While selected, any formatting changes you make will happen in all of the selected sheets.

3. Double-click each tab when you are done to un-select them.
Feb 8 '23 #6
NeoPa
32,554 Expert Mod 16PB
Hi Varsha.

I won't delete your post as I assume it was intended to help - and there may be some that read it later who find it helpful.

However, it really is necessary to read the question before posting an answer. The question was clear about wanting to edit multiple tabs (Worksheets) using code (VBA).

No matter how good the intention, it's frustrating to ask questions and see an answer - only to find it has nothing really to do with your question (Like this response to you now, unfortunately).

@ANoble.
Perhaps you could update the thread with where you are with it now. Hopefully my post #2 led you to what you needed.
Feb 8 '23 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

1
by: Carl Corcoran | last post by:
I'm developing a website for work that exports some reports to excel. I am using the... Response.AddHeader "Content-Disposition","attachment;filename=report.xls" Response.ContentType =...
0
by: acarrazco | last post by:
Hello, I am totaly new to VBA and I'm trying to modify a macro that was given to me but it doesn't seem to be working. I'm trying to extract data from three excel spreadsheets, put it into a combined...
4
by: Harshe | last post by:
hello all, I am trying to code, but i am just stuck after importing one sheet. so here is the gist of what i need help with. In a workbook at the start of the year (january) i will have 4...
1
by: CF FAN | last post by:
Can Report Builder Export Excel Files with multiple Sheets How can I create a report that it can export in excel by multiple
0
by: Supermansteel | last post by:
I have created an Form and Input sheet to be able to input Pricing informtion for 1st and 2nd Mortgages. However, I have recently come accross that there are about 13 states that have different...
0
by: puneetmittal33 | last post by:
Hi, I am facing the following issues and hoping that someone out there would have a resolution to the issue: 1) I am currently working on a web application which handles the payroll data for...
7
by: elham a | last post by:
I have an Excel workbook with multiple sheets (22)! that I would like to import into Access Tables for manipulation. I'd like to load them all to the same table if possible, so long as I know what...
1
by: anourmohammadi | last post by:
hi is there any way ? "Exporting Tables from Access into 1 Excel Workbook w/ Multiple Sheets" with no code .
0
by: vinodpaladi | last post by:
how to export the reports data from ms access to excel in multiple sheets
8
USTRAGNU1
by: USTRAGNU1 | last post by:
Good Day, I created a macro in Excel that works great. It formats multiple sheets exactly as expected, but when it completes it seems like I have to close out four or five worksheets before I can...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.