Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Copying/Pasting problem when data is filtered
I've got 3 worksheets, one containing source data and two associated with
specific companies. Firstly, I need to filter the source data worksheet to identify the required company name. Once filtered, I then need to copy/paste certain columns into the relevant worksheet. The code below works fine; filtering column A of the source data worksheet by the company name (ABC Ltd), and pasting it into the relevant section of the ABC Ltd worksheet. However, the code is only ok when the first filtered row is row 2. When repeating the code for the next company/worksheet, the first filtered row begins at row 3850, but this may change everytime the source data is updated. Is there any way therefore, to avoid specifying a particular row number once the data has been filtered? Or can I copy the cells from the required columns where the value in column A matches a certain value/name? Apologies if I've overcomplicated a simple issue. Many thanks! Sheets("OCR").Select ActiveSheet.ShowAllData Selection.AutoFilter Field:=1, Criteria1:="ABC Ltd" Range("O2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("ABC OCR").Select Range("E2").Select ActiveSheet.Paste Range("F2").Select Sheets("OCR").Select Range("Q2").Select Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.Copy Sheets("ABC OCR").Select ActiveSheet.Paste Range("G2").Select Sheets("OCR").Select Range("U2:W2").Select Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.Copy Sheets("ABC OCR").Select ActiveSheet.Paste Range("J2").Select Sheets("OCR").Select Range("AD2").Select Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.Copy Sheets("ABC OCR").Select ActiveSheet.Paste Range("D3").Select Range(Selection, Selection.End(xlToLeft)).Select Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.ClearContents Range("D2").Select Range(Selection, Selection.End(xlToLeft)).Select Selection.AutoFill Destination:=Range("A2:D" & ActiveSheet.UsedRange.Rows.Count) Range("E2").Select |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
VBA Copying/Pasting problem when data is filtered
I don't know if last Row is the same for every column, if so you only need
one Last Row statement With Sheets("OCR") .ShowAllData .AutoFilter Field:=1, Criteria1:="ABC Ltd" LastRow = Range("O" & Rows.Count).End(xlUp).Row .Range("O2:O" & LastRow).SpecialCells(xlCellTypeVisible).Copy _ Destination:=Sheets("ABC OCR").Range("E2") LastRow = Range("Q" & Rows.Count).End(xlUp).Row .Range("Q2:Q" & LastRow).SpecialCells(xlCellTypeVisible).Copy _ Destination:=Sheets("ABC OCR").Range("F2") LastRow = Range("U" & Rows.Count).End(xlUp).Row .Range("U2:W" & LastRow).SpecialCells(xlCellTypeVisible).Copy _ Destination:=Sheets("ABC OCR").Range("G2") LastRow = Range("AD" & Rows.Count).End(xlUp).Row .Range("AD2:AD" & LastRow).SpecialCells(xlCellTypeVisible).Copy _ Destination:=Sheets("ABC OCR").Range("J2") End With With Sheets("ABC OCR") FirstCol = .Range("D2").End(xlToLeft).Column LastRow = .Cells(2, FirstCol).End(xlDown).Row .Range(.Cells(3, FirstrCol), .Cells(LastRow, "D")).ClearContents .Range(.Cells(2, FirstCol), .Range("D2")).Copy _ Destination:=.Range(.Cells(3, FirstrCol), .Cells(LastRow, "D")) End With "Sarah (OGI)" wrote: I've got 3 worksheets, one containing source data and two associated with specific companies. Firstly, I need to filter the source data worksheet to identify the required company name. Once filtered, I then need to copy/paste certain columns into the relevant worksheet. The code below works fine; filtering column A of the source data worksheet by the company name (ABC Ltd), and pasting it into the relevant section of the ABC Ltd worksheet. However, the code is only ok when the first filtered row is row 2. When repeating the code for the next company/worksheet, the first filtered row begins at row 3850, but this may change everytime the source data is updated. Is there any way therefore, to avoid specifying a particular row number once the data has been filtered? Or can I copy the cells from the required columns where the value in column A matches a certain value/name? Apologies if I've overcomplicated a simple issue. Many thanks! Sheets("OCR").Select ActiveSheet.ShowAllData Selection.AutoFilter Field:=1, Criteria1:="ABC Ltd" Range("O2").Select Range(Selection, Selection.End(xlDown)).Select Selection.Copy Sheets("ABC OCR").Select Range("E2").Select ActiveSheet.Paste Range("F2").Select Sheets("OCR").Select Range("Q2").Select Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.Copy Sheets("ABC OCR").Select ActiveSheet.Paste Range("G2").Select Sheets("OCR").Select Range("U2:W2").Select Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.Copy Sheets("ABC OCR").Select ActiveSheet.Paste Range("J2").Select Sheets("OCR").Select Range("AD2").Select Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.Copy Sheets("ABC OCR").Select ActiveSheet.Paste Range("D3").Select Range(Selection, Selection.End(xlToLeft)).Select Range(Selection, Selection.End(xlDown)).Select Application.CutCopyMode = False Selection.ClearContents Range("D2").Select Range(Selection, Selection.End(xlToLeft)).Select Selection.AutoFill Destination:=Range("A2:D" & ActiveSheet.UsedRange.Rows.Count) Range("E2").Select |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Filtered Mode Copying and pasting as value yield as error? | Excel Discussion (Misc queries) | |||
Problem with Pasting into Filtered Fields | Excel Worksheet Functions | |||
copying and pasting range problem | Excel Programming | |||
Pasting Onto Filtered Data | Excel Discussion (Misc queries) | |||
Pasting on Filtered Data Sheets without pasting onto hidden cells | Excel Discussion (Misc queries) |