0 votes
in VBA by Beginner (2 points)
Dear Excel Lover,

i have an excel file which has 256 sheet with same heading i want to copy each sheet data with header please help.


Pankaj Gupta
by Beginner (40 points)
You wish to copy it to where? Are you trying to consolidate data from all sheets to one place?
by Beginner (2 points)
Yes I want consolidated data into sheet 1

1 Answer

0 votes
by Beginner (40 points)

To make a change to all 256 sheets at the same time, select the first tab, hold down shift, and select the last tab. With all desired tabs selected, you can make your headers bold, change the name of your headers, insert a new column, etc.


This code will copy data from all sheets (that start in cells A1) and consolidate to Sheet1. This may help you get started to do something similar.


Sub ConsolidateSheetData()

Sheet1.Range("A1").CurrentRegion.Offset(1, 0).ClearContents

Dim sh, v As Long
For Each sh In Sheets
    If Not sh.Name = Sheet1.Name Then
        v = Sheet1.Range("A1").CurrentRegion.Rows.Count
        sh.Range("A1").CurrentRegion.Offset(1, 0).Copy Sheet1.Range("A1").Offset(v, 0)
    End If

End Sub


by Beginner (2 points)
Dear ,

Below code not working.

by Beginner (40 points)
Is the first header for each table in A1? Did you get an error message? Does Sheet1 exist as an application name, not just as a display name? Also, how are you launching the sub, and did you put the red dots to the left to step through the sub?

This should help you get started, but you will want to make sure you understand the code. Are there more pages that need to be ignored in the loop? Do any lines need clarifying or an explanation?

Welcome to wellsr Q&A
Ask any questions you have about VBA and Python and our community will help answer them. wellsr Q&A is the standalone question and answer platform for wellsr.com. If you have a question about one of our specific tutorials, please include a link back to the tutorial.

Getting Started
VBA Cheat Sheets (On Sale Now)

Looking for something else? Hire our team directly through ourVBA Help page, instead.

For more programming tips visit the VBA Tutorials Blog and the Python Tutorials Blog.