Simple Copy

rcsberg

New Member
How can I extract the year "1851" from "AL011851" and copy that year for 14 rows. This process repeats with the variables being the year and the number of rows for that particular year. The number of rows to copy is given in a certain cell in column F.
There are 52,000 rows in total.

Example 1:
E
xtract the year "1851" from "AL011851" (in column 1) and copy the 4-digit year to another column for 14 rows.

Example 2:
Extract the year "1851" from "AL021851"
(in column 1) and copy the 4-digit year to another column
for 1 row.


Example 3:
Extract the year "1980" from "AL021980"
(in column 1) and copy the 4-digit year to another column
for 49 rows.

Etc...for 52,000 rows.

Thank you in advance!
 

DanteAmor

Well-known Member
Hi @★ rcsberg, welcome to the forum!

Any doubts:
- The year is always the 4 digits to the right of the text?
- How to know how many rows the year should be copied?
- In which other column and in which rows should the year be pasted?
- You could give an example of how your data looks. Just copy the range of your cells and paste here. Or upload an image to dropbox.
 

rcsberg

New Member
Hi Dante,

Thanks for the reply.

- The year is always the 4 digits to the right of the text?
1. Column A has the storm name number "AL011851" with year to the right, followed by successive updates for the storm. Each update "18510625" is written with the year to the right.
- How to know how many rows the year should be copied?
1. Column E has the number of rows for each storm.

Column A has the storm name number "AL011851" with year to the right, followed by successive updates for the storm. Each update "18510625" is written with the year to the right and month and day to the left.
Column B only has the storm name number "AL011851"
Column C has the actual name of the storm "UNNAMED"
Column D is a duplicate of Column C
Column E has the number of rows representing the updates for each storm "14"


I hope this table is formatted correctly for you to read. Hope this helps.


A B C D E
AL011851 AL011851 UNNAMED UNNAMED 14
18510625 0 0
18510625 600 600
18510625 1200 1200
18510625 1800 1800
18510625 2100 2100
18510626 0 0
18510626 600 600
18510626 1200 1200
18510626 1800 1800
18510627 0 0
18510627 600 600
18510627 1200 1200
18510627 1800 1800
18510628 0 0
AL021851 AL021851 UNNAMED UNNAMED 1
18510705 1200 1200
AL031851 AL031851 UNNAMED UNNAMED 1
18510710 1200 1200
AL041851 AL041851 UNNAMED UNNAMED 49
18510816 0 0
18510816 600 600
18510816 1200 1200
18510816 1800 1800
18510817 0 0
18510817 600 600
18510817 1200 1200
18510817 1800 1800
18510818 0 0
18510818 600 600
 

DanteAmor

Well-known Member
Run this macro

Code:
Sub Simple_copy()
  Dim c As Range
  For Each c In Range("E1:E" & Range("A" & Rows.Count).End(xlUp).Row).SpecialCells(xlCellTypeConstants).Areas
    c.Offset(1).Resize(c.Value).Value = Right(Cells(c.Row, "A"), 4)
  Next
End Sub
 

Some videos you may like

This Week's Hot Topics

  • Get External Data (long shot question!)
    This is likely a long shot but I am wondering if it is at all possible for Excel to somehow 'change' the contents of a URL that is being linked to...
  • Importing multiple excel files into one spreadsheet
    Hi, I'm trying to import multiple excel files (with the same format into a single spreadsheet) so that each day's file is listed underneath the...
  • Cell Formatting
    Good Morning, I need to format a few different cells in the following manners: A1 has to always add a colon (:) after whatever is typed in by a...
  • How to copy multiple rows using If
    Hi all, I'm very new to VBA and have written this simple code to copy certain cells if a certain cell within that row contains any data. I need...
  • Workbook_Change stopped working !
    I am working on an app to speed up & automate processing of Credit Cards statements. After data is input from a CSV file, it is presented to the...
  • VBA If statement
    Dear All, I have two dates, where I'd like a message box to pop, if the dates are between this criteria. [CODE] sDate1 = #10/1/2019#...
Top