G
Guest
I'm trying to copy a range of cells from one spreadsheet to another. How do
I specify the range such as "A1:C5" if I only know the ending column number -
How do I find out the column index - whether it is column C or D, etc.
Please scroll down to where I've 'now to find #of valid rows in worksheet -
to see where I'm experiencing trouble. Here's my code:
Sub Compute_Net(fname1)
Dim f, fs, fc As Object, hdir As String, gfile As String, m_id As String,
y_id As String, incr As Integer
Dim sheet_num As Integer, hfile As Long, start_row As Integer, end_row As
Integer, col_id As Integer, row_num As Integer
Dim mydb As Database
Dim start_cell As Column, end_cell As Column, start_cell_name As String,
end_cell_name As String
'create an object for browsing folders
Set fs = CreateObject("Scripting.FileSystemObject")
'hdir = "M:\Demand Planning\Logility\2006\"
hdir = "C:\"
Set f = fs.getfolder(hdir)
Set fc = f.files
'Files collection
gfile = fname1
'Create an Excel object
Set xlapp = CreateObject("Excel.Application")
xlapp.Visible = True
xlapp.DisplayAlerts = False
Set wbook = xlapp.Workbooks.Open(gfile) 'open the Excel workbook
containing the correct file
hfile = FreeFile
sheet_num = 1
row_num = 1
start_row = 1
Set sh1 = wbook.Worksheets(sheet_num)
sh1.Name = "Gross"
wbook.Worksheets.Add After:=Worksheets(Worksheets.Count)
sheet_num = sheet_num + 1
Set sh2 = wbook.Worksheets(sheet_num)
sh2.Name = "Net"
y_id = "20" & mid(fname1, 6, 2) 'year id
m_id = DatePart("m", Now()) 'month id
If (y_id = "2007") Then
m_id = m_id - 1 'the last month when NS data is available
col_id = m_id + 5 'column # from when the forecast has to be netted down
'now to find #of valid rows in worksheet
Do Until (Len(sh1.Cells(row_num, 1)) < 1)
row_num = row_num + 1
Loop
end_row = row_num - 1
'To Copy a range
sh1.Cells.Range("A1:Q1").Copy
sh2.Cells(1, 1).PasteSpecial xlPasteAll 'copy header line - This works
fine
sh1.Range(Cells(2, 1), Cells(end_row, col_id)).Copy 'Problem with this
statement
sh2.Cells.Range(2, 1).PasteSpecial xlPasteAll
End Sub
Thanks in advance for your help!
I specify the range such as "A1:C5" if I only know the ending column number -
How do I find out the column index - whether it is column C or D, etc.
Please scroll down to where I've 'now to find #of valid rows in worksheet -
to see where I'm experiencing trouble. Here's my code:
Sub Compute_Net(fname1)
Dim f, fs, fc As Object, hdir As String, gfile As String, m_id As String,
y_id As String, incr As Integer
Dim sheet_num As Integer, hfile As Long, start_row As Integer, end_row As
Integer, col_id As Integer, row_num As Integer
Dim mydb As Database
Dim start_cell As Column, end_cell As Column, start_cell_name As String,
end_cell_name As String
'create an object for browsing folders
Set fs = CreateObject("Scripting.FileSystemObject")
'hdir = "M:\Demand Planning\Logility\2006\"
hdir = "C:\"
Set f = fs.getfolder(hdir)
Set fc = f.files
'Files collection
gfile = fname1
'Create an Excel object
Set xlapp = CreateObject("Excel.Application")
xlapp.Visible = True
xlapp.DisplayAlerts = False
Set wbook = xlapp.Workbooks.Open(gfile) 'open the Excel workbook
containing the correct file
hfile = FreeFile
sheet_num = 1
row_num = 1
start_row = 1
Set sh1 = wbook.Worksheets(sheet_num)
sh1.Name = "Gross"
wbook.Worksheets.Add After:=Worksheets(Worksheets.Count)
sheet_num = sheet_num + 1
Set sh2 = wbook.Worksheets(sheet_num)
sh2.Name = "Net"
y_id = "20" & mid(fname1, 6, 2) 'year id
m_id = DatePart("m", Now()) 'month id
If (y_id = "2007") Then
m_id = m_id - 1 'the last month when NS data is available
col_id = m_id + 5 'column # from when the forecast has to be netted down
'now to find #of valid rows in worksheet
Do Until (Len(sh1.Cells(row_num, 1)) < 1)
row_num = row_num + 1
Loop
end_row = row_num - 1
'To Copy a range
sh1.Cells.Range("A1:Q1").Copy
sh2.Cells(1, 1).PasteSpecial xlPasteAll 'copy header line - This works
fine
sh1.Range(Cells(2, 1), Cells(end_row, col_id)).Copy 'Problem with this
statement
sh2.Cells.Range(2, 1).PasteSpecial xlPasteAll
End Sub
Thanks in advance for your help!