LinkBack Thread Tools Search this Thread Display Modes
Prev Previous Post   Next Post Next
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
 
Posts: 852
Default Code to copy header does not copy

This line will not copy the header of the Sheets(MyArr(i)) in the code below.

Range(Cells(1, 1), Cells(1, lcSH)).Copy Sheets("Master").Range("A" & Rows.Count).End(xlUp)(1) - or with the (2)

In a snippet run in the sheet module the line works fine.

Note that if it is the first time the code runs, I want the header to copy to row 1 on Master sheet and the FoundWk entries to copy directly below that header.

I believe I have the code set to provide a blank row for each time the code is run so there will be a header copy and a number of entries directly following. Then when run again, a blank row then a header copy and a number of entries directly following. (Can't get the header to copy so that has not been verified)

So on the Master I would have starting in row 1:

Header
entry
entry
entry

Header
entry
entry
entry
entry
entry

Header
entry
entry

Thanks,
Howard

Sub WeeklyReader()
Dim c As Range
Dim i As Long
Dim MyArr As Variant
Dim lrSH As Long, lcSH As Long
Dim FoundWk As Range
Dim aWeek As Variant

aWeek = InputBox("Enter the WEEK to search for")

If aWeek = "" Then
Exit Sub
ElseIf IsNumeric(aWeek) Then
aWeek = Val(aWeek) '/ converts a "text" number to a value
Else
'/ is text and that is okay
End If

MyArr = Array("Bodypump", "Spinning", "Zumba")

Application.ScreenUpdating = False

For i = LBound(MyArr) To UBound(MyArr)

With Sheets(MyArr(i))

lrSH = .Cells(Rows.Count, 1).End(xlUp).Row
lcSH = .Cells.Find(What:="*", after:=[A1], _
SearchOrder:=xlByColumns, _
SearchDirection:=xlPrevious).Column

Set FoundWk = .Range("A2:A" & lrSH).Find(What:=aWeek, _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByColumns, _
SearchDirection:=xlNext, MatchCase:=False, _
SearchFormat:=False)

If Not FoundWk Is Nothing Then
If Sheets("Master").Range("A" & Rows.Count).End(xlUp).Row = 1 Then
Range(Cells(1, 1), Cells(1, lcSH)).Copy Sheets("Master").Range("A" & Rows.Count).End(xlUp)(1)
FoundWk.Resize(1, lcSH).Copy Sheets("Master").Range("A" & Rows.Count).End(xlUp)(2)
Else
Range(Cells(1, 1), Cells(1, lcSH)).Copy Sheets("Master").Range("A" & Rows.Count).End(xlUp)(3)
FoundWk.Resize(1, lcSH).Copy Sheets("Master").Range("A" & Rows.Count).End(xlUp)(2)
End If
End If

End With
Next 'i

Application.ScreenUpdating = True
End Sub
 
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Copy picture into Excel Header Susan in Buffalo Excel Discussion (Misc queries) 1 July 27th 09 06:00 PM
Header copy across workbooks BruceD Excel Discussion (Misc queries) 2 July 20th 07 05:26 PM
copy last row without header JH Excel Programming 1 February 4th 05 01:17 PM
Code to copy range vs Copy Entire Worksheet - can't figure it out Mike Taylor Excel Programming 1 April 15th 04 08:34 PM
Need Help - Copy/Paste & Header Row Donnie Stone Excel Programming 3 October 18th 03 01:03 AM


All times are GMT +1. The time now is 04:32 PM.

Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
Copyright ©2004-2025 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"