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
wellsr Q&A is the VBA and Python programming community that rewards you for learning how to code.

Getting Started
VBA Cheat Sheets (On Sale Now)

Earn free prizes for asking VBA and Python questions and for answering questions asked by others in our community.

Looking for something else? Hire our professional VBA Help, instead.

What makes us different?
Our points system rewards you with a chance for free gifts based on the quality of your questions and answers. All you have to do is post and you could get rewarded, like these members:

ParserMonster $25 Amazon Gift Card
Hightree $10 Amazon Gift Card
Thales1 $10 Amazon Gift Card
runfunke $10 Amazon Gift Card
coolag $10 Amazon Gift Card
Siew Hun $10 Amazon Gift Card

So, why don't you join us? It really is an encouraging way to motivate members in our VBA and Python community.


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