excel learner
asked on
Macro to pull data from various sheets
Dear experts,
I have a file which has the below sheets with data sets in the below columns.
The data sets in each sheet is in different column.
Sheet Name Data set 1 Data set 2 Data set 3 Data set 4 Data set 5 Data set 6
Os_sheet 1 A3:A1000 I3:I1000 U3:U1000 J3:J1000 K3:K1000 AH3:AH1000
ir_sheet 2 D10:D1000 F10:F1000 C10:C1000 H10:H1000 I10:I1000 AL10:AL000
IR_Sheet 3 D19:D1000 F19:F1000 C19:C1000 H19:H1000 I19:I1000 AM19:AM000
Op_Sheet 4 D26:D1000 F26:F1000 B26:B1000 E26:E1000 G26:G1000 BB26:BB1000
Need a macro which will pull the data from the various sheets and column ranges in to new sheet. This new sheet should be created as part of the macro
The data pulled by the above macro should be copied one below the other.
Thank you
I have a file which has the below sheets with data sets in the below columns.
The data sets in each sheet is in different column.
Sheet Name Data set 1 Data set 2 Data set 3 Data set 4 Data set 5 Data set 6
Os_sheet 1 A3:A1000 I3:I1000 U3:U1000 J3:J1000 K3:K1000 AH3:AH1000
ir_sheet 2 D10:D1000 F10:F1000 C10:C1000 H10:H1000 I10:I1000 AL10:AL000
IR_Sheet 3 D19:D1000 F19:F1000 C19:C1000 H19:H1000 I19:I1000 AM19:AM000
Op_Sheet 4 D26:D1000 F26:F1000 B26:B1000 E26:E1000 G26:G1000 BB26:BB1000
Need a macro which will pull the data from the various sheets and column ranges in to new sheet. This new sheet should be created as part of the macro
The data pulled by the above macro should be copied one below the other.
Thank you
I read the requirements differently. This code will copy the listed ranges from each sheet and copies them into one column for each data set in a new worksheet.
The code does not assume that each range will be full and copies the data after the end of the last entry.
The code does not assume that each range will be full and copies the data after the end of the last entry.
Sub copy()
Dim lastRow As Long
Application.Worksheets.Add After:=Worksheets(Worksheets.Count)
lastRow = 2
ActiveSheet.Range("A1").Value = "Data Set 1"
Worksheets("Os_sheet 1").Range("A3:A1000").copy ActiveSheet.Range("A" & lastRow)
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Sheets("ir_sheet 2").Range("D10:D1000").copy ActiveSheet.Range("A" & lastRow)
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Sheets("IR_Sheet 3").Range("D19:D1000").copy ActiveSheet.Range("A" & lastRow)
lastRow = Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Op_Sheet 4").Range("D26:D1000").copy ActiveSheet.Range("A" & lastRow)
lastRow = 2
ActiveSheet.Range("B1").Value = "Data Set 2"
Sheets("Os_sheet 1").Range("I3:I1000").copy ActiveSheet.Range("B" & lastRow)
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
Sheets("ir_sheet 2").Range("F10:F1000").copy ActiveSheet.Range("B" & lastRow)
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
Sheets("IR_Sheet 3").Range("F19:F1000").copy ActiveSheet.Range("B" & lastRow)
lastRow = Cells(Rows.Count, "B").End(xlUp).Row
Sheets("Op_Sheet 4").Range("F26:F1000").copy ActiveSheet.Range("B" & lastRow)
lastRow = 2
ActiveSheet.Range("C1").Value = "Data Set 3"
Sheets("Os_sheet 1").Range("U3:U1000").copy ActiveSheet.Range("C" & lastRow)
lastRow = Cells(Rows.Count, "C").End(xlUp).Row
Sheets("ir_sheet 2").Range("C10:C1000").copy ActiveSheet.Range("C" & lastRow)
lastRow = Cells(Rows.Count, "C").End(xlUp).Row
Sheets("IR_Sheet 3").Range("C19:C1000").copy ActiveSheet.Range("C" & lastRow)
lastRow = Cells(Rows.Count, "C").End(xlUp).Row
Sheets("Op_Sheet 4").Range("B26:B1000").copy ActiveSheet.Range("C" & lastRow)
lastRow = 2
ActiveSheet.Range("D1").Value = "Data Set 4"
Sheets("Os_sheet 1").Range("J3:J1000").copy ActiveSheet.Range("D" & lastRow)
lastRow = Cells(Rows.Count, "D").End(xlUp).Row
Sheets("ir_sheet 2").Range("H10:H1000").copy ActiveSheet.Range("D" & lastRow)
lastRow = Cells(Rows.Count, "D").End(xlUp).Row
Sheets("IR_Sheet 3").Range("H19:H1000").copy ActiveSheet.Range("D" & lastRow)
lastRow = Cells(Rows.Count, "D").End(xlUp).Row
Sheets("Op_Sheet 4").Range("E26:E1000").copy ActiveSheet.Range("D" & lastRow)
lastRow = 2
ActiveSheet.Range("E1").Value = "Data Set 5"
Sheets("Os_sheet 1").Range("K3:K1000").copy ActiveSheet.Range("D" & lastRow)
lastRow = Cells(Rows.Count, "E").End(xlUp).Row
Sheets("ir_sheet 2").Range("I10:I1000").copy ActiveSheet.Range("E" & lastRow)
lastRow = Cells(Rows.Count, "E").End(xlUp).Row
Sheets("IR_Sheet 3").Range("I19:I1000").copy ActiveSheet.Range("E" & lastRow)
lastRow = Cells(Rows.Count, "E").End(xlUp).Row
Sheets("Op_Sheet 4").Range("G26:G1000").copy ActiveSheet.Range("E" & lastRow)
lastRow = 2
ActiveSheet.Range("F1").Value = "Data Set 6"
Sheets("Os_sheet 1").Range("AH3:AH1000").copy ActiveSheet.Range("D" & lastRow)
lastRow = Cells(Rows.Count, "E").End(xlUp).Row
Sheets("ir_sheet 2").Range("AL10:AL1000").copy ActiveSheet.Range("E" & lastRow)
lastRow = Cells(Rows.Count, "E").End(xlUp).Row
Sheets("IR_Sheet 3").Range("AM19:AM1000").copy ActiveSheet.Range("E" & lastRow)
lastRow = Cells(Rows.Count, "E").End(xlUp).Row
Sheets("Op_Sheet 4").Range("BB26:BB1000").copy ActiveSheet.Range("E" & lastRow)
End Sub
ASKER
Hi Michael,
Thank you for the comment.
I ran the macro, and it populated the first data field from sheet 1 into a new sheet and then gave the following error message:
1.run time error subscript out of range.
Kindly help.
Thank you
Thank you for the comment.
I ran the macro, and it populated the first data field from sheet 1 into a new sheet and then gave the following error message:
1.run time error subscript out of range.
Kindly help.
Thank you
@Excellearner
Did i understand your need right? If not, please advice.
Did i understand your need right? If not, please advice.
ASKER
Gbanik,
the data fields are in several sheets in different columns,
Hence a macro is required to pull the data from vairous sheets and put the similar data fields one below the other (coming from various sheets) and different data fields in different columns.
thank you
the data fields are in several sheets in different columns,
Hence a macro is required to pull the data from vairous sheets and put the similar data fields one below the other (coming from various sheets) and different data fields in different columns.
thank you
So it means... i did the exact opposite of what u wanted? (please see my code)
If yes, I could rework it back ;)
If yes, I could rework it back ;)
ASKER
Gbanik,
I do not think the code is right.
Thank you, coudl you please help me
I do not think the code is right.
Thank you, coudl you please help me
Almost done ... one bit of info...
Do u need the Project Names or the Dataset as the columns in the new sheet?
I am guessing it is Dataset.
Do u need the Project Names or the Dataset as the columns in the new sheet?
I am guessing it is Dataset.
Ok... didnt get a reply so going by my assumption.
Here is the file attached and the code to do so.
Rum CNTRL + SHFT + X to run the macro
.... I had to fill dummy data to satisfy all your ranges... hence the file size.
Also, your had some of the ranges addresses wrong....Data set 6....AL10:AL000 and AM19:AM000
Fetchdata.zip
Here is the file attached and the code to do so.
Rum CNTRL + SHFT + X to run the macro
.... I had to fill dummy data to satisfy all your ranges... hence the file size.
Also, your had some of the ranges addresses wrong....Data set 6....AL10:AL000 and AM19:AM000
Fetchdata.zip
Excel learner
I ran my script against some test data and it worked
There was a small bug that put the data out of alignment but that is corrected.
If this still does not work could you please post your data for me to test against
test.zip
I ran my script against some test data and it worked
There was a small bug that put the data out of alignment but that is corrected.
If this still does not work could you please post your data for me to test against
test.zip
ASKER
gbanik,
I tried themacro on my sheet a nd i got the following error message.
method 'range' of object'_ application failed'.
Michael,
I tried the macro susggested by you and i got the folowing error message:
run time error 9
subscript out of range
Kindly help.
Thank you,
I tried themacro on my sheet a nd i got the following error message.
method 'range' of object'_ application failed'.
Michael,
I tried the macro susggested by you and i got the folowing error message:
run time error 9
subscript out of range
Kindly help.
Thank you,
I just download the zip file and tested again... it works.
Did u check the macro on my file?
Are you trying to use my macro on your sheet and it failed?
Did u check the macro on my file?
Are you trying to use my macro on your sheet and it failed?
ASKER
Gbanik
thank you for the time.
Yes it worked on your file but it failed on my file.
Becasuse of confidentiality i am not able to share the file.
Thank you
thank you for the time.
Yes it worked on your file but it failed on my file.
Becasuse of confidentiality i am not able to share the file.
Thank you
If you can share the piece of code that failed I may be able to help you.
Excellearner
I will second gbaniks suggestion, unless of course you can overwrite the sensitive data in the original file with some test data. Just use the drag option to copy down. Any column not used can simply be deleted
I will second gbaniks suggestion, unless of course you can overwrite the sensitive data in the original file with some test data. Just use the drag option to copy down. Any column not used can simply be deleted
ASKER
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Open in new window