VBA Range

never really concentrated on the VBA although i can feel it is powerful.

i tried a pretty long time to copy 3 columns from 24 sheets to a new sheets. the code is as following. i know it may be stupid but it worked.

the RANGE is powerful with the offset. good. i will use this in later data processing.

Option Explicit
Sub CombineSheets()

Dim i As Integer
‘, j As Integer, k As Integer, nrows As Integer

Dim ws As Worksheet

Set ws = Worksheets(“Combined”)
For i = 1 To 24

ws.Range(ws.Range(“a1”).Offset(0, (i – 1) * 3), ws.Range(“a1”).Offset(65535, i * 3 – 1)).Value = Worksheets(“Case” & i).Range(“a1:c65536”).Value

‘For j = 1 To 3
‘ For k = 1 To nrows
‘ Cells(k, (i – 1) * 3 + j) = Worksheets(“Case” & i).Cells(k, j)
‘ Next k
‘Next j
Next i

End Sub


About yhtian

I am an academic researcher working on Offshore Geotechnical Engineering. My blog aims to write down some work related trivial things and tricks about software, programming. It is basically a memo for me to back up some thoughts and small details. But I am more than happy if someone would visit and discuss.
This entry was posted in Uncategorized. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s