Help needed with code
Hi PCLIVE,
I tried moving the '.' where stated and it did worked ok but this time it
causes different errors in the macro!
Below is my whole macro - please see whats the problem, thanks.
(I do have names where you see "" ive just deleted them here.)
Set oldbk = ActiveWorkbook
Set newbk = Workbooks.Add
newbk.Sheets("Sheet1").Name = ""
Set NewbkS1 = newbk.Sheets("")
If newbk.Sheets.Count 1 Then
newbk.Sheets("Sheet2").Name = ""
Else
newbk.Sheets.Add After:=Sheets(1)
newbk.Sheets("Sheet2").Name = ""
End If
Set NewbkS2 = newbk.Sheets("")
With oldbk.Sheets(1)
.AutoFilterMode = False
LastColumn = Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = Cells(Rows.Count, 1).End(xlUp).Row
Set rng = Range(Cells(1, 17), Cells(LastRow, LastColumn))
rng.Copy Destination:=NewbkS1.Range("E1")
oldbk.Activate
.Columns("H:H").AutoFilter Field:=1, Criteria1:=""
.Columns("C:D").Copy Destination:= _
NewbkS1.Range("A1")
.Columns("K:L").Copy Destination:= _
NewbkS1.Range("C1")
.Columns("N:Q").Copy Destination:= _
NewbkS1.Range("E1")
.Columns("R:AF").Copy Destination:= _
NewbkS1.Range("I1")
End With
Worksheets("").Activate
Cells.Select
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit
With oldbk.Sheets(2)
.Columns("H:H").Copy Destination:= _
NewbkS2.Range("A1")
.Columns("T:T").Copy Destination:= _
NewbkS2.Range("B1")
.Columns("AK:AK").Copy Destination:= _
NewbkS2.Range("C1")
.Columns("G:G").Copy Destination:= _
NewbkS2.Range("D1")
.Columns("AJ:AJ").Copy Destination:= _
NewbkS2.Range("E1")
.Columns("D:D").Copy Destination:= _
NewbkS2.Range("F1")
.Columns("AM:AM").Copy Destination:= _
NewbkS2.Range("G1")
.Columns("AP:AP").Copy Destination:= _
NewbkS2.Range("H1")
.Columns("AS:AS").Copy Destination:= _
NewbkS2.Range("I1")
.Columns("AU:AU").Copy Destination:= _
NewbkS2.Range("J1")
.Columns("AQ:AQ").Copy Destination:= _
NewbkS2.Range("K1")
End With
Worksheets("").Activate
Cells.Select
Cells.EntireColumn.AutoFit
Cells.EntireRow.AutoFit
With Application
.DisplayAlerts = True
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub
thanks!!
"PCLIVE" wrote:
Is this code in the middle of some sort of WITH statement? It doesn't
appear to be except for some periods in the code.
A several lines stand out.
LastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column 'Try
removing period before "Cells".
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row 'Try
removing period before "Cells".
Set rng = .Range(Cells(1, 17), Cells(LastRow, LastColumn)) 'Try
removing period before "Range".
rng.Copy Destination:=NewbkS1.Range("E1")
oldbk.Activate
.Columns("H:H").AutoFilter Field:=1, Criteria1:="X"
'Try removing period before "Columns".
.Columns("C:F").Copy Destination:= _
'Try removing period before "Columns".
NewbkS1.Range("A1")
.Columns("K:Q").Copy Destination:= _
'Try removing period before "Columns".
NewbkS1.Range("E1")
HTH,
Paul
--
"Gemz" wrote in message
...
Hi,
I have this sample code but everytime i try to run it this line is
highlighted and nothing happens:
Set rng = .Range(Cells(1, 17), Cells(LastRow, LastColumn))
The rest of the code:
LastColumn = .Cells(1, Columns.Count).End(xlToLeft).Column
LastRow = .Cells(Rows.Count, 1).End(xlUp).Row
Set rng = .Range(Cells(1, 17), Cells(LastRow, LastColumn))
rng.Copy Destination:=NewbkS1.Range("E1")
oldbk.Activate
.Columns("H:H").AutoFilter Field:=1, Criteria1:="X"
.Columns("C:F").Copy Destination:= _
NewbkS1.Range("A1")
.Columns("K:Q").Copy Destination:= _
NewbkS1.Range("E1")
Whats going wrong?
please help, thanks!
|