Pushing/Pulling Autofiltered data into specific section of another worksheet

[rem87]

Hi All,

I've spent the past week searching through every possible forum for a how-to, and have yet to find one. If an answer exists out there, please feel free to point me in the right direction.

I have one workbook with two worksheets: Sheet 1 (a list of raw data) and Sheet 2 - a preformatted agenda template that I've created. My goal is to be able to automatically pull/push specific (autofiltered) rows from Sheet 1 and populate it into a designated section of Sheet 2. I have created code autofiltering the raw data in Sheet 1. Success. I have test code copying/pasting the visible rows into a test spreadsheet. I'm stuck because I can't get the autofiltered rows into the specific section of Sheet 2 without upsetting my destination formatting. I want the designated section of Sheet 2 to expand or contract based on the number of filtered rows that I need to report on that week.

Example: This week, my autofilter shows 10 records (10 rows, 6 columns) in Sheet1. Section 2 of Sheet2 only has two rows (6 columns). What is the best way to insert these records into Section 2 and have Section 2 automatically expand/resize to an additional 8 rows? I realize that I could shift down and manually paste, but I lose my preformatted agenda template (and I really am reluctant to use Excel Reports).

An added complication is that Section 2 varies. I'm using a 'find' text code to locate and offset to my active cell and set where Section 2 is supposed to begin. Not sure if this makes sense, so please please feel free to chime in.

 

[williamgowtama]

i think in the sheet 2, copy the two rows that you want till it reaches the number of row that you want.

select the sheet2, 2 rows one,
Selection.copy
then use
for i=1 to (your number of records in sheet1 / 2)
sheets("sheet2").cells(((i*2)-1),1).pastespecial xlPasteFormats
next i
then copy your records from sheet1 as usual.

 

[rem87]

i think in the sheet 2, copy the two rows that you want till it reaches the number of row that you want.

select the sheet2, 2 rows one,
Selection.copy
then use
for i=1 to (your number of records in sheet1 / 2)
sheets("sheet2").cells(((i*2)-1),1).pastespecial xlPasteFormats
next i
then copy your records from sheet1 as usual.

Okay - so how do i count the number of records in sheet1 (visible cells only)?

Would using this code help? MsgBox (ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Count / ActiveSheet.AutoFilter.Range.Columns.Count) - 1

Is there a formula for that? i found code that uses a messagebox to return count of visible rows, but not sure how to apply the count to your suggestion above...

 

[williamgowtama]

the idea is to multiply the 2rows format in your sheet2, so ActiveSheet.AutoFilter.Range.SpecialCells(xlCellTypeVisible).Count / 2 is ok
xlCell Type Visible is okay. try and let me know the result

 

[rem87]

Thanks for getting back to me...here's what I have..running into issues - I tried simply inserting the Sheets(Sheet1).AutoFilter.Range.SpecialCells line into the for i=1 to...and kept running into issues. any ideas? tried setting i as active cell selection...didn't work. thanks

Sub ihopethisworks()
Dim wsSheet1 As Worksheet
Dim wsSheet2 As Worksheet
Dim i As Integer
Dim sourcedata As Range
Dim TotalRows As Long
Set wsSheet1 = Sheets("Sheet1")
With wsSheet1
Set datarange = .Range("E2:E" & .Cells(.Rows.Count, "E").End(xlUp).Row)
TotalRows = datarange.SpecialCells(xlCellTypeVisible).Count
End With
'select Sample Agenda Sheet and return to active cell selection of two rows
Set wsSheet2 = Sheets("Sheet2")
Sheets("Sheet2").Select
Selection.Copy

'then use
For i = 1 To (TotalRows / 2)
Sheets("Sample Agenda").Cells(((i * 2) - 1), 1).PasteSpecial xlPasteFormats
Next i

End Sub

 

[williamgowtama]

im a bit confused here because there's 3 sheets in your code, "sheet1", "sheet2", "Sample agenda".
Basically the sheet1 datas are not formatted yet,
and sheet2 has the format, and you want to keep the sheet2 formats?

 

[williamgowtama]

Sub ihopethisworks()Dim wsSheet1 As Worksheet
Dim wsSheet2 As Worksheet
Dim i As Integer
Dim sourcedata As Range
Dim TotalRows As Long
Set wsSheet1 = Sheets("Sheet1")
With wsSheet1
Set DataRange = .Range("E2:E" & .Cells(.Rows.Count, "E").End(xlUp).Row)
TotalRows = DataRange.SpecialCells(xlCellTypeVisible).Count
End With




Dim ends As Integer
ends = Application.RoundUp(((TotalRows / 2) - 1), 0)
'then use
For i = 1 To ends
Sheets("Sheet2").Cells((i * 2), 1).Copy
Sheets("Sheet2").Cells(((i + 1) * 2), 1).PasteSpecial xlPasteFormats
Sheets("Sheet2").Cells(((i + 1) * 2), 1).PasteSpecial xlPasteValues
Sheets("Sheet2").Cells(((i * 2) + 1), 1).Copy
Sheets("Sheet2").Cells((((i + 1) * 2) + 1), 1).PasteSpecial xlPasteFormats
Sheets("Sheet2").Cells((((i + 1) * 2) + 1), 1).PasteSpecial xlPasteValues
Next i


End Sub

Remember it only copy paste the formats of the cells. By "expand" do u want to insert new rows too?