Copy and paste bug

[Squareroot]

Hi,

I am trying to copy and paste some data from one sheet to another. I created a function to just do the copy paste becuse I have to do that for multiple headers. below is my code

Function insert(header)
Sheets(2).Activate
Sheets(2).Cells.Find(What:=header, After:=ActiveCell, LookIn:=xlFormulas2, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Do
ActiveCell.Offset(1, 0).Select
Loop While ActiveCell.EntireRow.Hidden = True

Range(ActiveCell, Range(ActiveCell.Address).End(xlDown)).Select
Selection.Copy
Sheets(1).Select
Sheets(1).Cells.Find(What:=header, After:=ActiveCell, LookIn:=xlFormulas2, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.Offset(2, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Function


''''''''''''''''''''''''''''''''''''''''''''''''main code''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
....
...
.
insert ("Supplier")

insert ("Description")

insert ("DwgNo")

insert ("QTY")

The code works well when I run it line by line using the F8 key. but when I run the code normally, it will have bug. The data under the supplier header will be pasted indefinitely until the entire column of my sheet 1 is filled. does anyone know what is wrong with my code?

 

[Squareroot]

Hi,

I am trying to copy and paste some data from one sheet to another. I created a function to just do the copy paste becuse I have to do that for multiple headers. below is my code

Function insert(header)
Sheets(2).Activate
Sheets(2).Cells.Find(What:=header, After:=ActiveCell, LookIn:=xlFormulas2, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Do
ActiveCell.Offset(1, 0).Select
Loop While ActiveCell.EntireRow.Hidden = True

Range(ActiveCell, Range(ActiveCell.Address).End(xlDown)).Select
Selection.Copy
Sheets(1).Select
Sheets(1).Cells.Find(What:=header, After:=ActiveCell, LookIn:=xlFormulas2, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.Offset(2, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Function


''''''''''''''''''''''''''''''''''''''''''''''''main code''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
....
...
.
insert ("Supplier")

insert ("Description")

insert ("DwgNo")

insert ("QTY")

The code works well when I run it line by line using the F8 key. but when I run the code normally, it will have bug. The data under the supplier header will be pasted indefinitely until the entire column of my sheet 1 is filled. does anyone know what is wrong with my code?

To add on, not too sure if this is the reason, I have a duplicated header at sheet 1. I have 1 supplier header in row1 and another in row 2

 

[Squareroot]

Found reason.

Hi,

I am trying to copy and paste some data from one sheet to another. I created a function to just do the copy paste becuse I have to do that for multiple headers. below is my code

Function insert(header)
Sheets(2).Activate
Sheets(2).Cells.Find(What:=header, After:=ActiveCell, LookIn:=xlFormulas2, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Do
ActiveCell.Offset(1, 0).Select
Loop While ActiveCell.EntireRow.Hidden = True

Range(ActiveCell, Range(ActiveCell.Address).End(xlDown)).Select
Selection.Copy
Sheets(1).Select
Sheets(1).Cells.Find(What:=header, After:=ActiveCell, LookIn:=xlFormulas2, LookAt _
:=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
False, SearchFormat:=False).Activate
Selection.Offset(2, 0).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
End Function


''''''''''''''''''''''''''''''''''''''''''''''''main code''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
....
...
.
insert ("Supplier")

insert ("Description")

insert ("DwgNo")

insert ("QTY")

The code works well when I run it line by line using the F8 key. but when I run the code normally, it will have bug. The data under the supplier header will be pasted indefinitely until the entire column of my sheet 1 is filled. does anyone know what is wrong with my code?

Please ignore.. found the reason.