Select empty column & paste - Help please

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

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

should be

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

--
Regards,
Tom Ogilvy


"saziz" <saziz.20be3b_1135041601.6951@excelforum-nospam.com> wrote in
message news:saziz.20be3b_1135041601.6951@excelforum-nospam.com...
>
> 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
>
>
> --
> saziz
> ------------------------------------------------------------------------
> saziz's Profile:
http://www.excelforum.com/member.php...fo&userid=6350
> View this thread: http://www.excelforum.com/showthread...hreadid=494770
>

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.

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

Sincerely,
Leith Ross

one other typo you had:

column.count should be columns.count

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

--
Regards,
Tom Ogilvy



"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:O$i3ggQBGHA.3488@TK2MSFTNGP10.phx.gbl...
> ActiveSheet.lastcolumn = Cells(1, Column.count).End(xlLeft).Column
>
> should be
>
> ActiveSheet.lastcolumn = Cells(1, Column.count).End(xltoLeft).Column
>
> --
> Regards,
> Tom Ogilvy
>
>
> "saziz" <saziz.20be3b_1135041601.6951@excelforum-nospam.com> wrote in
> message news:saziz.20be3b_1135041601.6951@excelforum-nospam.com...
> >
> > 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
> >
> >
> > --
> > saziz
> > ------------------------------------------------------------------------
> > saziz's Profile:
> http://www.excelforum.com/member.php...fo&userid=6350
> > View this thread:
http://www.excelforum.com/showthread...hreadid=494770
> >
>
>

Thank you both for the help.

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

Now what I wanted was to copy and go to DataAll and instead of finding an empty row, find the next empty column and paste it. I am putting my 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 want 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
Syed

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

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



--
Regards,
Tom Ogilvy


"saziz" <saziz.20cm2n_1135098608.3617@excelforum-nospam.com> wrote in
message news:saziz.20cm2n_1135098608.3617@excelforum-nospam.com...
>
> Thank you both for the help.
>
> Actually I have a code which is working fine. But it copies a dynamic
> range from an active work bood and go to DataAll work book and find the
> last row with data and pate below it.
>
> Now what I wanted was to copy and go to DataAll and instead of finding
> an empty row, find the next empty column and paste it. I am putting my
> 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 want
> 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
> Syed
>
>
> --
> saziz
> ------------------------------------------------------------------------
> saziz's Profile:
http://www.excelforum.com/member.php...fo&userid=6350
> View this thread: http://www.excelforum.com/showthread...hreadid=494770
>