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

Closed
Honey85209 Posts 7 Registration date Thursday December 26, 2013 Status Member Last seen January 5, 2014 - Dec 26, 2013 at 09:55 AM
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jan 5, 2014 at 11:37 PM
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

venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Dec 28, 2013 at 12:10 AM
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
0
Honey85209 Posts 7 Registration date Thursday December 26, 2013 Status Member Last seen January 5, 2014
Dec 28, 2013 at 10:21 AM
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.
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Dec 28, 2013 at 09:25 PM
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
0
Honey85209 Posts 7 Registration date Thursday December 26, 2013 Status Member Last seen January 5, 2014
Dec 29, 2013 at 09:25 AM
Hi Venkat,,

I am sorry Its not working...

Data is not pasting into the destination file
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Dec 29, 2013 at 10:16 PM
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.
0
Honey85209 Posts 7 Registration date Thursday December 26, 2013 Status Member Last seen January 5, 2014
Jan 5, 2014 at 09:56 AM
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
0

Didn't find the answer you are looking for?

Ask a question
Honey85209 Posts 7 Registration date Thursday December 26, 2013 Status Member Last seen January 5, 2014
Dec 31, 2013 at 10:52 AM
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.
0
Honey85209 Posts 7 Registration date Thursday December 26, 2013 Status Member Last seen January 5, 2014
Dec 31, 2013 at 10:53 AM
Hii............

Advance Happy New Year.........................
0
venkat1926 Posts 1864 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 810
Jan 5, 2014 at 11:37 PM
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.
0