copying a range of cells using vba

> 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!

--
pchakra

Hi,

I did not look at your code, but the answer to your question is use the
Cells(row,column) command. Both the row and column argument are numeric.

--
Cheers,
Shane Devenshire


"PC" wrote:

> > 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!
>
> --
> pchakra