I am unable to inserted a copied range from sheet1 to sheet2

[benpatz]

I am trying to make sheet 1 a place to enter data and sheet 2 into a publishable sheet. All my code works until I try and insert the range I copied from sheet 1. I tested it and range is selected if I don't enter the, Selection.Insert Shift:=xlDown command. Once I put this part of visual basics into the macro the copied range selected, deselects, and the insert command doesn't work.

Here is my code:
Option Explicit


Sub CopyandInsert()


' CTR+a is the shortcut key


ThisWorkbook.Worksheets("Sheet2").Range("A1:D400").ClearContents


Dim wsData As Worksheet
Set wsData = ThisWorkbook.Worksheets("Sheet1")


Dim wsPublishSheet As Worksheet
Set wsPublishSheet = ThisWorkbook.Worksheets("Sheet2")


wsData.Select
With wsData


On Error Resume Next
'//clear out filter
.ShowAllData
Range("A1:D1").AutoFilter 3, "LC"

End With ' LC Filter Complete


With wsData.AutoFilter.Range
.Offset(1, 0).Resize(.Rows.Count - 1).Copy
End With 'takes header out of the range I want to copy


wsPublishSheet.Select
Range("A12:D12").Select
Selection.Insert Shift:=xlDown ' Inserts copied cells into the range (this command doesn't work)


'Selection.Insert Shift:=xlDown 'If I want to paste, delete the Selection.Insert
'method and replace it with, ActiveSheet.paste




End Sub

 

[Fluff]

How about

Sub CopyandInsert()
' CTR+a is the shortcut key

Dim wsData As Worksheet
Set wsData = ThisWorkbook.Worksheets("Sheet1")


Dim wsPublishSheet As Worksheet
Set wsPublishSheet = ThisWorkbook.Worksheets("Sheet2")

wsPublishSheet.Range("A1:D400").ClearContents

wsData.Select
With wsData
   On Error Resume Next
   '//clear out filter
   .ShowAllData
   .Range("A1:D1").AutoFilter 3, "LC"
   .AutoFilter.Range.Offset(1, 0).Resize(.Rows.Count - 1).Copy
End With ' LC Filter Complete

wsPublishSheet.Range("A12").Insert Shift:=xlDown
End Sub

 

[benpatz]

I worked with your suggestions in a few ways. One, I copied your code and put it into my visual basic editor. The code ran but I had similar results with your code as I did with mine. The program still wouldn’t insert the cells that I wanted to insert on to wsPublishSheet.

Two, I marked this part as comment to test the filter,
‘wsPublishSheet.Range("A12").Insert Shift:=xlDown

When I tested the AutoFilter, the range did deselect the headers in (“A1:D1”), but the rest of the 4 columns A through D were selected. The Range seemed to include all of the columns but the header.

I had two new ideas too.
One, I was going to try and see if I can call the Clipboard part of the program after selecting wsPublishSheet and the range I want on wsPublishSheet and see if the Clipboard will help me insert the data I copied. I am doing this because I am wondering if I am telling the computer too much or not enough before I insert. It is almost like the copied range deselects itself before I want to insert.

Two, I also was wondering if I could nest. The .Copy(Destination) has the destination part. I was wondering if I code nest something like this .Copy(wsPublish.Range(“A12”).Insert Shift:=xlDown). I’ll look that up to.

I am shooting in the dark here so please don’t stop helping. Thanks so much for the help.

 

[Fluff]

How about

With wsData
'   On Error Resume Next
   '//clear out filter
   .ShowAllData
   .Range("A1:D1").AutoFilter 3, "LC"
   .AutoFilter.Range.Offset(1, 0).Resize(.Rows.Count - 1).Copy wsPublishSheet.Range("A12")
End With ' LC Filter Complete

End Sub

 

[benpatz]

I tried the process I am trying to do without visual basics in Excel. My conclusion is the autofilter selects rows 2,3, and 6. Rows 4 and 5 are filtered out. I wasn't able to insert this type of selection manually either. I think that is my problem.

 

[benpatz]

I added MicroSoft Forms 2.0 object library to my VBE. I put his code right after the autofitler to see what was on my clipboard and it is what I want to insert on the wsPublishSheet.

Dim DataObj As New MSForms.DataObject
Dim S As String
DataObj.GetFromClipboard
S = DataObj.GetText