New Posts  All Forums:Forum Nav:

Excel VBA Questions

post #1 of 3
Thread Starter 
I have run into a couple issues with macro programming for Excel 2003.

1. I have specific cells that I want to copy from one sheet to another with coordinates for the copy and paste location - in the form of integer coordinates (1,1 is A1). Sheet two is a Sheet object (so sheet2.activate would be the command to select the sheet).

2. I need to import all the files from a directory. I will have 10-15 spreadsheets (each with one sheet) in a directory. I need to import them by either by doing a multi-select or selecting the directory. The data from each sheet will be "imported" by doing a copy/paste special of just the values in a new sheet in the destination workbook for each file.

If you know the solutions to either of these, the help is appreciated. Learned VBA as I go...
Edited by mortimersnerd - 2/8/11 at 6:15am
Mr. Windy
(13 items)
 
  
CPUMotherboardGraphicsRAM
Q9650 Asus Maximus II Formula EVGA GTX 460 8GB OCZ 800Mhz 
Hard DriveOSMonitorPower
2x300 V-Raptor, 2x1TB WD, 2xSegate 320GB Fedora 14 2x Samsung 245BW Corsair 750TX; APC BR1500 
Case
Soldam Windy Altium FC 700 
  hide details  
Reply
Mr. Windy
(13 items)
 
  
CPUMotherboardGraphicsRAM
Q9650 Asus Maximus II Formula EVGA GTX 460 8GB OCZ 800Mhz 
Hard DriveOSMonitorPower
2x300 V-Raptor, 2x1TB WD, 2xSegate 320GB Fedora 14 2x Samsung 245BW Corsair 750TX; APC BR1500 
Case
Soldam Windy Altium FC 700 
  hide details  
Reply
post #2 of 3
Thread Starter 
I figured out the solution to the first issue. When working with another sheet its ActiveSheet.Paste not Selection.Paste.

Code:
Rows(7).Select 'copy line 7 from first sheet
Selection.Copy
analysis.Activate

'Select Paste into specified line in second sheet
Rows(analysisLine).Select 
analysisLine = analysisLine + 1
ActiveSheet.Paste
WS_GPS.Activate 'Activate original sheet
Still looking for a solution to the second issue.
Mr. Windy
(13 items)
 
  
CPUMotherboardGraphicsRAM
Q9650 Asus Maximus II Formula EVGA GTX 460 8GB OCZ 800Mhz 
Hard DriveOSMonitorPower
2x300 V-Raptor, 2x1TB WD, 2xSegate 320GB Fedora 14 2x Samsung 245BW Corsair 750TX; APC BR1500 
Case
Soldam Windy Altium FC 700 
  hide details  
Reply
Mr. Windy
(13 items)
 
  
CPUMotherboardGraphicsRAM
Q9650 Asus Maximus II Formula EVGA GTX 460 8GB OCZ 800Mhz 
Hard DriveOSMonitorPower
2x300 V-Raptor, 2x1TB WD, 2xSegate 320GB Fedora 14 2x Samsung 245BW Corsair 750TX; APC BR1500 
Case
Soldam Windy Altium FC 700 
  hide details  
Reply
post #3 of 3
For #2:
  1. Get list of all .xls, .xlsx, and .xlsm files in a directory and place into an array(http://www.ozgrid.com/forum/showthre...t=51306&page=1)
  2. Tranverse the array of filenames
    1. Create new worksheet
    2. Open file from list
    3. Select data from open file
    4. Copy data into the new worksheet
    5. Close file

Edited by DuckieHo - 2/10/11 at 7:41am
Once again...
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 920 [4.28GHz, HT] Asus P6T + Broadcom NetXtreme II VisionTek HD5850 [900/1200] + Galaxy GT240 2x4GB G.Skill Ripjaw X [1632 MHz] 
Hard DriveOSMonitorKeyboard
Intel X25-M 160GB + 3xRAID0 500GB 7200.12 Window 7 Pro 64 Acer H243H + Samsung 226BW XARMOR-U9BL  
PowerCaseMouseMouse Pad
Antec Truepower New 750W Li Lian PC-V2100 [10x120mm fans] Logitech G9 X-Trac Pro 
  hide details  
Reply
Once again...
(13 items)
 
  
CPUMotherboardGraphicsRAM
i7 920 [4.28GHz, HT] Asus P6T + Broadcom NetXtreme II VisionTek HD5850 [900/1200] + Galaxy GT240 2x4GB G.Skill Ripjaw X [1632 MHz] 
Hard DriveOSMonitorKeyboard
Intel X25-M 160GB + 3xRAID0 500GB 7200.12 Window 7 Pro 64 Acer H243H + Samsung 226BW XARMOR-U9BL  
PowerCaseMouseMouse Pad
Antec Truepower New 750W Li Lian PC-V2100 [10x120mm fans] Logitech G9 X-Trac Pro 
  hide details  
Reply
New Posts  All Forums:Forum Nav:
  Return Home
  Back to Forum: Coding and Programming