Hi Kaushik,
Try this......!!!
Option Explicit
Private Sub CommandButton1_Click()
On Error Resume Next
Dim fso As FileSystemObject
Dim fld As Folder
Dim fil As File
Dim wkb As Workbook
Dim akb As Workbook
Dim i As Long
Dim fldpath As String
Application.FileDialog(msoFileDialogFolderPicker).Title = "Choose Folder"
Application.FileDialog(msoFileDialogFolderPicker).Show
fldpath =
Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1) & "\"
Set fso = CreateObject("scripting.filesystemobject")
Set fld = fso.GetFolder(fldpath)
Set wkb = ThisWorkbook
For Each fil In fld.Files
Workbooks.Open fil
Set akb = ActiveWorkbook
i = wkb.Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row + 1
akb.Sheets(1).Range("A1:G1").Copy wkb.Sheets(1).Cells(1, 1)
akb.Sheets(1).Range("A2:G" & akb.Sheets(1).Cells(Rows.Count,
"G").End(xlUp).Row).Copy wkb.Sheets(1).Cells(i, 1)
Next fil
On Error GoTo 0
wkb.Sheets(1).Columns.AutoFit
End Sub
On Tuesday, June 4, 2013 9:23:33 PM UTC+5:30, Kaushik wrote:
>
> Hi Team,
>
> Need your help in creating the macro doing the below:-
>
> I am having huge data in different worksheets
> I am having 10 worksheets in total
> I want to get the data copied from each worksheet and to combine in
> one worksheet and name the combined data sheet as "Combined Data"
>
> Note: The biggest challenge I am facing is there is no fix range of
> the data in each worksheet. Eg in one worksheet there is data upto 200
> rows, in another 240 rows, etc.
>
> Need your help.
>
> Thanks,
> Kaushik Savla
>
--
Are you =EXP(E:RT) or =NOT(EXP(E:RT)) in Excel? And do you wanna be? It’s
=TIME(2,DO:IT,N:OW) ! Join official Facebook page of this forum @
https://www.facebook.com/discussexcel
FORUM RULES
1) Use concise, accurate thread titles. Poor thread titles, like Please Help,
Urgent, Need Help, Formula Problem, Code Problem, and Need Advice will not get
quick attention or may not be answered.
2) Don't post a question in the thread of another member.
3) Don't post questions regarding breaking or bypassing any security measure.
4) Acknowledge the responses you receive, good or bad.
5) Jobs posting is not allowed.
6) Sharing copyrighted material and their links is not allowed.
NOTE : Don't ever post confidential data in a workbook. Forum owners and
members are not responsible for any loss.
---
You received this message because you are subscribed to the Google Groups "MS
EXCEL AND VBA MACROS" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to excel-macros+unsubscr...@googlegroups.com.
To post to this group, send email to excel-macros@googlegroups.com.
Visit this group at http://groups.google.com/group/excel-macros?hl=en.
For more options, visit https://groups.google.com/groups/opt_out.
