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 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jan 5, 2014 at 11:37 PM
venkat1926 Posts 1863 Registration date Sunday June 14, 2009 Status Contributor Last seen August 7, 2021 - Jan 5, 2014 at 11:37 PM
Related:
- How to transfer data from all the sheets and pasting it in
- Free fire transfer - Guide
- Transfer data from one excel worksheet to another automatically - Guide
- Tmobile data check - Guide
- How to be offline in whatsapp when data is on - Guide
- How to transfer contacts from sim to phone - Guide
7 responses
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Dec 28, 2013 at 12:10 AM
Dec 28, 2013 at 12:10 AM
the master sheet is called sheet1
try this macro and modify if necessary
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
Honey85209
Posts
7
Registration date
Thursday December 26, 2013
Status
Member
Last seen
January 5, 2014
Dec 28, 2013 at 10:21 AM
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.
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.
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Dec 28, 2013 at 09:25 PM
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
Honey85209
Posts
7
Registration date
Thursday December 26, 2013
Status
Member
Last seen
January 5, 2014
Dec 29, 2013 at 09:25 AM
Dec 29, 2013 at 09:25 AM
Hi Venkat,,
I am sorry Its not working...
Data is not pasting into the destination file
I am sorry Its not working...
Data is not pasting into the destination file
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Dec 29, 2013 at 10:16 PM
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.
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.
Honey85209
Posts
7
Registration date
Thursday December 26, 2013
Status
Member
Last seen
January 5, 2014
Jan 5, 2014 at 09:56 AM
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
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
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
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.
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.
Honey85209
Posts
7
Registration date
Thursday December 26, 2013
Status
Member
Last seen
January 5, 2014
Dec 31, 2013 at 10:53 AM
Dec 31, 2013 at 10:53 AM
Hii............
Advance Happy New Year.........................
Advance Happy New Year.........................
venkat1926
Posts
1863
Registration date
Sunday June 14, 2009
Status
Contributor
Last seen
August 7, 2021
811
Jan 5, 2014 at 11:37 PM
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
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.
in vba try this
'http://stackoverflow.com/questions/11152870/macro-open-all-files-in-a-folder
Sub OpenFiles()I have NOT tested because it is cumbersome to open all the files in a folder, I have to change the folder eets
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
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.
