How to transfer data from all the sheets and pasting it in
Closed
Honey85209
Posts
7
Registration date
Thursday 26 December 2013
Status
Member
Last seen
5 January 2014
-
26 Dec 2013 à 09:55
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 - 5 Jan 2014 à 23:37
venkat1926 Posts 1863 Registration date Sunday 14 June 2009 Status Contributor Last seen 7 August 2021 - 5 Jan 2014 à 23:37
Related:
- How to transfer data from all the sheets and pasting it in
- How to transfer ff id from facebook to google - Guide
- Transfer data from one excel worksheet to another automatically - Guide
- Google sheets right to left - Guide
- Ssh secure file transfer download - Download - Remote access
- Tmobile data check - Guide
7 responses
venkat1926
Posts
1863
Registration date
Sunday 14 June 2009
Status
Contributor
Last seen
7 August 2021
811
28 Dec 2013 à 00:10
28 Dec 2013 à 00:10
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 26 December 2013
Status
Member
Last seen
5 January 2014
28 Dec 2013 à 10:21
28 Dec 2013 à 10:21
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 14 June 2009
Status
Contributor
Last seen
7 August 2021
811
28 Dec 2013 à 21:25
28 Dec 2013 à 21:25
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 26 December 2013
Status
Member
Last seen
5 January 2014
29 Dec 2013 à 09:25
29 Dec 2013 à 09:25
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 14 June 2009
Status
Contributor
Last seen
7 August 2021
811
29 Dec 2013 à 22:16
29 Dec 2013 à 22:16
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 26 December 2013
Status
Member
Last seen
5 January 2014
5 Jan 2014 à 09:56
5 Jan 2014 à 09:56
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
Honey85209
Posts
7
Registration date
Thursday 26 December 2013
Status
Member
Last seen
5 January 2014
31 Dec 2013 à 10:52
31 Dec 2013 à 10:52
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 26 December 2013
Status
Member
Last seen
5 January 2014
31 Dec 2013 à 10:53
31 Dec 2013 à 10:53
Hii............
Advance Happy New Year.........................
Advance Happy New Year.........................
venkat1926
Posts
1863
Registration date
Sunday 14 June 2009
Status
Contributor
Last seen
7 August 2021
811
5 Jan 2014 à 23:37
5 Jan 2014 à 23:37
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.
