How to transfer data from all the sheets and pasting it in

Closed
Report
Posts
7
Registration date
Thursday December 26, 2013
Status
Member
Last seen
January 5, 2014
-
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
-
Hi All,

I need one help, I am having excel workbook, I need to copy the data which is there in each sheet and i need to paste that in a master workbook in a particular worksheet.

Please help me out

7 replies

Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
the master sheet is called sheet1

try this macro and modify if necessary

Sub test()
Dim j As Integer
For j = 2 To Worksheets.Count
With Worksheets(j)
.UsedRange.Copy
With Worksheets("sheet1")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
End With
Next j

End Sub
Posts
7
Registration date
Thursday December 26, 2013
Status
Member
Last seen
January 5, 2014

Hi Venkat,

Thank you for sending its working, But I know this code.

My requirement is not to paste the data in the same sheet.

My requirement is to copy the data from a workbook which contains data in all the sheets,

and that data need to be paste in other workbook in a particular worksheet.
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
book1 is source data and the data from all the sheets in this workbook should be copied into the sheet1 of the Book2. try this modified macro. in macro the book2 comes . change it to the name of your second workbook. keep open the second workbook also.

Sub testone()
Dim j As Integer
With ThisWorkbook
For j = 1 To .Worksheets.Count
With .Worksheets(j)
.UsedRange.Copy
With Workbooks("book2").Worksheets("sheet1")
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
End With
Next j
End With
End Sub
Posts
7
Registration date
Thursday December 26, 2013
Status
Member
Last seen
January 5, 2014

Hi Venkat,,

I am sorry Its not working...

Data is not pasting into the destination file
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
you have not said what is the error and what is the error message. I have used expression Book2 in a code statement. now in the Book2 workbook select any sheet , at the top of the sheet what do you see
vbook2
or
book2.xlsm

if latter change code statement to
With Workbooks("book2.xlsm").Worksheets("sheet1")


I hope sheet names are also ok. no spelling mistakes.

did you save the file as .xlsm. both Book1(in which the macro is embedded) and book2 are open

I checked it in my computer it works.
Posts
7
Registration date
Thursday December 26, 2013
Status
Member
Last seen
January 5, 2014

Hi Venkat

Please help me out with this code while executing this code iam getting run time 1004

This program is about it opens all the excel files in a folder and copies the data from each workbook and paste it in master workbook

But iam getting runtime error 1004 like unable to open excel file . please help me out in this

Dim mainwb As Workbook
Dim wb As Workbook
Dim i As Integer
Set mainwb = ThisWorkbook
mainwb.Activate
Sheets("sheet1").Select
Range("a2:b500").ClearContents
Set filesystemobj = CreateObject("scripting.filesystemobject")
Set folderobj = filesystemobj.getfolder("C:\Documents and Settings\suhasini\Desktop\allfiles")
For Each fileobj In folderobj.Files
If fileobj.Name <> "book3.xlsm" And (filesystemobj.getextensionname(fileobj.Path) = "xlsx" Or filesystemobj.getextensionname(fileobj.Path) = "xlsm") Then
Application.DisplayAlerts = False
Set wb = Workbooks.Open(fileobj.Path)
wb.Worksheets("sheet1").UsedRange.Select
'lastcell = Range("a2:b500").SpecialCells(xlCellTypeLastCell).Address
'Range("a2:" & lastcell).Select
Selection.Copy
mainwb.Activate
Sheets("sheet1").Select
If Range("a2").Value = "" Then
Range("a2").Select
Else
Range("a1").End(xlDown).Offset(1, 0).Select
End If
ActiveSheet.Paste
wb.Activate
wb.Save
wb.Close
End If
mainwb.Activate
mainwb.Save
Next fileobj
Application.Quit
End Sub
Posts
7
Registration date
Thursday December 26, 2013
Status
Member
Last seen
January 5, 2014

Hi Venkat,

Thank you for sending, I will with the code what you send again.

I will try it on Saturday and sunday.

Thank you for sending.
Posts
7
Registration date
Thursday December 26, 2013
Status
Member
Last seen
January 5, 2014

Hii............

Advance Happy New Year.........................
Posts
1864
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
804
your macro is vb.net I suppose and I am not familiar

in vba try this

'http://stackoverflow.com/questions/11152870/macro-open-all-files-in-a-folder
Sub OpenFiles()
Dim MyFolder As String
Dim MyFile As String
Dim j As Integer
MyFolder = "C:\Documents and Settings\suhasini\Desktop"
MyFile = Dir(MyFolder & "\*.xlsx")
Do While MyFile <> ""
Workbooks.Open Filename:=MyFolder & "\" & MyFile
With ActiveWorkbook
For j = 1 To .Worksheets.Count
.Worksheets(j).UsedRange.Copy
With thisworkbook.
.Worksheets("sheet1").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).PasteSpecial
End With
Next j
End With
ActiveWorkbook.Close savechanges:=False

MyFile = Dir
Loop

End Sub
I have NOT tested because it is cumbersome to open all the files in a folder, I have to change the folder eets

remember keep the summary file open
PARK THE FILE IN THE SMMARY WORKBOOK

THERE MAY BE BUGS IF SO EXPLAIN WHICH CODE GIVES THE BUG AND WHAT IS THE ERROR MESSAGE.