Change in macro

R

Roman12

Board Regular
Joined
Jun 10, 2008
Messages
117
Hi,
I have received the attached macro that does exactly what I asked for, but it would actually help if somebody could make a minor change in it.

The following macro copy pastes the data into different sheets. That means: in my sheet called Data, the macro copies the data in the whole row if the word xxx is written in column C and pastes it into the sheet with the same name.

Would it be possible to copy paste the data instead of the sheet into a new Excel file? This would mean, that the data of xxx would instead of in a sheet be placed into a new Excel file with the same name.

Thank you very much for your help.


Sub ExtractData()
Dim lr As Long
Dim i As Long

mysheet = Array("www", "xxx", "yyy")
lr = Sheets("Data").Range("C" & Rows.Count).End(xlUp).Row
Application.EnableEvents = False
Application.ScreenUpdating = False
For i = 0 To UBound(mysheet)
Sheets(mysheet(i)).UsedRange.ClearContents
With Sheets("Data").Range("A1:L" & lr)
.AutoFilter Field:=3, Criteria1:=mysheet(i)
.Copy Destination:=Sheets(mysheet(i)).Range("A1")
.AutoFilter
End With
Next i
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
 

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.
Sort by date Sort by votes
Hi

Perhaps:

Code: 
Sub ExtractData()
Dim lr As Long
Dim i As Long
Dim wb, ele
mysheet = Array("www", "xxx", "yyy")
For Each ele In mySheet
Set wb = Workbooks.Add(xlWBATWorksheet)
wb.Save ele
Next
lr = Sheets("Data").Range("C" & Rows.Count).End(xlUp).Row
Application.EnableEvents = False
Application.ScreenUpdating = False
For i = 0 To UBound(mysheet)
Sheets(mysheet(i)).UsedRange.ClearContents
With Sheets("Data").Range("A1:L" & lr)
.AutoFilter Field:=3, Criteria1:=mysheet(i)
.Copy Destination:=Workbooks(mysheet(i) & ".xls").Sheets(1).Range("A1")
.AutoFilter
End With
Next i
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
 
Upvote 0
Hi
Unfortunately, it does not work yet and I do not know where the mistake could be. There is a runtime error coming up saying that it could be the wrong number of arguments or invalid property assignment.

Until now, the macro copies the whole row in which the word i.e. xxx is existing in column C. The data is then pasted into a new sheet in the same workbook.

Is it possible to paste the data into a new workbook that is not created yet and will have the same name as the word in column C. Here xxx.

It might be more clear now.

Thank you
 
Upvote 0
Roman12 said:
Hi
Unfortunately, it does not work yet and I do not know where the mistake could be. There is a runtime error coming up saying that it could be the wrong number of arguments or invalid property assignment.

Until now, the macro copies the whole row in which the word i.e. xxx is existing in column C. The data is then pasted into a new sheet in the same workbook.

Is it possible to paste the data into a new workbook that is not created yet and will have the same name as the word in column C. Here xxx.

It might be more clear now.

Thank you
Click to expand...
When you get the error, do you have the option of clicking 'Debug' to identify which line the error is in?

Also, what version of Excel are you using?
 
Upvote 0
My macro changed the activeworkbook which is probably the cause of your problem. Try this amended version. If this fails, when you reply indicate on which line you get an error and copy out all the error information that is displayed when you get the runtime error:

Code: 
Sub ExtractData()
Dim lr As Long
Dim i As Long
Dim wb, ele
mysheet = Array("www", "xxx", "yyy")
For Each ele In mySheet
Set wb = Workbooks.Add(xlWBATWorksheet)
wb.Save ele
Next
ThisWorkbook.Activate
lr = Sheets("Data").Range("C" & Rows.Count).End(xlUp).Row
Application.EnableEvents = False
Application.ScreenUpdating = False
For i = 0 To UBound(mysheet)
Sheets(mysheet(i)).UsedRange.ClearContents
With Sheets("Data").Range("A1:L" & lr)
.AutoFilter Field:=3, Criteria1:=mysheet(i)
.Copy Destination:=Workbooks(mysheet(i) & ".xls").Sheets(1).Range("A1")
.AutoFilter
End With
Next i
Application.EnableEvents = True
Application.ScreenUpdating = True
End Sub
 
Upvote 0
It still does not work. There is no debug and I tried to delete the line you suggested.
The macro should put the data into a new Excel workbook instead of the sheet. Do you have another suggestion? By the way it is Microsoft Excel 2003
 
Upvote 0
How does the code not work? Is there a runtime error? If not, is it a case that the new workbooks get created but nothing gets copied across to them? or do the new workbooks not get created at all? The code does reside in the workbook in which the values will be copied from also exist?
 
Upvote 0
At the beginning, the workbook has been created, but no data has been copied in. At the moment I get a runtime error which says, subscript out of range. The workbooks in which the data has to be copied in do not exist yet.
 
Upvote 0
You must log in or register to reply here.

Similar threads

F
  • Solved
VBA set conditional formatting application-defined or object-defined error
Replies
3
Views
127
Cubist
Cubist
SaraWitch
  • Question
Password protect two sheets for viewing
Replies
1
Views
218
gplhl
G
J
  • Question
Help with VBA Code to to unlock and lock sheet when using a command a command
Replies
1
Views
156
Jeffrey Mahoney
Jeffrey Mahoney
B
  • Solved
VBA
Replies
5
Views
173
igold
igold
F
  • Question
VBA Code to record changes/logs modification needed?
Replies
0
Views
97
FGaxha
F

Forum statistics

Threads
1,216,037
Messages
6,128,440
Members
449,453
Latest member
jayeshw

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top