Macro Skipping a column?
 

Good Morning all!

Question is why is this Macro (Originally done by Tom, attempted
modification by me :)) skipping a column before pasting its results..?

It opens a book, selects cells from Analysis sheet, and pastes it into
the existing open book first worksheet. Does the same thing for a second
sheet, but pasting it into exisitng open bok, second worksheet. Then
closes source. Then repeats.

For some reason it pastes into every second column, ie pastes into
column A, then column C, then column E. It does this on both 1st and 2nd
sheets its pasting into.... I need it to paste into every column, not
every second column

Any ideas...?

Regards

Darin



Sub consolidator3()
'CONSOLIDATES ANALYSIS and STATS SHEET
Dim i As Long, sName As String, sh As Worksheet
Dim dest As Range, bk As Workbook, bk1 As Workbook
Dim sh1 As Worksheet
Set bk1 = ThisWorkbook
i = 1
sName = Dir("D:\Documents and
Settings\dk\Desktop\Consolidation_AR_test_files\*. xls")
Do While sName < ""
Set bk = Workbooks.Open("D:\Documents and
Settings\dk\Desktop\Consolidation_AR_test_files\" & sName)

Set sh = bk.Worksheets("Analysis")
Set dest = ThisWorkbook.Worksheets(1).Cells(1, i)
i = i + 1
sh.Columns(3).Copy
dest.PasteSpecial xlValues
dest.PasteSpecial xlFormats
' write name of the workbook in row 1
dest.Value = sName

'now get stats and paste into Second Worksheet in book (RM stats)
Set sh = bk.Worksheets("stats")
Set dest = ThisWorkbook.Worksheets(2).Cells(1, i)
i = i + 1
sh.Columns(4).Copy
dest.PasteSpecial xlValues
dest.PasteSpecial xlFormats


bk.Close SaveChanges:=False
sName = Dir()
Loop
ThisWorkbook.Worksheets(1).Name = "Consol_AR"
ThisWorkbook.Worksheets(2).Name = "Sum_Stats" '


End Sub



*** Sent via Developersdex http://www.developersdex.com ***

Macro Skipping a column?
 

Darin,
Do you mean the routine to have 2 statements of :
i=i+1

NickHK

"Darin Kramer" wrote in message
...
Good Morning all!

Question is why is this Macro (Originally done by Tom, attempted
modification by me :)) skipping a column before pasting its results..?

It opens a book, selects cells from Analysis sheet, and pastes it into
the existing open book first worksheet. Does the same thing for a second
sheet, but pasting it into exisitng open bok, second worksheet. Then
closes source. Then repeats.

For some reason it pastes into every second column, ie pastes into
column A, then column C, then column E. It does this on both 1st and 2nd
sheets its pasting into.... I need it to paste into every column, not
every second column

Any ideas...?

Regards

Darin



Sub consolidator3()
'CONSOLIDATES ANALYSIS and STATS SHEET
Dim i As Long, sName As String, sh As Worksheet
Dim dest As Range, bk As Workbook, bk1 As Workbook
Dim sh1 As Worksheet
Set bk1 = ThisWorkbook
i = 1
sName = Dir("D:\Documents and
Settings\dk\Desktop\Consolidation_AR_test_files\*. xls")
Do While sName < ""
Set bk = Workbooks.Open("D:\Documents and
Settings\dk\Desktop\Consolidation_AR_test_files\" & sName)

Set sh = bk.Worksheets("Analysis")
Set dest = ThisWorkbook.Worksheets(1).Cells(1, i)
i = i + 1
sh.Columns(3).Copy
dest.PasteSpecial xlValues
dest.PasteSpecial xlFormats
' write name of the workbook in row 1
dest.Value = sName

'now get stats and paste into Second Worksheet in book (RM stats)
Set sh = bk.Worksheets("stats")
Set dest = ThisWorkbook.Worksheets(2).Cells(1, i)
i = i + 1
sh.Columns(4).Copy
dest.PasteSpecial xlValues
dest.PasteSpecial xlFormats


bk.Close SaveChanges:=False
sName = Dir()
Loop
ThisWorkbook.Worksheets(1).Name = "Consol_AR"
ThisWorkbook.Worksheets(2).Name = "Sum_Stats" '


End Sub



*** Sent via Developersdex http://www.developersdex.com ***

Macro Skipping a column?
 

Nick,

Im still quite new at this, so Im not sure.... - I dont want two columns
thats for sure!
I can try some options to see if it elimiates the two columns - do I
drop the "+1" ?

Regards

D

*** Sent via Developersdex http://www.developersdex.com ***

Macro Skipping a column?
 

get rid of the first i = i +1

it's adding 1 + 1 and getting 2 with analysis

then when it gets to stats, it's adding 2 + 1 and getting 3

then when it loops back to analysis again, i = 3 instead of 2.



--


Gary


"Darin Kramer" wrote in message
...
Good Morning all!

Question is why is this Macro (Originally done by Tom, attempted
modification by me :)) skipping a column before pasting its results..?

It opens a book, selects cells from Analysis sheet, and pastes it into
the existing open book first worksheet. Does the same thing for a second
sheet, but pasting it into exisitng open bok, second worksheet. Then
closes source. Then repeats.

For some reason it pastes into every second column, ie pastes into
column A, then column C, then column E. It does this on both 1st and 2nd
sheets its pasting into.... I need it to paste into every column, not
every second column

Any ideas...?

Regards

Darin



Sub consolidator3()
'CONSOLIDATES ANALYSIS and STATS SHEET
Dim i As Long, sName As String, sh As Worksheet
Dim dest As Range, bk As Workbook, bk1 As Workbook
Dim sh1 As Worksheet
Set bk1 = ThisWorkbook
i = 1
sName = Dir("D:\Documents and
Settings\dk\Desktop\Consolidation_AR_test_files\*. xls")
Do While sName < ""
Set bk = Workbooks.Open("D:\Documents and
Settings\dk\Desktop\Consolidation_AR_test_files\" & sName)

Set sh = bk.Worksheets("Analysis")
Set dest = ThisWorkbook.Worksheets(1).Cells(1, i)
i = i + 1
sh.Columns(3).Copy
dest.PasteSpecial xlValues
dest.PasteSpecial xlFormats
' write name of the workbook in row 1
dest.Value = sName

'now get stats and paste into Second Worksheet in book (RM stats)
Set sh = bk.Worksheets("stats")
Set dest = ThisWorkbook.Worksheets(2).Cells(1, i)
i = i + 1
sh.Columns(4).Copy
dest.PasteSpecial xlValues
dest.PasteSpecial xlFormats


bk.Close SaveChanges:=False
sName = Dir()
Loop
ThisWorkbook.Worksheets(1).Name = "Consol_AR"
ThisWorkbook.Worksheets(2).Name = "Sum_Stats" '


End Sub



*** Sent via Developersdex http://www.developersdex.com ***