m_in_spain
Board Regular
- Joined
- Sep 28, 2018
- Messages
- 64
- Office Version
- 365
- Platform
- Windows
Hi again, alas more help is needed!
I am trying to copy selected data from one sheet to another, but only if there is data in column A on the origin sheet.
If there is data in column A and B, then i copy A to C to my destination sheet.
This part, from scratching around on the internet i have managed. Next I need to do the same task over a few more sheets, but copying it to the next available row on my destination sheet.
I have managed to get this to work, but it will only copy column A, I have tried every way i can think of to extend the second (and subsequent) to cover columns A,B & C but i draw blank.
I am quite sure this is possible, and for some with be simple, unfortunately for me it is I who is simple!
Story So far:
Any guidance greatly appreciated
I am trying to copy selected data from one sheet to another, but only if there is data in column A on the origin sheet.
If there is data in column A and B, then i copy A to C to my destination sheet.
This part, from scratching around on the internet i have managed. Next I need to do the same task over a few more sheets, but copying it to the next available row on my destination sheet.
I have managed to get this to work, but it will only copy column A, I have tried every way i can think of to extend the second (and subsequent) to cover columns A,B & C but i draw blank.
I am quite sure this is possible, and for some with be simple, unfortunately for me it is I who is simple!
Story So far:
VBA Code:
Sub CollateUsed()
Dim sIn As Worksheet, sOut As Worksheet, rIn As Range, rOut As Range
Dim inputdata() As Variant
Dim tmpArr(1 To 3) As Variant
Dim i As Long, outcount As Long
Set sIn = Sheets("TRmech")
Set sOut = Sheets("ProjOutput")
Set rIn = sIn.Range("A5:C64")
'Set rIn = sIn.UsedRange
Set rOut = sOut.Range("A1:C1")
inputdata = rIn.Value
outcount = 0
'Reads data from inputdata Array and prints selected values from columns A, B, and C on Output sheet row by row.
For i = 1 To UBound(inputdata, 1)
If inputdata(i, 1) <> "" Then
If inputdata(i, 2) <> "" Then
If inputdata(i, 1) = "" Then End
outcount = outcount + 1
tmpArr(1) = inputdata(i, 1)
tmpArr(2) = inputdata(i, 2)
tmpArr(3) = inputdata(i, 3)
rOut.Offset(outcount - 1, 0).Value = tmpArr
Erase tmpArr
End If
End If
Next i
Erase inputdata
'==================DO IT ALL AGAIN DIFFERENT INPUT SHEET
Set sIn = Sheets("TRelec")
Set sOut = Sheets("ProjOutput")
Set rIn = sIn.Range("A5:C64")
'Set rIn = sIn.UsedRange
Set rOut = sOut.Range("A" & Rows.Count).End(xlUp).Offset(1)
inputdata = rIn.Value
outcount = 0
'Reads data from inputdata Array and prints selected values from columns A, B, and C on Output sheet row by row.
For i = 1 To UBound(inputdata, 1)
If inputdata(i, 1) <> "" Then
'If inputdata(i, 2) <> "" Then
If inputdata(i, 1) = "" Then End
outcount = outcount + 1
tmpArr(1) = inputdata(i, 1)
'tmpArr(2) = inputdata(i, 2)
'tmpArr(3) = inputdata(i, 3)
rOut.Offset(outcount - 1, 0).Value = tmpArr
Erase tmpArr
End If
'End If
Next i
Erase inputdata
End Sub
Any guidance greatly appreciated