Select empty column & paste - Help please

[saziz]

Hi All,
Here is my problem:

I am copying a range (say A1:C150 ) then I am activating another
workbook and
selecting a sheet (sheet1) and find first empty column and paste.

Can someone please help.

Here is my code:

Sub test3()
Dim lastcolumn As Range
Range("A1:E150").Select
Application.CutCopyMode = False
Selection.Copy
Windows("DataAll.xls").Activate
ActiveWindow.WindowState = xlNormal
Sheets("Sheet2").Select
ActiveSheet.lastcolumn = Cells(1, Column.count).End(xlLeft).Column
Cells(lastcolumn + 1, 1).PasteSpecial Paste:=xlValues _
, Operation:=xlNone, SkipBlanks:=False, Transpose:=False

End Sub

the above code doesn't work

thanks
Syed

 

[Tom Ogilvy]

ActiveSheet.lastcolumn = Cells(1, Column.count).End(xlLeft).Column

should be

ActiveSheet.lastcolumn = Cells(1, Column.count).End(xltoLeft).Column

 

[Leith Ross]

Hello Syed,

Here is your macro re-written using object variables. This makes the
code easier to read and follow. When writing code for multiple
Workbooks, it is easy to lose track of what is doing what.


Code:
--------------------

Sub test3()

Dim LastColumn As Long
Dim LastRow As Long
Dim Rng1 As Range
Dim Rng2 As Range
Dim Wkb1 As Workbook
Dim Wkb2 As Workbook
Dim Wks1 As Worksheet
Dim Wks2 As Worksheet

Set Wkb1 = ThisWorkbook
Set Wkb2 = "DataAll.xls"

Set Wks1 = Wkb1.Worksheets(Wkb1.ActiveSheet.Name)
Set Wks2 = Wkb2.Worksheets("Sheet2")

Set Rng1 = Wkb1.Application.Selection

LastRow = Rng1.Rows.Count
LastColumn = Wks2.Cells(1, Wks2.Columns.Count).End(xlToLeft).Column

Set Rng2 = Wks2.Range(Cells(1, LastColumn), Cells(LastRow, LastColumn))

Rng1.Copy
Rng2.PasteSpecial (xlPasteAll)

Application.CutCopyMode = False

End Sub

 

[Tom Ogilvy]

one other typo you had:

column.count should be columns.count

ActiveSheet.lastcolumn = Cells(1, Columns.count).End(xltoLeft).Column

--
Regards,
Tom Ogilvy

ActiveSheet.lastcolumn = Cells(1, Column.count).End(xlLeft).Column

should be

ActiveSheet.lastcolumn = Cells(1, Column.count).End(xltoLeft).Column

 

[saziz]

Thank you both for the help.

Actually I have a code which is working fine. But it copies a dynami
range from an active work bood and go to DataAll work book and find th
last row with data and pate below it.

Now what I wanted was to copy and go to DataAll and instead of findin
an empty row, find the next empty column and paste it. I am putting m
code here. i would appreciate very much if you can make it work.

I tried Tom's changes it gives me an error 'object not defined'

Sub mycode11()

Dim c As Range
Dim rRng As Range
Dim lastRow As Long
Dim count As Long


'Formula Worksheets(1).Range("a2") = Mid(CELL("filename", A1)
Find("]", CELL("filename", A1)) + 1, 255)
With Worksheets(1).Range("A2").Select
ActiveCell.FormulaR1C1 = _

"=MID(CELL(""filename"",R[-3]C[-6]),FIND(""["",CELL(""filename"",R[-3]C[-6]))+1,FIND(""]"",CELL(""filename"",R[-3]C[-6]))-FIND(""["",CELL(""filename"",R[-3]C[-6]))-1)"

With Worksheets(1).Range("A:A")
Set c = .Find("Ave", LookIn:=xlValues)
If Not c Is Nothing Then
.Range("A1:E" & c.Row - 1).Copy
End If
End With

Windows("DataAll.xls").Activate
With Worksheets("DataAll")
lastRow = .Range("B" & Rows.count).End(xlUp).Row (This is where I wan
the change from row to column)
.Cells(lastRow + 1, 1).PasteSpecial Paste:=xlValues _
, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End WithEnd With
Application.CutCopyMode = False
count = 5
Do

If Application.Range("B" & count) = "Orifice Axis 1" Then
Application.Range("B" & count).Select
Selection.EntireRow.Delete
End If
count = count + 1
Loop Until Application.Range("B" & count).Value = ""

End Sub

Thank you
Sye

 

[saziz]

Hi Tom & Leith,
Thank you for help. I did manage to work it out. As Tom found out
some typos and I also found in Leith's code one minute thing which I
was not doing right.
In identifying columns when I wrote 'activesheet.columns.count' it
worked in my code it was just columns.count.
Its working pretty well now. thank you once again.
Sincerely
Syed

 

[Tom Ogilvy]

Dim lastCol as Long


With Worksheets("DataAll")
lastCol = .Range("IV" & 1).End(xltoLeft).Column
.Cells(1,lastCol + 1,).PasteSpecial Paste:=xlValues, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=False
End With