copy data from WS to WS in same workbook

[Guest]

I'm not sure how to accomplish this and I've searched previous postings with
no luck. My WB titled "Today" has numberous WS. WS1, WS2, WS3, WS4 and so
on. I'd like to copy the info in col A and B of each worksheet, only if the
rows are populated and copy the data to Sheet titled "Final" into Col A and
B. Then in Col C of the Final sheet, I'd also like to capture the sheet
titled where the data was copied from....WS1, WS2 etc. My plan is to launch
a macro with a button command so once the user enters all the data into the
various WS, they would just click the command button to run the macro to
bring in the data. Any help you can give me is appreciated, thank you.

 

[Nigel]

Try this code, it reads all worksheets with a name beginning WS (not case
sensitive) and copies cells A and B from each sheet where A or B has a value
into the sheet called Final. The source sheet of the entry is inserted
into column C and the source row into column 4.
You did not say if "A and B" or "A or B" values exist should be copied, I
chose the later but this is easily changed. Put the code into a standard
module, and call it from a worksheet control button as required.

Sub CopyAll()
Dim wSh As Worksheet, wTarget As Worksheet
Dim xlr As Long, xr As Long, xTarget As Long

' set up final sheet
Set wTarget = Worksheets("Final")
With wTarget
.Cells.ClearContents
.Cells(1, 1) = "ColumnA"
.Cells(1, 2) = "ColumnB"
.Cells(1, 3) = "Source WS"
.Cells(1, 4) = "Source Row"
End With
xTarget = 2

' scan all Sheets prefixed WS and copy to target
For Each wSh In ActiveWorkbook.Worksheets
If UCase(Left(wSh.Name, 2)) = "WS" Then
With wSh
xlr = .Cells(.Rows.Count, "A").End(xlUp).Row
If .Cells(.Rows.Count, "B").End(xlUp).Row > xlr Then _
xlr = .Cells(.Rows.Count, "B").End(xlUp).Row
For xr = 1 To xlr
If Len(Trim(.Cells(xr, 1))) > 0 Or Len(Trim(.Cells(xr, 2))) >
0 Then
.Range(.Cells(xr, 1), .Cells(xr, 2)).Copy
Destination:=wTarget.Cells(xTarget, 1)
wTarget.Cells(xTarget, 3) = wSh.Name
wTarget.Cells(xTarget, 4) = xr
xTarget = xTarget + 1
End If
Next xr
End With
End If
Next wSh
wTarget.Columns("A").Columns.AutoFit

End Sub

 

[Nigel]

I re-read your post and I detect that you might be copying A & B if other
columns in the row are populated? If that is the case then the test to
check for populated cells needs to be made BEFORE column A & B are copied.
You need to confirm what is the extent of this test. - is it column C and
above or does it include column A and B as well?

 

[Guest]

Hi Nigel,

Thanks so much for your help, one question though. I used WS1, WS2, WS3 for
ease of description, my worksheets have more complex names. How would I edit
the code to spike out the worksheets real name? Another, how would I or is
it even possible to call them by range using something like
Worksheet1:Worksheet10? I'm trying to learn when I can use ranges and when
not. Thanks so much for your help.

 

[Nigel]

Worksheets is a collection of worksheets in the active workbook. They can
be referred to in the following way....

1. By name, that is the name that appears on the sheet tab e.g Sheet1, so
to refer to this sheet use Worksheets("Sheet1")
2. By Index, since the worksheets is the collection, you can reference each
item index in this collection, Worksheets(1), returns the first sheet.
Worksheets index values are used as they in appear in sequence in the
workbook (including hidden worksheets).
3. By CodeName, this can only be set in the VBE and not by VBA program code,
this name therefore once set does not change. The default values are the
same as the sheet name when created, so for example a new workbook with
three sheets would appear with names of Sheet1, Sheet2 and Sheet3, the
codenames will be the same, change the sheet name (using the tab or by
program) will not affect the codename. This is really useful because the
user may change names but cannot change the codename. To refer to a
Worksheet by codename use just Sheet1

To loop through specific collection of worksheets, you could set the
codenames for this task e.g. use the WS1, WS2, WS3 etc. construct but it
will mean editing the codenames using the VBE (select the sheet object and
press f4 to edit the name). Or use the index number but this can give
anomalies if the sheet order is changed.

In this case, if ALL sheets except the Final sheet are to be processed, then
use the loop to sequence ALL worksheets and exclude the one named Final,
e.g.

Dim wSh As Worksheet
For Each wSh In ActiveWorkbook.Worksheets
If wSh.Name <> "Final" Then
MsgBox wSh.Name
End If
Next

or using the index number......

Dim i As Integer
For i = 1 To Worksheets.Count
If Worksheets(i).Name <> "Final" Then
MsgBox "Index: " & i & " " & Worksheets(i).Name
End If
Next i

You cannot refer to a range as such but you can set up your own collection
of objects (worksheets); but this is rarely the best approach.

 

[Guest]

Thanks So Much Nigel for explaining this to me; I truly appreciate your time
in doing so!