Writing a Macro

[tcfthomas01]

Hello people, I have over 12,000 activities that I have to copy and paste, when I run a filter on both sheets I can only paste until the break in the filter. I am racking my brain to get this to work. I was introduced to macros' and I made a short marco:

Sub Macro31()
'
' Macro31 Macro
' Macro recorded 8/2/2009 by JR Thomas
'
' Keyboard Shortcut: Ctrl+q
'
Range("B3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("A2").Select
Sheets("Sheet1").Select
Range("K3").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Sheet2").Select
Range("C2").Select
ActiveSheet.Paste
Sheets("Sheet1").Select
Range("C4").Select
End Sub

I have a demo file if anyone would like to look at it

**Things to keep in mind:
I am running a filter on both sheets. (Visible Cells Only feature)?
I am finding a unique activity in sheet one to match sheet two then paste in sheet two assigned cell for cost.
I have it up and running for the first cell, but I can't get it to loop over and over! ANY HELP WOULD BE GREAT due to the fact I have to copy and paste each activity if not... Thanks

J.R. Thomas

 

[osx99]

The code below may help. It copies the visible filtered range from the first sheet into cell A1 of second sheet regardless of whether second sheet filter range is visible or not

Sub test()

Dim range As Range
Dim range2 As Range
 
With Sheet1.AutoFilter.Range
    Set range2 = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
    .SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
End With
If range2 Is Nothing Then
     MsgBox "No filter"
Else
Sheet1.AutoFilter.Range.Copy Destination:=Sheet2.Range("A1")
End If

End Sub

 

[tcfthomas01]

Please keep in mind that I am VERY NEW to VBA! So how would I add this to my macro, and will it loop to find the next cell and repeat its self?

 

[SkipVought]

Please explain WHAT you are trying to do instead of HOW you are tyring to get it done.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[tcfthomas01]

I have two spread sheets where I am trying to filter the sheets, and paste our cost from the first sheet. So I run filters on each sheet, and attempt to copy and paste. When doing so I can't paste past the break in the hidden (Filtered) cells. I need the spaces due to the fact I am running this as a third party app. to copy and paste the results into another program.







(Example)
I want to filter everything that has type "A" so I only have "B" showing
Copy "A" and paste in sheet 2 with filter on it as well...

Sheet 1
A B C D E F
1 WBS ID TYPE MIN ML MAX UNIT COST
2 1 A X X X $1000
3 2 B X X X $1200
4
5 3 B X X X $1400
6 4 A X X X $1600
7
8
9
10 5 B X X X $1800


Sheet 2
ID UNIT COST
1
2
3
4
5
6
7
8
9
10

 

[tcfthomas01]

**Sheet 2 also has TYPE in heading, and each ID is unique!!!
Sorry didn't post that in the last post

 

[Gavona]

Not really very clear at all.
Filtering, copying and pasting are HOW you are trying to do it. Skip asked WHAT you are trying to do.

What should the result look like in sheet 2?
Your posts don't indicate the result that you need or where you need them.

I am finding a unique activity in sheet one to match sheet two then paste in sheet two assigned cell for cost.

Is your requirement to bring into sheet1 the unit cost information for the relevant Type and ID? You say ID is unique so is it just the unit cost for each ID that you need (and we can ignore Type).
A vlookup could probably do this though you might want vba to convert from formulae to values as that many formulae would tend to slow the workbook.

If I am on the right lines then maybe the solution is to filter sheet2 to show the cells where you want the results, put a formula in these cells, convert the formula to values.



Comments on your approach:
Your original code references sheet1 cell K3. How does this relate to the file structure you have since posted?

In sheet 1 you seem to be wanting to hide with a filter rows of type A. Then you want to copy info about type A to Sheet2. Why not filter so that you can only see the type A records?

It is very unwise to paste into a filtered list. You will paste into the hidden rows. You should take the filter off sheet2 while pasting.





Gavin

 

[SkipVought]

When I hear "copy 'n' paste," it is almost always, an attempt to get a result that Excel can provide 'automatically,' using other more advanced features, like lookup functions, PivotTables or QueryTables.

Why the empty rows in sheet1? You should NEVER have empty rows in a table.



Skip,

_{Just traded in my old subtlety...
for a NUANCE!}