Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 *** |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reference row on another sheet skipping zeros but not skipping li. | Excel Discussion (Misc queries) | |||
Skipping every other column for an Average. | Excel Worksheet Functions | |||
Need help in numbering a column while skipping lines | Excel Worksheet Functions | |||
SKIPPING A COLUMN | Excel Programming | |||
SKIPPING A COLUMN | Excel Worksheet Functions |