Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
JLGWhiz, I have copied everything this is how it looks....
Macro 1 Start last4() next last4 Userform6 Userform_Initialize() Next Listbox1_click() 1) I get the listbox and can pick a mix type then I recieve an error "Autofilter Method or Range class failed". 2) In this list box can we do the no dupes so that I don't have a mix type for each test. I only want to see the different mix types not all of them. 3) I will need to delete cells ("B72:AD700") on sheets("last four") after it posts up on to rows 9 through 12 on sheets("last four"). I figure I can tack this on to the Last4 macro, is this correct? 4) At the end of your last posting you place New subject. Clearing content from Range("A9:AD12").clearcontents. Did you think I wanted to clear this? I do not want this cleared until the next time I need to run this macro. Eric |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
Same as before, your Q my A.
1) I get the listbox and can pick a mix type then I recieve an error "Autofilter Method or Range class failed". That autofilter in my nemesis. I couldn't get it to work in the click event so I moved it to the standard module and it worked. Now you are getting the error in the standard module. Are you sure you put the code in Module1 of the VB editor? It will not work properly anywhere else. If you did, then copy the code and paste it back into this posting so I can see if I left something in that I used to run on my system. 2) In this list box can we do the no dupes so that I don't have a mix type for each test. I only want to see the different mix types not all of them. I think that if the autofilter works, it will do the same thing. If you look at your no dupes code and then look at the first half of the new last4 code it should be about the same. Let me know if I am wrong. 3) I will need to delete cells ("B72:AD700") on sheets("last four") after it posts up on to rows 9 through 12 on sheets("last four"). I figure I can tack this on to the Last4 macro, is this correct? Yes, Add this line just before the "End Sub" in the last4 code: Sheets("last four").Range("B72:AD" & lr4).ClearContents The variable lr4 allows for any changes in the size of the data range. It finds the last row that was copied over no matter how many. 4) At the end of your last posting you place New subject. Clearing content from Range("A9:AD12").clearcontents. Did you think I wanted to clear this? I do not want this cleared until the next time I need to run this macro. Read the instructions carefully. It says to change the code that is now in there from Range("A9:Z12").ClearContents. The location of this command in the code is before the data is copied over from test Database, which means that it is clearing data from the previous posting not the current one. In the mean time here is something for you to play with. You might have done this before, but just in case, follow these steps to see what the code does. 1. Open the VB editor. Alt + F11 Or ToolsMacroVisual Basic Editor 2. In the Project window on the top left of the screen, double click on module1, if you are not already in that screen. 3. In the upper right of the screen is a panel that says Declarations. Click in this panel and find "startLast4". Click on it. 4. You should now see that code segment in the big window and the cursor should be blinking there. Click on the name one time just in case. 5. Now you can use the F8 key to move one step at a time through the entire process and see how the code works. A yellow highlight will show you which step is executing as you move through. 6. If you are not in full screen with the VBE window, you can move that window over so you can watch the Excel sheets as you execute the code. Don't be surprised when the VBE screen disappears while the UserForm is showing. When you click the list box the VBE screen returns. 7. This is how you can debug your code. You can see exactly which step causes the error. Have fun. "Eric" wrote: JLGWhiz, I have copied everything this is how it looks.... Macro 1 Start last4() next last4 Userform6 Userform_Initialize() Next Listbox1_click() 1) I get the listbox and can pick a mix type then I recieve an error "Autofilter Method or Range class failed". 2) In this list box can we do the no dupes so that I don't have a mix type for each test. I only want to see the different mix types not all of them. 3) I will need to delete cells ("B72:AD700") on sheets("last four") after it posts up on to rows 9 through 12 on sheets("last four"). I figure I can tack this on to the Last4 macro, is this correct? 4) At the end of your last posting you place New subject. Clearing content from Range("A9:AD12").clearcontents. Did you think I wanted to clear this? I do not want this cleared until the next time I need to run this macro. Eric |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
I assume you mean Module 1 below.
"Eric" wrote: JLGWhiz, I have copied everything this is how it looks.... Macro 1 Start last4() next last4 Userform6 Userform_Initialize() Next Listbox1_click() 1) I get the listbox and can pick a mix type then I recieve an error "Autofilter Method or Range class failed". 2) In this list box can we do the no dupes so that I don't have a mix type for each test. I only want to see the different mix types not all of them. 3) I will need to delete cells ("B72:AD700") on sheets("last four") after it posts up on to rows 9 through 12 on sheets("last four"). I figure I can tack this on to the Last4 macro, is this correct? 4) At the end of your last posting you place New subject. Clearing content from Range("A9:AD12").clearcontents. Did you think I wanted to clear this? I do not want this cleared until the next time I need to run this macro. Eric |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
Here is the macro. I double checked and it is in module 1
Sub last4() Dim ws As Worksheet Dim rng As Range Dim rng2 As Range Dim lr4, lc4, mCnt, cnt As Long lr = Sheets("test Database").Cells(Rows.count, 2).End(xlUp).Row lr4 = Sheets("last four").Cells(Rows.count, 2).End(xlUp).Row Set ws = Worksheets("test Database") Set rng = ws.Range("B26:AD" & lr) ' UserForm6.Show myVar4 = Sheets("test Database").Range("A25") If Sheets("test Database").Range("A25") "" Then With Application .ScreenUpdating = False .EnableEvents = False End With ws.AutoFilterMode = False cRng = Sheets("test Database").Range("A25").Value rng.AutoFilter Field:=1, Criteria1:=cRng, VisibleDropDown:=False ws.AutoFilter.Range.Copy Sheets("last four").Range("B" & lr4 + 1).PasteSpecial Paste:=xlValues ws.AutoFilterMode = False Application.CutCopyMode = False With Application .ScreenUpdating = True .EnableEvents = True End With End If With Sheets("last four") lr4 = Sheets("last four").Cells(Rows.count, 2).End(xlUp).Row lc4 = Sheets("last four").UsedRange.Columns.count + 1 mCnt = Application.CountIf(.Range("B72:B" & lr4), myVar4) Range("A9:Z12").ClearContents With Application .ScreenUpdating = False .EnableEvents = False End With If mCnt = 4 Then mCnt = 4 End If cnt = 1 For i = lr4 To 72 Step -1 If .Cells(i, 2) = myVar4 Then If cnt <= 4 Then Select Case mCnt Case Is = 1 .Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B9").PasteSpecial Paste:=xlPasteValues Case Is = 2 If x = "" Then x = 10 .Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B" & x).PasteSpecial Paste:=xlPasteValues x = x - 1 Case Is = 3 If x = "" Then x = 11 .Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B" & x).PasteSpecial Paste:=xlPasteValues x = x - 1 Case Is = 4 If x = "" Then x = 12 .Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B" & x).PasteSpecial Paste:=xlPasteValues x = x - 1 End Select cnt = cnt + 1 End If End If Next With Application .ScreenUpdating = True .EnableEvents = True End With End With Application.CutCopyMode = False End Sub Eric "Eric" wrote: yes I did mean module 1 sorry about that.... Eric "JLGWhiz" wrote: I assume you mean Module 1 below. "Eric" wrote: JLGWhiz, I have copied everything this is how it looks.... Macro 1 Start last4() next last4 Userform6 Userform_Initialize() Next Listbox1_click() 1) I get the listbox and can pick a mix type then I recieve an error "Autofilter Method or Range class failed". 2) In this list box can we do the no dupes so that I don't have a mix type for each test. I only want to see the different mix types not all of them. 3) I will need to delete cells ("B72:AD700") on sheets("last four") after it posts up on to rows 9 through 12 on sheets("last four"). I figure I can tack this on to the Last4 macro, is this correct? 4) At the end of your last posting you place New subject. Clearing content from Range("A9:AD12").clearcontents. Did you think I wanted to clear this? I do not want this cleared until the next time I need to run this macro. Eric |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
JLWhiz,
It works with one exception.....The listbox1 doesn't do a nodupe procedure. The problem with the macro was in the listbox1 procedure which had sheet("sheet1") cells("A25") instead of sheets("test database"). Unfortunely I have a sheet in the workbook call sheet 1 that I record all changes to the workbook so I can go back and see what I have done ....... Now the only thing is the nodupes. You are fantastic thank you.... Eric "Eric" wrote: Here is the macro. I double checked and it is in module 1 Sub last4() Dim ws As Worksheet Dim rng As Range Dim rng2 As Range Dim lr4, lc4, mCnt, cnt As Long lr = Sheets("test Database").Cells(Rows.count, 2).End(xlUp).Row lr4 = Sheets("last four").Cells(Rows.count, 2).End(xlUp).Row Set ws = Worksheets("test Database") Set rng = ws.Range("B26:AD" & lr) ' UserForm6.Show myVar4 = Sheets("test Database").Range("A25") If Sheets("test Database").Range("A25") "" Then With Application .ScreenUpdating = False .EnableEvents = False End With ws.AutoFilterMode = False cRng = Sheets("test Database").Range("A25").Value rng.AutoFilter Field:=1, Criteria1:=cRng, VisibleDropDown:=False ws.AutoFilter.Range.Copy Sheets("last four").Range("B" & lr4 + 1).PasteSpecial Paste:=xlValues ws.AutoFilterMode = False Application.CutCopyMode = False With Application .ScreenUpdating = True .EnableEvents = True End With End If With Sheets("last four") lr4 = Sheets("last four").Cells(Rows.count, 2).End(xlUp).Row lc4 = Sheets("last four").UsedRange.Columns.count + 1 mCnt = Application.CountIf(.Range("B72:B" & lr4), myVar4) Range("A9:Z12").ClearContents With Application .ScreenUpdating = False .EnableEvents = False End With If mCnt = 4 Then mCnt = 4 End If cnt = 1 For i = lr4 To 72 Step -1 If .Cells(i, 2) = myVar4 Then If cnt <= 4 Then Select Case mCnt Case Is = 1 .Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B9").PasteSpecial Paste:=xlPasteValues Case Is = 2 If x = "" Then x = 10 .Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B" & x).PasteSpecial Paste:=xlPasteValues x = x - 1 Case Is = 3 If x = "" Then x = 11 .Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B" & x).PasteSpecial Paste:=xlPasteValues x = x - 1 Case Is = 4 If x = "" Then x = 12 .Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B" & x).PasteSpecial Paste:=xlPasteValues x = x - 1 End Select cnt = cnt + 1 End If End If Next With Application .ScreenUpdating = True .EnableEvents = True End With End With Application.CutCopyMode = False End Sub Eric "Eric" wrote: yes I did mean module 1 sorry about that.... Eric "JLGWhiz" wrote: I assume you mean Module 1 below. "Eric" wrote: JLGWhiz, I have copied everything this is how it looks.... Macro 1 Start last4() next last4 Userform6 Userform_Initialize() Next Listbox1_click() 1) I get the listbox and can pick a mix type then I recieve an error "Autofilter Method or Range class failed". 2) In this list box can we do the no dupes so that I don't have a mix type for each test. I only want to see the different mix types not all of them. 3) I will need to delete cells ("B72:AD700") on sheets("last four") after it posts up on to rows 9 through 12 on sheets("last four"). I figure I can tack this on to the Last4 macro, is this correct? 4) At the end of your last posting you place New subject. Clearing content from Range("A9:AD12").clearcontents. Did you think I wanted to clear this? I do not want this cleared until the next time I need to run this macro. Eric |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
I thought I told you about that change in the ListBox1 code, but maybe I
forgot. At my age, I am lucky to remember to get up in the morning. Anyhow, glad you got it working. I am not sure what your no dupes is. I assume it is something you run prior to running the autofilter. If it is, and you want to automatically run the UserForm6 and last four code when you run the no dupes, then just put a line in the "no dupes" right before the End Sub like: UserForm6.Show and you are in business. No dupes will run, call UserForm6, you click the ListBox1 and "last4" will run. Simple. Good Luck. "Eric" wrote: JLWhiz, It works with one exception.....The listbox1 doesn't do a nodupe procedure. The problem with the macro was in the listbox1 procedure which had sheet("sheet1") cells("A25") instead of sheets("test database"). Unfortunely I have a sheet in the workbook call sheet 1 that I record all changes to the workbook so I can go back and see what I have done ....... Now the only thing is the nodupes. You are fantastic thank you.... Eric "Eric" wrote: Here is the macro. I double checked and it is in module 1 Sub last4() Dim ws As Worksheet Dim rng As Range Dim rng2 As Range Dim lr4, lc4, mCnt, cnt As Long lr = Sheets("test Database").Cells(Rows.count, 2).End(xlUp).Row lr4 = Sheets("last four").Cells(Rows.count, 2).End(xlUp).Row Set ws = Worksheets("test Database") Set rng = ws.Range("B26:AD" & lr) ' UserForm6.Show myVar4 = Sheets("test Database").Range("A25") If Sheets("test Database").Range("A25") "" Then With Application .ScreenUpdating = False .EnableEvents = False End With ws.AutoFilterMode = False cRng = Sheets("test Database").Range("A25").Value rng.AutoFilter Field:=1, Criteria1:=cRng, VisibleDropDown:=False ws.AutoFilter.Range.Copy Sheets("last four").Range("B" & lr4 + 1).PasteSpecial Paste:=xlValues ws.AutoFilterMode = False Application.CutCopyMode = False With Application .ScreenUpdating = True .EnableEvents = True End With End If With Sheets("last four") lr4 = Sheets("last four").Cells(Rows.count, 2).End(xlUp).Row lc4 = Sheets("last four").UsedRange.Columns.count + 1 mCnt = Application.CountIf(.Range("B72:B" & lr4), myVar4) Range("A9:Z12").ClearContents With Application .ScreenUpdating = False .EnableEvents = False End With If mCnt = 4 Then mCnt = 4 End If cnt = 1 For i = lr4 To 72 Step -1 If .Cells(i, 2) = myVar4 Then If cnt <= 4 Then Select Case mCnt Case Is = 1 .Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B9").PasteSpecial Paste:=xlPasteValues Case Is = 2 If x = "" Then x = 10 .Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B" & x).PasteSpecial Paste:=xlPasteValues x = x - 1 Case Is = 3 If x = "" Then x = 11 .Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B" & x).PasteSpecial Paste:=xlPasteValues x = x - 1 Case Is = 4 If x = "" Then x = 12 .Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B" & x).PasteSpecial Paste:=xlPasteValues x = x - 1 End Select cnt = cnt + 1 End If End If Next With Application .ScreenUpdating = True .EnableEvents = True End With End With Application.CutCopyMode = False End Sub Eric "Eric" wrote: yes I did mean module 1 sorry about that.... Eric "JLGWhiz" wrote: I assume you mean Module 1 below. "Eric" wrote: JLGWhiz, I have copied everything this is how it looks.... Macro 1 Start last4() next last4 Userform6 Userform_Initialize() Next Listbox1_click() 1) I get the listbox and can pick a mix type then I recieve an error "Autofilter Method or Range class failed". 2) In this list box can we do the no dupes so that I don't have a mix type for each test. I only want to see the different mix types not all of them. 3) I will need to delete cells ("B72:AD700") on sheets("last four") after it posts up on to rows 9 through 12 on sheets("last four"). I figure I can tack this on to the Last4 macro, is this correct? 4) At the end of your last posting you place New subject. Clearing content from Range("A9:AD12").clearcontents. Did you think I wanted to clear this? I do not want this cleared until the next time I need to run this macro. Eric |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
copy and paste
' UserForm6.Show
I see that you commented this out. You should delete it completely so it does not accidentally have the comment mark removed. It will screw up the process if it calls the UserForm form the last4 procedure. Range("A9:Z12").ClearContents Also don't forget to change the Z12 to AD12 in this line. Othewise, you will have data in cells for AA thru AD when you have less than four tests. I shortened it for the data I was using during testing and forgot to change it back. If data in those columns don't matter, then leave it alone. See ya! "Eric" wrote: JLWhiz, It works with one exception.....The listbox1 doesn't do a nodupe procedure. The problem with the macro was in the listbox1 procedure which had sheet("sheet1") cells("A25") instead of sheets("test database"). Unfortunely I have a sheet in the workbook call sheet 1 that I record all changes to the workbook so I can go back and see what I have done ....... Now the only thing is the nodupes. You are fantastic thank you.... Eric "Eric" wrote: Here is the macro. I double checked and it is in module 1 Sub last4() Dim ws As Worksheet Dim rng As Range Dim rng2 As Range Dim lr4, lc4, mCnt, cnt As Long lr = Sheets("test Database").Cells(Rows.count, 2).End(xlUp).Row lr4 = Sheets("last four").Cells(Rows.count, 2).End(xlUp).Row Set ws = Worksheets("test Database") Set rng = ws.Range("B26:AD" & lr) ' UserForm6.Show myVar4 = Sheets("test Database").Range("A25") If Sheets("test Database").Range("A25") "" Then With Application .ScreenUpdating = False .EnableEvents = False End With ws.AutoFilterMode = False cRng = Sheets("test Database").Range("A25").Value rng.AutoFilter Field:=1, Criteria1:=cRng, VisibleDropDown:=False ws.AutoFilter.Range.Copy Sheets("last four").Range("B" & lr4 + 1).PasteSpecial Paste:=xlValues ws.AutoFilterMode = False Application.CutCopyMode = False With Application .ScreenUpdating = True .EnableEvents = True End With End If With Sheets("last four") lr4 = Sheets("last four").Cells(Rows.count, 2).End(xlUp).Row lc4 = Sheets("last four").UsedRange.Columns.count + 1 mCnt = Application.CountIf(.Range("B72:B" & lr4), myVar4) Range("A9:Z12").ClearContents With Application .ScreenUpdating = False .EnableEvents = False End With If mCnt = 4 Then mCnt = 4 End If cnt = 1 For i = lr4 To 72 Step -1 If .Cells(i, 2) = myVar4 Then If cnt <= 4 Then Select Case mCnt Case Is = 1 .Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B9").PasteSpecial Paste:=xlPasteValues Case Is = 2 If x = "" Then x = 10 .Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B" & x).PasteSpecial Paste:=xlPasteValues x = x - 1 Case Is = 3 If x = "" Then x = 11 .Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B" & x).PasteSpecial Paste:=xlPasteValues x = x - 1 Case Is = 4 If x = "" Then x = 12 .Range(.Cells(i, 2), .Cells(i, lc4)).Copy .Range("B" & x).PasteSpecial Paste:=xlPasteValues x = x - 1 End Select cnt = cnt + 1 End If End If Next With Application .ScreenUpdating = True .EnableEvents = True End With End With Application.CutCopyMode = False End Sub Eric "Eric" wrote: yes I did mean module 1 sorry about that.... Eric "JLGWhiz" wrote: I assume you mean Module 1 below. "Eric" wrote: JLGWhiz, I have copied everything this is how it looks.... Macro 1 Start last4() next last4 Userform6 Userform_Initialize() Next Listbox1_click() 1) I get the listbox and can pick a mix type then I recieve an error "Autofilter Method or Range class failed". 2) In this list box can we do the no dupes so that I don't have a mix type for each test. I only want to see the different mix types not all of them. 3) I will need to delete cells ("B72:AD700") on sheets("last four") after it posts up on to rows 9 through 12 on sheets("last four"). I figure I can tack this on to the Last4 macro, is this correct? 4) At the end of your last posting you place New subject. Clearing content from Range("A9:AD12").clearcontents. Did you think I wanted to clear this? I do not want this cleared until the next time I need to run this macro. Eric |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Automating copy/paste/paste special when row references change | Excel Programming | |||
help w/ generic copy & paste/paste special routine | Excel Programming | |||
Excel cut/Paste Problem: Year changes after data is copy and paste | Excel Discussion (Misc queries) | |||
Copy and Paste macro needs to paste to a changing cell reference | Excel Programming |