Excel VBA

August 6, 2009 at 06:37:25
Specs: Windows XP
Hi,

I have a working VBA code which copies from a range of cells from a workbook and copies them to another sheet.

InvSheet.Range(InvSheet.Cells(iRow, "B"), InvSheet.Cells(iRow, "K")).Copy
BatchSheet.Cells(oRow, "D").PasteSpecial xlPasteValues
iRow = iRow + 1
oRow = oRow + 1
Loop Until IsEmpty(InvSheet.Cells(iRow, "B")) Or InvSheet.Cells(iRow, "B") = 0

I need to make a change to this so that it picks one additional cell and copies its contents.

ie (Range (same row) A toK) + (same Row)Z

Can anyone help changing the code above?

Thanks.


See More: Excel VBA

Report •


#1
August 6, 2009 at 07:26:04
re: (Range (same row) A toK) + (same Row)Z

Your original code appears to copy B:K, yet you're asking for A:K plus Z. Which is it?

In any case, try this. Without the rest of your code, I can't really test it...

myRange = _
  InvSheet.Range(InvSheet.Cells(iRow, "B"), InvSheet.Cells(iRow, "K")).Address _
  & "," & InvSheet.Cells(iRow, "Z").Address
Range(myRange).Copy

What I am suggesting is to build a string that contains the Range address, such as:

$B$1:$K$1,$Z$1

and then letting VBA evaluate:

Range(myRange).Copy

as

Range("$B$1:$K$1,$Z$1").Copy


Report •

#2
August 6, 2009 at 08:37:14
Thanks, but please accept my novice knowledge which seems inadequate here. I tried your suggestion but am not getting the results. what do i put where you have .Address ?

For completeness, here is the full VBA cose. It makes calls to some other excell files which means you still wont be able to test it, but at least the full code will give you the gist of what it is trying to do. And teh only change required of it is to pick up one more cell "AB" from the same row and paste it along with the other data into the other sheet called stocksbatch.xls.

---------------- the Code -----------------------------------

Sub InvoiceToBatch()
'Copies the current invoice in the INVOICE TEMPLATE sheet of
'this workbook to stocksbatch.xls
Dim Account As String
Dim InvDate As Date
Dim InvNum As Long 'invoice "FR" number
Dim InvSheet As Worksheet
Dim BatchSheet As Worksheet
Dim NextRow As Long 'the next available invoice row on the batch sheet
Dim oRow As Long 'row number on BatchSheet
Dim iRow As Long 'row number on InvSheet

Set InvSheet = ThisWorkbook.Worksheets("INVOICE TEMPLATE")

'Workbooks.Open Filename:="C:\Documents and Settings\tomer\Desktop\stocks_batch.xls"
'Workbooks.Open Filename:="G:\PUBS\PP-MS\INVOICES\stocksbatch.xls"
Workbooks.Open Filename:="T:\Amnesty\stocksbatch.xls"
Set BatchSheet = ActiveWorkbook.Worksheets("Sheet1")
NextRow = BatchSheet.Range("D65536").End(xlUp).Row + 1
oRow = NextRow
iRow = 20

With InvSheet
Account = .Range("E5")
InvNum = .Range("K2")
InvDate = .Range("F17")
End With

With BatchSheet
.Cells(oRow, "A") = Account
.Cells(oRow, "B") = InvNum
.Cells(oRow, "C") = InvDate
Do
InvSheet.Range(InvSheet.Cells(iRow, "B"), InvSheet.Cells(iRow, "K")).Copy
BatchSheet.Cells(oRow, "D").PasteSpecial xlPasteValues
iRow = iRow + 1
oRow = oRow + 1
Loop Until IsEmpty(InvSheet.Cells(iRow, "B")) Or InvSheet.Cells(iRow, "B") = 0
End With
Application.CutCopyMode = False
ActiveWorkbook.Close True 'save changes and close
End Sub



Report •

#3
August 6, 2009 at 11:11:49
I'll look at your code later, but to answer your question:

You don't put anything where I put .Address. You leave .Address right where it is.

=====================

Let's say iRow = 1

Range(Cells(iRow, "B"), Cells(iRow, "K")).Copy

evaluates to:

Range("$B$1:$K$1").Copy

=====================

Range(Cells(iRow, "B"), Cells(iRow, "K")).Address

evaluates to:

"$B$1:$K$1"

which the address of the range specified.

=====================

Using that logic, you can assign that string to a variable and use the variable as a range address:

myRange = Range(Cells(iRow, "B"), Cells(iRow, "K")).Address

Range(myRange).Copy

which evaluates to:

Range("$B$1:$K$1").Copy

=====================

Finally, by concatenating additional .Addresses with commas you can build more complex address strings:

myRange = Range(Cells(iRow, "B"), Cells(iRow, "K")).Address & "," & Range(Cells(iRow, "Z").Address

which evaluates to:

"$B$1:$K$1,$Z$1"

so that Range(myRange).Copy

evaluates to:

Range("$B$1:$K$1,$Z$1").Copy


Report •

Related Solutions

#4
August 6, 2009 at 12:10:28
As far as I can tell my code should work, with one minor modification. In my earlier suggestion, I wasn't referring to the InvSheet object when I was copying the range.

Try this:

myRange = _
  InvSheet.Range(InvSheet.Cells(iRow, "B"), InvSheet.Cells(iRow, "K")).Address _
  & "," & InvSheet.Cells(iRow, "Z").Address
InvSheet.Range(myRange).Copy

P.S. You should get used to this syntax to find the next empty row in worksheet column:

NextRow = BatchSheet.Range("D" & Rows.Count).End(xlUp).Row + 1

Rows.Count (instead of 65536) will work in all versions, including 2007. Since there might be more than 65536 rows in a 2007 worksheet, you might not be finding the last row.


Report •

#5
August 6, 2009 at 13:11:25
Many thanks for your perseverence with this. It works very well now. I am very grateful.

All I hav eto do now is to figure out how I can get the code to repeat
.Cells(oRow, "A") = Account to .Cells(oRow, "c") =InvDate

I think I can do that.

Once again a big THANK YOU



Report •


Ask Question