Home Menu

Menu


ExcelBanter » Excel Newsgroups » Excel Programming > Help needed with code
Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 86
Default Help needed with code
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!
Reply With QuoteReply With Quote
  #3   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,311
Default Help needed with code
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!


Reply With QuoteReply With Quote
  #4   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 86
Default 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!



Reply With QuoteReply With Quote
  #5   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 10,124
Default Help needed with code

As I said in my post, the trick you have to learn is
Either activate the sheet and forget using the with and dots
or better coding is to PROPERLY use the dots within the with statement

with blah
Reply With QuoteReply With Quote
  #6   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 86
Default Help needed with code
Hi,

Sorry im confused, PCLIVE said to remove the dots and you said to use them?
unless i misinterpreted PCLIVE's post, im not too sure.

Please help!

thanks.

"Don Guillett" wrote:


As I said in my post, the trick you have to learn is
Either activate the sheet and forget using the with and dots
or better coding is to PROPERLY use the dots within the with statement

with blah
.
.
end with

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gemz" wrote in message
...
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!





Reply With QuoteReply With Quote
  #7   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,311
Default Help needed with code
Usually when you use a With / End With section, everything within that
section will begin with a period (.).
But in your code you're mixing it up. You don't want to start activating
other sheets or workbooks in the middle of a With statement. In a With
statement, if the code deals with anything other than the With content, then
you should be ending the With.
See if this helps.

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("")
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")

With oldbk.Sheets(1)
.AutoFilterMode = False
.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


HTH,
Paul

--

"Gemz" wrote in message
...
Hi,

Sorry im confused, PCLIVE said to remove the dots and you said to use
them?
unless i misinterpreted PCLIVE's post, im not too sure.

Please help!

thanks.

"Don Guillett" wrote:


As I said in my post, the trick you have to learn is
Either activate the sheet and forget using the with and dots
or better coding is to PROPERLY use the dots within the with statement

with blah
.
.
end with

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gemz" wrote in message
...
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!







Reply With QuoteReply With Quote
  #8   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 86
Default Help needed with code
Hi,

Thanks a lot PCLIVE you made it work!



"Gemz" wrote:

Hi,

Sorry im confused, PCLIVE said to remove the dots and you said to use them?
unless i misinterpreted PCLIVE's post, im not too sure.

Please help!

thanks.

"Don Guillett" wrote:


As I said in my post, the trick you have to learn is
Either activate the sheet and forget using the with and dots
or better coding is to PROPERLY use the dots within the with statement

with blah
.
.
end with

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gemz" wrote in message
...
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!





Reply With QuoteReply With Quote
  #9   Report Post  
Posted to microsoft.public.excel.programming
external usenet poster
  
Posts: 1,311
Default Help needed with code
You're welcome.

--

"Gemz" wrote in message
...
Hi,

Thanks a lot PCLIVE you made it work!



"Gemz" wrote:

Hi,

Sorry im confused, PCLIVE said to remove the dots and you said to use
them?
unless i misinterpreted PCLIVE's post, im not too sure.

Please help!

thanks.

"Don Guillett" wrote:


As I said in my post, the trick you have to learn is
Either activate the sheet and forget using the with and dots
or better coding is to PROPERLY use the dots within the with statement

with blah
.
.
end with

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Gemz" wrote in message
...
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!







Reply With QuoteReply With Quote
Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes
Linear Mode Linear Mode

Posting Rules
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On

Similar Threads
Thread Thread Starter Forum Replies Last Post
Help with code needed Les Stout[_2_] Excel Programming 1 December 5th 06 01:47 PM
VBA code help needed Martin Excel Discussion (Misc queries) 3 April 28th 06 09:28 AM
Code Needed Carolyn[_3_] Excel Programming 4 June 16th 04 01:26 PM
VBA code Help needed liamothelegend Excel Programming 1 November 5th 03 12:25 PM
code needed ibo Excel Programming 0 July 29th 03 05:32 PM


All times are GMT +1. The time now is 08:08 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"

 

Contact Us ExcelBanter forum home Privacy Statement